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
SELECT * FROM table_name \G
How to get table schema?
SHOW CREATE TABLE table_name \G
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:
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 -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
-Nflag is an alias for the option
--skip-column-namesso 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:
How to Identify locked tables?
SHOW OPEN TABLES where in_use > 1;
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 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.