MySQL Cheatsheet - MySQL Command Reference

Q: Why does altering a large table (adding a column or changing a type) stall production in MySQL?

Most ALTERs rebuild the table and take a metadata lock; on large tables they copy data and consume resources, which can block reads and writes and lag replication. Modern versions can use ALGORITHM=INPLACE and LOCK=NONE to run more online, but still validate on a replica first, run during a low-traffic window, and assess long-transaction impact.

For anyone creating databases, optimizing slow queries, or backing up MySQL. The key is not typing CRUD but understanding whether a WHERE hits an index, what EXPLAIN type means, and that DDL and bulk operations can lock tables and stall production. By the end you can use EXPLAIN to judge whether a query uses an index and spot full table scans (type=ALL), grant least-privilege accounts safely, take restorable backup with mysqldump, and recognize where replication can stall.

Databases·37 commands·Last updated 2026-07-21

Connection & User 7

mysql -u root -p -h 127.0.0.1 -P 3306

Interactive login, -p prompts for password, -h host, -P port

mysql -u root -p db_name < dump.sql

Import SQL file into a database

mysqldump -u root -p --single-transaction db_name > backup.sql

Consistent 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\G

View 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\G

View 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\G

InnoDB engine status with deadlock info and LATEST DETECTED DEADLOCK

Backup & Recovery 5

mysqldump -u root -p --all-databases --routines --triggers > all.sql

Full backup including stored procedures and triggers

mysqldump -u root -p --single-transaction --master-data=2 db > db.sql

Include binlog position for replica setup

mysqlbinlog --start-datetime="2026-01-01 00:00:00" mysql-bin.000123 | mysql -u root -p

Point-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\G

View replica status (MySQL 5.7), check Slave_IO_Running and Slave_SQL_Running

SHOW REPLICA STATUS\G

View 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

Typical Use Case

"The page API got slow" is the typical entry point. Start with SHOW PROCESSLIST to check for long transactions or metadata locks, then run EXPLAIN on a suspected slow query — type=ALL or an empty key means no index is used, so consider a covering index or rewriting the WHERE/ORDER BY to hit one. If a query is slow even with an index, look for a full filesort or excessive row lookups. For backup, use mysqldump for a full logical backup plus binlog for incremental. When replication stalls, check SHOW SLAVE STATUS for Last_SQL_Error / Seconds_Behind_Master, fix or skip and re-run. Before onboarding a new user, grant only the least privileges on the needed database, never ALL PRIVILEGES ON *.*.

Command Examples

Use EXPLAIN to check whether a query uses an index

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at;

type=ref 且 key=idx_user 说明命中索引；这里 Extra 里的 Using filesort 提示 ORDER BY 未走索引，若有性能压力可在 (user_id, created_at) 上建联合索引消除排序。

Output

id  select_type  table   type  key         key_len  rows  Extra
1   SIMPLE       orders  ref   idx_user    4        3     Using index condition; Using filesort

Dump and restore a single database

mysqldump -u backup -p --single-transaction --routines --triggers mydb > mydb.sql
mysql -u root -p < mydb.sql

--single-transaction 用 InnoDB 事务一致性快照导出，线上导备份不加锁；恢复时直接重定向 sql 文件到 mysql 客户端执行。

Inspect replication status

SHOW SLAVE STATUS\G

SQL 线程停住时先看 Last_SQL_Error 与 Last_Errno；若是可跳过的重复主键错误，定位后可用跳过或手动修复语句，再 START SLAVE SQL_THREAD 恢复。

Output

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Error 'Duplicate entry' for key 'PRIMARY'
Seconds_Behind_Master: NULL

Common Pitfalls

Never ALTER a large table directly in production, it locks the table and blocks DML; use pt-online-schema-change or gh-ost instead.
Confirm the WHERE clause has an index before bulk UPDATE/DELETE; otherwise the lock range grows and can lock the whole table.
A full backup strategy is mysqldump full + binlog incremental. Exporting periodic dumps without binlog means you can only recover to the last dump point.
Keep privileges minimal; avoid ALL PRIVILEGES ON *.* and never grant broad access to non-admin accounts.
Seconds_Behind_Master = 0 does not prove zero lag; it can distort across large transactions. Judge together with relay log position and Last_SQL_Error.

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.

FAQ

How do I reset a forgotten MySQL root password?

Stop mysqld, then start it with skip-grant-tables to bypass the grant tables (or provide an init-file containing an ALTER USER statement that runs at startup), log in, reset the password with ALTER USER, then restart without that flag and run FLUSH PRIVILEGES. Remember that skip-grant-tables disables authentication entirely and should be used only during a maintenance window.

Should I use an auto-increment id or a UUID as the primary key in MySQL?

An auto-increment primary key is compact and write-friendly, suitable for single databases and append-heavy inserts. A UUID suits distributed merges or when a non-guessable global key is required. If you use UUIDs, store them in binary or as ordered UUIDs, otherwise index size and random writes grow on large tables.

What does type=ALL in EXPLAIN mean, and how do I optimize it in MySQL?

type=ALL means a full table scan, which usually indicates the query uses no usable index and performs terribly with many rows. Optimize by creating appropriate indexes on the columns used in WHERE and ORDER BY, using a covering index to avoid table lookups, and rewriting the SQL or adding LIMIT when possible.

How do I take a consistent, non-locking backup of an InnoDB database with mysqldump in MySQL?

Pass --single-transaction for InnoDB to export from a consistent snapshot, and add --master-data=2 to record the binary-log position, so the backup barely blocks writes. Restore with a mysql < dump.sql redirect. For large databases, prefer purpose-built logical or physical backup tools instead.

Why does altering a large table (adding a column or changing a type) stall production in MySQL?

Most ALTERs rebuild the table and take a metadata lock; on large tables they copy data and consume resources, which can block reads and writes and lag replication. Modern versions can use ALGORITHM=INPLACE and LOCK=NONE to run more online, but still validate on a replica first, run during a low-traffic window, and assess long-transaction impact.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Official Docs

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.