MySQL cheat sheet

A scannable MySQL reference: 17 short snippets across 9 topics, each linking back to the lesson it came from.

At a glance

Quick snippets

Installation, databases, users and engines

Install and connect

# Debian / Ubuntu
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation      # root password, removes anonymous accounts

# Docker, fastest for local experiments
docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8.4

# Client
mysql -h 127.0.0.1 -P 3306 -u app -p
mysql -u app -p -e "SELECT VERSION();"   # one statement, script friendly

Databases, tables and users

CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';

SHOW GRANTS FOR 'app'@'10.0.0.%';
SHOW CREATE TABLE app.orders;

-- change a password (MySQL 8 syntax)
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-newer-longer-password';

Full lesson: Installation, databases, users and engines →

Indexes and reading EXPLAIN

Composite indexes and the left prefix

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_items_product ON order_items (product_id);   -- foreign keys

-- uses the index: leading column is present
SELECT id, total FROM orders WHERE customer_id = 42 AND created_at >= '2026-01-01';

-- cannot seek: leading column missing
SELECT id FROM orders WHERE created_at >= '2026-01-01';

Reading a plan

EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

EXPLAIN FORMAT=JSON
SELECT id FROM orders WHERE customer_id = 42;      -- cost detail

EXPLAIN ANALYZE                                     -- 8.0.18+: actually runs it
SELECT id FROM orders WHERE customer_id = 42;

Full lesson: Indexes and reading EXPLAIN →

Replication, backup and operational pitfalls

Binary logging and replication

# primary: /etc/mysql/my.cnf
[mysqld]
server-id            = 1
log_bin              = mysql-bin
binlog_format        = ROW
binlog_expire_logs_seconds = 604800   # keep 7 days of point-in-time recovery
gtid_mode            = ON
enforce_gtid_consistency = ON

Binary logging and replication

-- on the replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.10',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'secret',
  SOURCE_AUTO_POSITION = 1;          -- derive position from GTIDs

START REPLICA;
SHOW REPLICA STATUS\G

Backups that actually restore

# logical dump: consistent without locking the whole server
mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 --set-gtid-purged=OFF app > app-$(date +%F).sql

# restore and verify
mysql app_restore_check < app-2026-09-18.sql
mysql app_restore_check -e "CHECKSUM TABLE orders EXTENDED;"

Full lesson: Replication, backup and operational pitfalls →

Querying data: SELECT, joins and aggregation

SELECT and filtering

SELECT   c.name, o.id, o.total
FROM     orders AS o
JOIN     customers AS c ON c.id = o.customer_id
WHERE    o.status = 'paid'
  AND    o.placed_at >= '2026-01-01'
  AND    c.country IN ('GB', 'DE')
GROUP BY c.name, o.id, o.total
HAVING   SUM(o.total) > 0
ORDER BY o.placed_at DESC
LIMIT    20 OFFSET 40;

Full lesson: Querying data: SELECT, joins and aggregation →

Writing data safely: DML and transactions

Transactions and savepoints

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

SAVEPOINT after_transfer;
INSERT INTO audit_log (note) VALUES ('transfer 1 -> 2');

-- undo only the log entry, keep both balance changes
ROLLBACK TO SAVEPOINT after_transfer;

COMMIT;

Full lesson: Writing data safely: DML and transactions →

JSON, generated columns and window functions

Generated columns

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(20)
      GENERATED ALWAYS AS (payload ->> '$.type') VIRTUAL,
  ADD INDEX idx_event_type (event_type);

ALTER TABLE orders
  ADD COLUMN total_with_tax DECIMAL(12,2)
      GENERATED ALWAYS AS (ROUND(total * 1.20, 2)) STORED;

EXPLAIN SELECT id FROM events WHERE event_type = 'click';

Full lesson: JSON, generated columns and window functions →

User management, privileges and security

Transport, authentication and safe defaults

CREATE USER 'svc'@'10.0.0.%'
  IDENTIFIED WITH caching_sha2_password BY 'a-long-random-secret'
  REQUIRE SSL;

SHOW STATUS LIKE 'Ssl_cipher';        -- empty means the session is not encrypted

Auditing and credential rotation

SELECT user, host, account_locked, password_expired FROM mysql.user;

-- rotate: create or change first, deploy, then disable the old credential
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-newer-longer-secret';

-- disable immediately without losing the grants
ALTER USER 'app'@'10.0.0.%' ACCOUNT LOCK;

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SELECT * FROM mysql.audit_log_user LIMIT 5;

Full lesson: User management, privileges and security →

High availability, Group Replication and upgrades

Choosing a topology

[mysqld]
server_id                        = 1
gtid_mode                        = ON
enforce_gtid_consistency         = ON
binlog_checksum                  = NONE
plugin_load_add                  = group_replication.so
group_replication_group_name     = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot  = OFF
group_replication_local_address  = "10.0.0.11:33061"
group_replication_group_seeds    = "10.0.0.11:33061,10.0.0.12:33061,10.0.0.13:33061"
group_replication_single_primary_mode = ON

Running the cluster

-- bootstrap exactly one member
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- then join the others
START GROUP_REPLICATION;

SELECT member_host, member_state, member_role
FROM performance_schema.replication_group_members;

SELECT * FROM performance_schema.replication_group_member_stats;

Backups and version upgrades

// MySQL Shell AdminAPI
dba.checkInstanceConfiguration("[email protected]:3306")
dba.configureInstance("[email protected]:3306")

var cluster = dba.getCluster();
cluster.status();
cluster.rescan();

Full lesson: High availability, Group Replication and upgrades →

Next steps: managed MySQL, Aurora and cloud operations

Parameter groups, replicas and scaling

aws rds create-db-parameter-group \
  --db-parameter-group-family mysql8.4 \
  --db-parameter-group-name app-mysql84 \
  --description "application tuning"

aws rds modify-db-parameter-group \
  --db-parameter-group-name app-mysql84 \
  --parameters "ParameterName=innodb_buffer_pool_size,ParameterValue=8589934592,ApplyMethod=immediate"

aws rds create-db-instance-read-replica \
  --db-instance-identifier app-rr-1 \
  --source-db-instance-identifier app-primary

Cost and monitoring

SELECT event_name, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1e9, 1) AS total_ms
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected','Threads_running','Slow_queries','Aborted_connects',
   'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');

Full lesson: Next steps: managed MySQL, Aurora and cloud operations →

FAQ

Related cheat sheets

SQL PostgreSQL MongoDB Redis SQLite

Last refreshed 2026-09-27.