Ahmed Yakout
Yakout's Blog

Yakout's Blog

MySQL most useful commands and tips (Cheat Sheet)

For troubleshooting and diagnosing issues

Ahmed Yakout's photo
Ahmed Yakout
·Jun 7, 2021·

4 min read

MySQL most useful commands and tips (Cheat Sheet)

Subscribe to my newsletter and never miss my upcoming articles

In this article I will give you the most useful commands in MySQL that I use more often. I will try to keep updating it with more useful commands.


How to show results vertically instead of a table?

  • terminate the query with \G instead of ;
    SELECT * FROM table_name \G
    

How to get table schema?

SHOW CREATE TABLE table_name \G

or

DESCRIBE table_name;

How to get some info about the table (e.g number of rows, Auto increment value, creation time, update time .. etc)

> SHOW TABLE status LIKE 'table_name' \G
*************************** 1. row ***************************
           Name: table_name
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 495
 Avg_row_length: 695
    Data_length: 344064
Max_data_length: 0
   Index_length: 344064
      Data_free: 0
 Auto_increment: 339472789
    Create_time: 2021-06-07 09:59:29
    Update_time: 2021-06-07 09:59:29
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.001 sec)

How to list processes

SHOW FULL processlist \G

or if you want more control (e.g adding conditions)

SELECT *
FROM information_schema.processlist
WHERE [some_conditions] \G

How to stop/kill a query?

  • After getting the id of the query from the processlist run:
    KILL [process_id];
    

How to kill multiple queries (massive kill)?

  • If you have the needed permissions you can use:

    MySQL> SELECT concat('KILL ',id,';')
    FROM information_schema.processlist
    WHERE USER='yakout'
    AND time > 1000 into outfile '/tmp/death_note.txt;
    MySQL> source /tmp/death_note.txt;
    
  • If you received error:

    Access denied for user 'yakout'@'%' (using password: YES)
    

    Then you can do it using mysql command

    > mysql -h $RDS_HOSTNAME -u $RDS_USERNAME -p$RDS_PASSWORD -N -e "select concat('KILL ',id,';') from information_schema.processlist where user='yakout' and time > 1000" > death_note.sql
    > mysql -h $RDS_HOSTNAME -u $RDS_USERNAME -p$RDS_PASSWORD < death_note.sql
    

    Note: that the -N flag is an alias for the option --skip-column-names so we remove the column headers from being printed into the file.

How to see MySQL warnings?

  • Sometime when you execute a query you get Empty set, 1 warning (0.001 sec). You can check this warning by running:
    SHOW WARNINGS;
    

How to Identify locked tables?

SHOW OPEN TABLES where in_use > 1;

Note: IN_USE column indicates that table is locked. When the first lock is taken, the value is incremented to 1. So a value of 5 represents the initial lock request + 4 blocked lock requests.

How to know who is holding metadata locks?

If you are seeing a lot of queries in the state: Waiting for table metadata lock and you need to know who is holding that metadata lock so you can kill and unlock the table:

  • For MySQL version < 5.7.3
    SELECT * 
    FROM INFORMATION_SCHEMA.INNODB_LOCKS 
    WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS);
    
  • For MySQL version >= 5.7.3
    SELECT OBJECT_TYPE,
         OBJECT_SCHEMA,
         OBJECT_NAME,
         LOCK_TYPE,
         LOCK_STATUS,
         THREAD_ID,
         PROCESSLIST_ID,
         PROCESSLIST_INFO
    FROM performance_schema.metadata_locks
    INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
    WHERE PROCESSLIST_ID <> CONNECTION_ID();
    

How to get some useful information about InnoDB engine for diagnosing issues?

SHOW ENGINE INNODB STATUS \G

Important sections that are displayed:

  • LATEST FOREIGN KEY ERROR: Only shown if there has been a foreign key constraint error, it displays the failed statement and information about the constraint and the related tables.
  • LATEST DETECTED DEADLOCK: Only shown if there has been a deadlock, it displays the transactions involved in the deadlock and the statements being executed, held and required locked and the transaction rolled back to.
  • TRANSACTIONS: The output of this section can help identify lock contention, as well as reasons for the deadlocks.

When to increase or decrease innodb_lock_wait_timeout?

innodb_lock_wait_timeout is InnoDB system variable, which controls the length of time in seconds an InnoDB transactions waits for a row lock (Type of locking done by DML operations on tables) before they give up waiting the lock issuing this error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • You can change this variable in the session using
    SET SESSION innodb_lock_wait_timeout=50 -- default value
    
  • Decrease this value in sessions from highly interactive applications that require fast response to users or clients.
  • Increase this value if you are running in the background or if you running a long running task like a migration on a heavily loaded database.
 
Share this