MySQL Cheatsheet - MySQL Command Reference
Essential MySQL commands for daily database administration and development. Organized by scenario from table creation to query optimization. Copy and use directly during troubleshooting.
Back to DatabasesConnection & User 7
mysql -u root -p -h 127.0.0.1 -P 3306Interactive login, -p prompts for password, -h host, -P port
mysql -u root -p db_name < dump.sqlImport SQL file into a database
mysqldump -u root -p --single-transaction db_name > backup.sqlConsistent snapshot backup without locking (InnoDB)
CREATE USER 'app'@'%' IDENTIFIED BY 'password';Create user, '%' allows all hosts, restrict IP in production
GRANT SELECT, INSERT, UPDATE ON db.* TO 'app'@'%';Grant privileges on specific database, principle of least privilege
ALTER USER 'app'@'%' IDENTIFIED BY 'new_password';Change user password, MySQL 5.7+ syntax
SHOW GRANTS FOR CURRENT_USER;View current user privileges
Database & Table 8
SHOW DATABASES;List all databases
USE db_name;Switch to a database
SHOW TABLES;List all tables in current database
DESC table_name;View table structure, equivalent to SHOW COLUMNS FROM
SHOW CREATE TABLE table_name\GView CREATE TABLE statement, \G for vertical output
ALTER TABLE t ADD COLUMN col INT DEFAULT 0 AFTER id;Add column, be careful on large tables (locks table)
ALTER TABLE t ADD INDEX idx_name (col);Add index, use pt-online-schema-change in production to avoid locking
TRUNCATE TABLE t;Clear table data, faster than DELETE and cannot be rolled back
Query & Index 6
EXPLAIN SELECT * FROM t WHERE col = 1\GView execution plan, check type/key/rows/Extra
EXPLAIN FORMAT=JSON SELECT ...JSON format execution plan, more detailed (MySQL 5.6+)
SHOW INDEX FROM t;View all indexes, check Cardinality for selectivity
SELECT COUNT(*) FROM t WHERE col IS NULL;Count NULL values, indexes don't include NULL rows
SHOW STATUS LIKE "Slow_queries";Check slow query count, requires slow_query_log enabled
SHOW VARIABLES LIKE 'slow_query%';View slow query log configuration
Process & Lock 6
SHOW PROCESSLIST;View all connections and running SQL, quickly find stuck queries
SHOW FULL PROCESSLIST;Show full SQL statements (not truncated)
KILL <id>;Terminate a connection, verify it's not a replication thread first
SELECT * FROM information_schema.INNODB_TRX;View current transactions, find long-running transactions and lock waits
SELECT * FROM performance_schema.data_locks WHERE LOCK_STATUS='PENDING';View lock waits (MySQL 8.0+)
SHOW ENGINE INNODB STATUS\GInnoDB engine status with deadlock info and LATEST DETECTED DEADLOCK
Backup & Recovery 5
mysqldump -u root -p --all-databases --routines --triggers > all.sqlFull backup including stored procedures and triggers
mysqldump -u root -p --single-transaction --master-data=2 db > db.sqlInclude binlog position for replica setup
mysqlbinlog --start-datetime="2026-01-01 00:00:00" mysql-bin.000123 | mysql -u root -pPoint-in-time recovery (PITR)
mysql -u root -p -e "SET GLOBAL read_only=1;"Set read-only mode, use before failover
SHOW BINARY LOGS;View binlog list and size
Replication 5
SHOW SLAVE STATUS\GView replica status (MySQL 5.7), check Slave_IO_Running and Slave_SQL_Running
SHOW REPLICA STATUS\GView replica status (MySQL 8.0+ new syntax)
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.1', SOURCE_PORT=3306;Configure source address (MySQL 8.0+)
START REPLICA; STOP REPLICA;Start/stop replication (MySQL 8.0+)
SELECT * FROM performance_schema.replication_applier_status_by_worker;View replication worker status and errors
💡 Tips
- Use pt-online-schema-change or gh-ost for schema changes in production — never ALTER large tables directly.
- Seconds_Behind_Master = 0 does not guarantee zero lag; large transactions can cause jumps. Check relay log size as well.
- For slow query analysis, enable slow_query_log first, then use pt-query-digest to analyze — don't just rely on EXPLAIN.