MySQL Cheatsheet - Queries, Joins, Users & Indexes

Q: 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.

Q: 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.

Q: 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.

Q: 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.

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

Typical Use Case

Daily database operations: connect, create schemas and tables, manage users and privileges, backup and restore, locate slow queries, and troubleshoot connection exhaustion, replication lag and lock waits.

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

Parameter matrix

参数	Effect	Example
`-u`	指定登录用户	`mysql -u root -p`
`-h`	指定主机地址	`mysql -h 127.0.0.1 -u app`
`-P`	指定端口（默认 3306）	`mysql -h 127.0.0.1 -P 3307`
`--single-transaction`	一致性逻辑备份（InnoDB 不锁表）	`mysqldump --single-transaction -u root -p db > db.sql`
`--master-data`	备份中记录 binlog 位点，便于搭建从库	`mysqldump --master-data=2 -u root -p db > db.sql`
`EXPLAIN`	查看查询执行计划，定位全表扫描	`EXPLAIN SELECT * FROM orders WHERE user_id=1;`
`SHOW PROCESSLIST`	查看当前连接与正在执行的语句	`SHOW PROCESSLIST;`
`innodb_buffer_pool_size`	缓冲池大小，影响命中率	`SET GLOBAL innodb_buffer_pool_size=2G;`
`slow_query_log`	开启慢查询日志	`SET GLOBAL slow_query_log=ON;`
`--quick`	流式导出，减少内存占用	`mysqldump --quick -u root -p db > db.sql`

Common pitfalls

Symptom连接报 Too many connections。

Causemax_connections 过小、连接未释放，或应用连接池配置不当。

FixSHOW PROCESSLIST 看来源；调大 max_connections；应用侧用连接池并设超时；排查慢查询占住连接。

SymptomUPDATE/DELETE 忘记加 WHERE，误改全表。

CauseSQL 未带条件，或误在错误环境执行。

Fix危险操作前先 SELECT 确认影响行数；开启 SQL_SAFE_UPDATES；先 BEGIN 再提交，确认无误再 COMMIT。

Symptom主从延迟持续增大。

Cause从库单线程回放、大事务、或从库硬件/网络瓶颈。

FixSHOW SLAVE STATUS 看 Seconds_Behind_Master 与延迟事务；拆大事务；升级从库或多线程复制（slave_parallel_workers）。

Symptom查询很慢，EXPLAIN 显示 ALL（全表扫描）。

Cause缺少合适索引，或查询对索引列做了函数/隐式类型转换。

Fix对 WHERE/JOIN/ORDER BY 列建索引；避免 WHERE DATE(created)=... 这类索引失效写法。

Symptom执行 DDL（加列）时锁全表，线上卡死。

CauseMySQL 5.6 之前或特定 DDL 需要锁表复制。

Fix优先用 Online DDL（MySQL 8 默认）；大表用 gh-ost/pt-online-schema-change 无锁变更。

Symptommysqldump 备份时锁表导致写入阻塞。

Cause未加 --single-transaction，对非事务表加全局读锁。

Fix对 InnoDB 加 --single-transaction；仅 MyISAM 才需 --lock-all-tables，并尽量在低峰期。

Troubleshooting

1查看实时连接与来源
SHOW PROCESSLIST;
定位是哪些主机/应用占满连接，区分慢查询与连接泄漏。
2定位慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
配合 slow_query_log 与 long_query_time 使用。
3查看主从复制状态
SHOW SLAVE STATUS\G
关注 Seconds_Behind_Master 与 Last_Error，定位延迟与中断原因。
4从备份恢复单库
mysql -u root -p db < db.sql
恢复前确认备份一致性；误删数据用 binlog 时间点恢复更精确。

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?