Ahmed Yakout
Yakout's Blog

Yakout's Blog

MySQL most useful commands and tips (Cheat Sheet)

For troubleshooting and diagnosing issues

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