All cheatsheets

MySQL Commands Cheat Sheet

Quick reference for MySQL / MariaDB: client commands, DDL/DML statements, joins/aggregates, indexes, transactions and backup/restore — with common options and practical examples.

38 commands

Connection

mysql -h <host> -P <port> -u <user> -p <db>

Open an interactive MySQL client session.

-h host; -P 3306 default; -u user; -p password; -D db; -e cmd; -f force continue; --safe-updates

mysql -h mysql.internal -u app -p app_production
mysql -u root -p -e "SHOW DATABASES;"

Run one or more SQL statements from the shell without entering the REPL.

--tee file capture output; --html html format; --table tab-separated

mysql -u root -p -e "SHOW SLAVE STATUS\G"

Meta & Info

SHOW DATABASES

List all databases visible to the user.

SHOW SCHEMAS is a synonym

SHOW DATABASES LIKE 'app%';
SHOW TABLES / DESCRIBE

List tables in the current db; DESCRIBE columns of a single table.

SHOW FULL TABLES; DESCRIBE t; SHOW COLUMNS FROM t

SHOW FULL TABLES WHERE Table_type != 'BASE TABLE';
SHOW CREATE TABLE <t>

Print the exact statement that recreates the table — handy for migrations.

SHOW CREATE TABLE orders\G
EXPLAIN <statement>

Show the query plan and index usage. Prefix with FORMAT=JSON for tooling.

EXPLAIN FORMAT=JSON; EXPLAIN ANALYZE (MySQL 8.0+) runs the query; EXTENDED/traditional deprecated

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
SHOW STATUS / SHOW VARIABLES

Inspect server health counters and tunable variables.

SHOW GLOBAL STATUS LIKE 'Threads_running'; SHOW VARIABLES LIKE 'innodb_buffer_pool_size%';

SHOW GLOBAL STATUS LIKE 'Slow_queries';

Database & Schema

CREATE DATABASE <db>

Create a new database with optional character set and collation.

CHARACTER SET utf8mb4; COLLATE utf8mb4_0900_ai_ci; ENCRYPTION 'Y' (8.0.16+)

CREATE DATABASE app_dev CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE <db> / DROP DATABASE <db>

Switch the default database or drop an existing one.

USE empties the last result and sets DB; DROP DATABASE is unrecoverable

DROP DATABASE IF EXISTS app_old;

Tables & DDL

CREATE TABLE

Define a new table — columns, keys, defaults and engine options.

ENGINE=InnoDB; DEFAULT CHARSET=utf8mb4; AUTO_INCREMENT=<n>; KEY/UNIQUE/PRIMARY KEY constraints

CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, total DECIMAL(10,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_created (user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE

Add/rename/drop columns and indexes, change engine, set options.

ADD COLUMN; DROP COLUMN; MODIFY/ALTER COLUMN; ADD/DROP INDEX; RENAME; ORDER BY; ALGORITHM=INSTANT|INPLACE|COPY

ALTER TABLE orders ADD COLUMN status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending', ADD INDEX idx_status (status);
DROP TABLE / TRUNCATE TABLE

Drop a table (CASCADE optional) or empty it fast (resets AUTO_INCREMENT).

DROP TABLE IF EXISTS; TRUNCATE TABLE; FOREIGN_KEY_CHECKS=0 to allow inter-table ops

TRUNCATE TABLE staging.events;
RENAME TABLE <a> TO <b>

Atomic rename — handy for hot-swap with a shadow table.

RENAME TABLE orders TO orders_old, orders_new TO orders;

DML

INSERT INTO <t> [(cols)] VALUES ...

Insert one or more rows; multi-row INSERT is faster than many single inserts.

INSERT IGNORE; ON DUPLICATE KEY UPDATE (upsert); INSERT ... SELECT; LAST_INSERT_ID()

INSERT INTO orders(user_id, total) VALUES (1, 9.99), (1, 4.50), (2, 19.95);
INSERT ... ON DUPLICATE KEY UPDATE

Upsert — update if a unique/primary key collides; affected-rows returns 2.

VALUES(col) for old/new; LAST_INSERT_ID() trick for auto-increment chain

INSERT INTO counters(id, hits) VALUES (1, 1) ON DUPLICATE KEY UPDATE hits = hits + 1
REPLACE INTO ...

Shorthand for delete+insert on a unique-key collision (deprecated pattern).

REPLACE INTO settings(k, v) VALUES ('theme', 'dark')
UPDATE / DELETE

Modify or remove rows. Without WHERE, the entire table is affected.

UPDATE ... ORDER BY ... LIMIT n; multi-table UPDATE t1, t2 SET ... WHERE ...; ON DELETE CASCADE

UPDATE orders SET status='paid' WHERE user_id = 42 AND status='pending' ORDER BY created_at LIMIT 100;
DELETE ... ORDER BY ... LIMIT N

Slowly delete big chunks of rows to bound undo logs and lock duration.

LIMIT N; REPEAT the loop; consider truncation for all rows

DELETE FROM events WHERE created_at < now() - INTERVAL 30 DAY ORDER BY id LIMIT 5000

Query & Filter

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ... OFFSET ...

Read rows with filter, sort and pagination.

DISTINCT; IN (subquery) / ANY / ALL; BETWEEN; IS NULL; REGEXP; STRAIGHT_JOIN hint

SELECT id, status FROM orders WHERE user_id = 42 AND status IN ('paid','shipped') ORDER BY created_at DESC LIMIT 10 OFFSET 20;
GROUP BY ... HAVING ...

Aggregate rows then filter on the result.

sql_mode=ONLY_FULL_GROUP_BY (8.0+); WITH ROLLUP adds grand-total row

SELECT user_id, count(*) AS n, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3 ORDER BY gmv DESC LIMIT 50;

Joins

[INNER | LEFT | RIGHT | CROSS] JOIN ... ON ...

Combine rows from multiple tables.

STRAIGHT_JOIN; USING(col); nested-loop/hash only on MySQL 8.0+; JSON_TABLE

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email ORDER BY count(o.id) DESC;
WITH cte AS (...) SELECT ...

Common Table Expressions — recursive support since 8.0.

WITH RECURSIVE org AS (SELECT id, manager_id, name FROM staff WHERE manager_id IS NULL UNION ALL SELECT s.id, s.manager_id, s.name FROM staff s JOIN org o ON s.manager_id = o.id) SELECT * FROM org;

Aggregate

COUNT() / SUM() / AVG() / MIN() / MAX() / GROUP_CONCAT()

Built-in aggregate functions.

COUNT(DISTINCT x); GROUP_CONCAT(col ORDER BY col SEPARATOR ',')

SELECT day(created_at) AS d, sum(total) AS gmv, count(*) AS n FROM orders GROUP BY day(created_at) ORDER BY d DESC LIMIT 7;

Index & View

CREATE INDEX ...

Add a secondary index to accelerate lookups and ORDER BY.

UNIQUE; FULLTEXT/SPATIAL (storage-engine aware); ON tbl(col, ...) for composite; INVISIBLE flag (8.0+); DESC on key parts

CREATE INDEX idx_user_created ON orders(user_id, created_at DESC) ALGORITHM=INPLACE LOCK=NONE;
DROP INDEX / ALTER TABLE ... DROP INDEX

Remove an index. With ALTER TABLE you can add and drop in one statement.

ALTER TABLE orders DROP INDEX idx_user_created;
CREATE VIEW / CREATE OR REPLACE VIEW

Stored SELECT; updatable views support INSERT/UPDATE/DELETE under restrictions.

CREATE OR REPLACE; WITH CHECK OPTION enforces WHERE filter on writes; ALGORITHM=MERGE|TEMPTABLE

CREATE OR REPLACE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW v_user_orders AS SELECT user_id, count(*) AS n FROM orders GROUP BY user_id;

Transactions

START TRANSACTION / COMMIT / ROLLBACK

Wrap statements in an ACID unit; default is autocommit (no Tx needed for single statements).

START TRANSACTION WITH CONSISTENT SNAPSHOT; COMMIT; ROLLBACK TO SAVEPOINT

START TRANSACTION; UPDATE ...; ROLLBACK;
SET autocommit = 0; SET TRANSACTION ISOLATION LEVEL ...

Disable per-statement autocommit; change isolation: REPEATABLE READ (InnoDB default), READ COMMITTED, READ UNCOMMITTED, SERIALIZABLE.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET autocommit = 0;
SELECT ... FOR UPDATE / LOCK IN SHARE MODE

Row-level locks for safety under concurrency. NOWAIT/SKIP LOCKED available in 8.0+.

FOR UPDATE NOWAIT / SKIP LOCKED

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Backup & Restore

mysqldump -u <user> -p <db> > dump.sql

Logical backup: emit SQL INSERTs / DDL to a file.

--single-transaction for InnoDB; --routines; --triggers; --no-data schema only; --add-drop-table; --column-statistics=0

mysqldump -u root -p --single-transaction --routines --triggers --events app > /backup/app-$(date +%F).sql
mysql -u root -p <db> < dump.sql

Replay a SQL dump into a (target) database.

--force; --auto-rehash; --comments

mysql -u root -p app_new < /backup/app.sql
SOURCE /path/script.sql

Run a SQL script from inside the REPL.

source ends without terminator needed for the last statement; \. in the CLI

SOURCE /tmp/migrations/0002_add_index.sql;
mysqlbinlog binlog.000001 > incr.sql

Convert binary logs into SQL for point-in-time recovery.

--start-datetime; --stop-datetime; --start-position; --stop-position

mysqlbinlog --start-datetime='2026-07-23 00:00:00' /var/lib/mysql/binlog.000123 > /inc.sql

User & Permission

CREATE USER / ALTER USER / DROP USER

Manage MySQL accounts.

IDENTIFIED BY '...'; IDENTIFIED WITH caching_sha2_password; WITH MAX_CONNECTIONS 10;

CREATE USER 'reporting'@'%' IDENTIFIED BY 'P@ssw0rd!'; ALTER USER 'app'@'%' PASSWORD EXPIRE NEVER;
GRANT / REVOKE

Object and global privileges; combinations determine effective capabilities.

GRANT SELECT ON app.* TO 'reporting'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'%';
FLUSH PRIVILEGES

Reload in-memory grant tables after manual changes (not needed after GRANT statements).

FLUSH HOSTS / LOGS / TABLES

FLUSH PRIVILEGES;

Diagnostics

SHOW PROCESSLIST / KILL <id>

Show running threads; KILL long-running or stuck queries.

SHOW FULL PROCESSLIST gives full SQL; KILL CONNECTION aborts client; KILL QUERY stops query only

SHOW FULL PROCESSLIST\G; KILL 12345;
SHOW ENGINE INNODB STATUS\G

Detailed InnoDB diagnostics: locks, transactions, buffer pool, history.

PERFORMANCE_SCHEMA queries: SELECT * FROM performance_schema.events_statements_summary_by_digest_by_error;

SHOW ENGINE INNODB STATUS\G

Cheatsheet version 1.0.0

Covers MySQL 8.0+