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.
Official References
Commands are compiled from the official docs below. Click to verify the latest usage.
Maintained by LaoHand
Publicly updated on Jul 21, 2026, continuously proofread against official docs.
Found an error? Report it
Wrong command or description? Open an issue to help us fix it.
Found an error? Report it