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

Parameter	Effect	Example
`-u`	Specify the login user	`mysql -u root -p`
`-h`	Specify the host address	`mysql -h 127.0.0.1 -u app`
`-P`	Specify the port (default 3306)	`mysql -h 127.0.0.1 -P 3307`
`--single-transaction`	Consistent logical backup (no table lock for InnoDB)	`mysqldump --single-transaction -u root -p db > db.sql`
`--master-data`	Record the binlog position in the backup, convenient for setting up a replica	`mysqldump --master-data=2 -u root -p db > db.sql`
`EXPLAIN`	View the query execution plan to locate full table scans	`EXPLAIN SELECT * FROM orders WHERE user_id=1;`
`SHOW PROCESSLIST`	View current connections and running statements	`SHOW PROCESSLIST;`
`innodb_buffer_pool_size`	Buffer pool size, affecting the hit rate	`SET GLOBAL innodb_buffer_pool_size=2G;`
`slow_query_log`	Enable the slow query log	`SET GLOBAL slow_query_log=ON;`
`--quick`	Streaming export, reducing memory use	`mysqldump --quick -u root -p db > db.sql`

Common pitfalls

SymptomThe connection reports Too many connections.

Causemax_connections is too small, connections are not released, or the application connection pool is misconfigured.

FixUse SHOW PROCESSLIST to see the sources; raise max_connections; use a connection pool with a timeout on the application side; investigate slow queries holding connections.

SymptomUPDATE/DELETE forgot the WHERE clause and modified the whole table by mistake.

CauseThe SQL had no condition, or it ran in the wrong environment by mistake.

FixBefore a dangerous operation, run a SELECT to confirm the affected rows; enable SQL_SAFE_UPDATES; BEGIN first, then COMMIT only after confirming.

SymptomReplication lag keeps growing.

CauseSingle-threaded replay on the replica, large transactions, or a replica hardware/network bottleneck.

FixUse SHOW SLAVE STATUS to check Seconds_Behind_Master and the lagging transaction; split large transactions; upgrade the replica or use multi-threaded replication (slave_parallel_workers).

SymptomThe query is very slow and EXPLAIN shows ALL (full table scan).

CauseNo suitable index, or the query applies a function / implicit type conversion to the indexed column.

FixIndex the WHERE/JOIN/ORDER BY columns; avoid index-defeating forms like WHERE DATE(created)=...

SymptomRunning DDL (adding a column) locks the whole table and stalls production.

CauseMySQL before 5.6, or certain DDLs, require table-locking copy.

FixPrefer Online DDL (the default in MySQL 8); for large tables use gh-ost/pt-online-schema-change for lock-free changes.

Symptommysqldump locks tables during backup, blocking writes.

Cause--single-transaction was omitted, taking a global read lock on non-transactional tables.

FixAdd --single-transaction for InnoDB; only MyISAM needs --lock-all-tables, and prefer off-peak hours.

Troubleshooting

1View live connections and sources
SHOW PROCESSLIST;
Locate which hosts/apps are exhausting connections, distinguishing slow queries from connection leaks.
2Locate slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
Use it with slow_query_log and long_query_time.
3View replication status
SHOW SLAVE STATUS\G
Watch Seconds_Behind_Master and Last_Error to locate the cause of lag and interruptions.
4Restore a single database from backup
mysql -u root -p db < db.sql
Confirm backup consistency first; for accidental deletions, point-in-time recovery from the binlog is more precise.

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 and key=idx_user mean the index is hit; here Using filesort in Extra indicates ORDER BY is not using the index — if under performance pressure, add a composite index on (user_id, created_at) to eliminate the sort.

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 exports with a consistent InnoDB transaction snapshot, so taking a backup online does not lock; to restore, redirect the sql file straight into the mysql client.

Inspect replication status

SHOW SLAVE STATUS\G

When the SQL thread stops, check Last_SQL_Error and Last_Errno first; for a skippable duplicate-primary-key error, after locating it you can skip it or fix it manually, then START SLAVE SQL_THREAD to resume.

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?