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.