MySQL Cheat Sheet

mysql client and admin — query, manage users, dumps, slow queries, and replication status.

Databases5 sections · 31 commands
⌘K

Connecting & Running

4
mysql -u USER -p -h HOST DB

Connect with a password prompt

mysql -e 'SELECT 1;' DB

Run one statement non-interactively

mysql < script.sql

Execute a SQL script

USE db;

Pick the database

Inspecting

7
SHOW DATABASES;

All databases

SHOW TABLES;

All tables in the database

DESCRIBE TABLE;

Column definitions

SHOW CREATE TABLE TABLE;

The full DDL

SHOW INDEX FROM TABLE;

Indexes on a table

EXPLAIN SELECT ...;

Execution plan

SELECT @@VERSION;

Server version

Users & Grants

7
CREATE USER 'app'@'%' IDENTIFIED BY 'secret';

Create a user from anywhere

GRANT ALL ON appdb.* TO 'app'@'%';

Grants on all tables of a DB

GRANT SELECT ON appdb.table TO 'readonly'@'%';

Read-only grant

SHOW GRANTS FOR 'app'@'%';

What a user can do

REVOKE ALL... ... ON appdb.* FROM 'app'@'%';

Revoke privileges

DROP USER 'app'@'%';

Remove a user

ALTER USER 'app'@'%' IDENTIFIED BY 'newpass';

Change a password

Backup & Restore

6
mysqldump -u USER -p DB > db.sql

Dump a database

mysqldump --single-transaction -u USER -p DB > db.sql

Consistent InnoDB dump

mysqldump --all-databases -u USER -p > all.sql

Dump every database

mysqldump -u USER -p DB --where='created > CURDATE() - INTERVAL 1 DAY' > today.sql

Dump a subset

mysql -u USER -p DB < db.sql

Restore a dump

mysqldump --routines --triggers --events DB > db.sql

Include stored objects

Troubleshooting

7
SHOW PROCESSLIST;

Current connections

SHOW FULL PROCESSLIST;

Queries with full text

KILL ID;

Kill a connection

SHOW STATUS LIKE 'Threads_connected';

Connection count

SHOW VARIABLES LIKE 'max_connections';

Limit check

SELECT * FROM information_schema.processlist WHERE command = 'Query';

Active queries via SQL

SHOW ENGINE INNODB STATUS\G

InnoDB internals (locks, deadlocks)