MySQL Cheat Sheet

Docker: a server in one command

docker run -d --name mysql -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=change-me -e MYSQL_DATABASE=shop \
  -e MYSQL_USER=app -e MYSQL_PASSWORD=app-secret \
  -v mysql-data:/var/lib/mysql mysql:9.7

docker exec -it mysql mysql -u root -p          # client inside the container
docker logs mysql                               # first start prints the init progress

Linux, macOS, Windows

# Ubuntu / Debian: the distro package, or Oracle's APT repository for a chosen series
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation

# RHEL / Rocky / Alma: Oracle's YUM repository, then
sudo dnf install mysql-community-server && sudo systemctl enable --now mysqld
sudo grep 'temporary password' /var/log/mysqld.log

# macOS
brew install mysql            # or mysql@8.4 for the older LTS
brew services start mysql

# Windows: MySQL Installer or the ZIP archive, then run as a service
mysqld --initialize --console && mysqld --install && net start MySQL

The client, and the options you will type most

mysql -u app -p shop                        # prompt for the password, open database shop
mysql -h db.example.com -P 3306 -u app -p   # remote host and port
mysql -u root -p --protocol=tcp             # force TCP instead of the local socket
mysql -S /var/run/mysqld/mysqld.sock -u root -p
mysql -u app -p -e "SELECT NOW();" shop     # run one statement and exit
mysql -u app -p shop < schema.sql           # run a script
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=ca.pem -h db.example.com -u app -p

Stop typing the password: option files and login paths

# ~/.my.cnf  (chmod 600)
[client]
user = app
password = app-secret
host = 127.0.0.1

[mysql]
database = shop
prompt = \u@\h [\d]>\_

mysql_config_editor set --login-path=prod --host=db.example.com --user=app --password
mysql --login-path=prod shop
mysql_config_editor print --all

MySQL Shell: JavaScript, Python and SQL modes, plus admin utilities

mysqlsh app@db.example.com:3306/shop --sql
\sql   \js   \py                            # switch language
\connect root@localhost
util.checkForServerUpgrade()               # before any major upgrade
util.dumpSchemas(["shop"], "/backups/shop")   # parallel logical dump, see Backup

When a terminal is not the right tool

MySQL Workbench     Oracle's free client: modelling, visual EXPLAIN, migration wizard
DBeaver             free, every database, good data editor and ER diagrams
TablePlus, Sequel Ace (macOS), HeidiSQL (Windows)   fast native clients
DataGrip            JetBrains, strongest SQL completion and refactoring
phpMyAdmin, Adminer web interfaces; never expose them publicly without auth and TLS
VS Code             the MySQL Shell for VS Code or SQLTools extensions

Reach a private server through SSH

ssh -N -L 3307:127.0.0.1:3306 deploy@db.example.com       # then connect the client to 127.0.0.1:3307
mysql -h 127.0.0.1 -P 3307 -u app -p shop

Databases, tables, columns

SHOW DATABASES;
USE shop;
SELECT DATABASE();                     -- which database am I in
SHOW TABLES;
SHOW TABLES LIKE 'order%';
SHOW FULL TABLES WHERE Table_type = 'VIEW';
DESCRIBE orders;                       -- or DESC orders, SHOW COLUMNS FROM orders
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G

What DESCRIBE tells you

DESCRIBE orders;
-- Key: PRI primary key, UNI unique, MUL first column of a non unique index
-- Extra: auto_increment, DEFAULT_GENERATED, on update CURRENT_TIMESTAMP, VIRTUAL GENERATED, INVISIBLE

Server and session information

SELECT VERSION(), CURRENT_USER(), USER(), NOW(), @@hostname, @@port;
status                                  -- \s: version, connection, charset, uptime
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW WARNINGS;                          -- after a statement that reported warnings
SHOW ERRORS;
SHOW ENGINES;
SHOW PLUGINS;

\G for vertical output

SELECT * FROM orders WHERE id = 10042\G
SHOW ENGINE INNODB STATUS\G
mysql -u app -p -E -e "SELECT * FROM orders LIMIT 1" shop    # -E: vertical for every statement

Scripts, paging, logging a session

source /path/to/script.sql              -- or \. script.sql
pager less -S                           -- scroll wide results sideways; \n or nopager to stop
pager grep -i error                     -- filter any output
tee /tmp/session.log                    -- copy everything to a file; notee to stop
system clear                            -- \! runs a shell command
edit                                    -- \e opens $EDITOR with the current statement
\c                                      -- cancel a half typed statement
help SELECT                             -- server side help for any statement
quit                                    -- \q, exit, Ctrl+D

Output for other programs

mysql -N -B -e "SELECT id, email FROM users" shop > users.tsv     # no headers, tab separated
mysql --html -e "SELECT * FROM products LIMIT 5" shop > p.html
mysql --xml  -e "SELECT * FROM products LIMIT 5" shop
mysql -e "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM products" -N shop | jq .
mysql --safe-updates shop      # refuse UPDATE and DELETE without a key in WHERE or a LIMIT

User variables carry values between statements

SET @cutoff = '2026-09-01';
SELECT MAX(id) INTO @last_id FROM orders;
SELECT SUM(total) INTO @revenue FROM orders WHERE customer_id = 12 AND created_at >= @cutoff;
SELECT @last_id, @revenue;
-- user variables live until the connection closes and are never shared between connections

System variables: global, session, and where they came from

SELECT @@SESSION.sql_mode, @@GLOBAL.max_connections;
SET SESSION sort_buffer_size = 4 * 1024 * 1024;       -- this connection only
SHOW SESSION VARIABLES LIKE 'autocommit';
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('version', 'datadir', 'port', 'socket');

Server housekeeping from the shell

mysqladmin -u root -p ping                  # health check, exit code 0 when alive
mysqladmin -u root -p status
mysqladmin -u root -p processlist
mysqladmin -u root -p flush-logs            # rotate the error, slow and binary logs
mysqladmin -u root -p create shop_test
mysqladmin -u root -p shutdown

mysqlshow: databases, tables, columns from the shell

mysqlshow -u app -p                      # databases
mysqlshow -u app -p shop                 # tables
mysqlshow -u app -p shop orders          # columns of one table
mysqlshow -u app -p --count shop         # tables with row counts

mysqlimport: LOAD DATA from the shell

mysqlimport --local -u app -p --fields-terminated-by=',' --fields-optionally-enclosed-by='"' \
  --ignore-lines=1 shop /tmp/products.csv
mysqlimport --local --replace -u app -p shop /tmp/products.tsv     # tab separated by default; replace duplicates

sql_safe_updates for interactive sessions

SET SESSION sql_safe_updates = 1;
UPDATE products SET price = 0;                    -- refused
UPDATE products SET price = 0 WHERE id = 12;      -- fine: key column
SET SESSION sql_safe_updates = 0;                 -- when you really mean it
[mysql]
safe-updates                                      # in ~/.my.cnf: on for every interactive session

Create, use, drop

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE DATABASE IF NOT EXISTS shop;
USE shop;
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- default for new tables only
DROP DATABASE IF EXISTS shop_test;
-- in MySQL, DATABASE and SCHEMA are synonyms: CREATE SCHEMA shop works the same

How big is each table

SELECT table_name,
       table_rows AS rows_guess,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY size_mb DESC;
-- table_rows is an InnoDB estimate; COUNT(*) for the exact figure

utf8mb4, always

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SET NAMES utf8mb4;                                      -- the connection charset, drivers do this for you
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- rewrites the table
SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.columns
WHERE table_schema = 'shop' AND table_name = 'posts';

Collations decide equality and sort order

SELECT 'a' = 'A' COLLATE utf8mb4_0900_ai_ci,     -- ai = accent insensitive, ci = case insensitive
       'e' = 'é' COLLATE utf8mb4_0900_ai_ci,
       'a' = 'A' COLLATE utf8mb4_0900_as_cs;     -- as_cs: accent and case sensitive
SELECT * FROM users WHERE email = 'Ana@Example.com' COLLATE utf8mb4_0900_as_cs;
-- utf8mb4_bin compares bytes; utf8mb4_unicode_ci and utf8mb4_general_ci are the older, pre 8.0 choices

Which tables have a column

SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'shop' AND column_name LIKE '%email%'
ORDER BY table_name;

Tables without a primary key

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

Who references this table

SELECT table_name, column_name, constraint_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_schema = 'shop' AND referenced_table_name = 'customers';

Exact versus approximate

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE),
       CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));

price    DECIMAL(10,2) NOT NULL
quantity INT UNSIGNED NOT NULL DEFAULT 0
rating   FLOAT                        -- fine for a 4.7 star average
id       BIGINT UNSIGNED AUTO_INCREMENT

Strings: pick a sensible length

email    VARCHAR(254) NOT NULL          -- the practical email maximum
name     VARCHAR(120) NOT NULL
slug     VARCHAR(160) NOT NULL
country  CHAR(2) NOT NULL                -- ISO code
body     MEDIUMTEXT                      -- no DEFAULT allowed on TEXT except an expression
status   ENUM('draft','live','archived') NOT NULL DEFAULT 'draft'
uuid_bin BINARY(16)                      -- UUID_TO_BIN(UUID(), 1) keeps it time ordered

UUID keys without the index bloat

CREATE TABLE sessions (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  user_id BIGINT NOT NULL
);
SELECT BIN_TO_UUID(id, 1), HEX(id) FROM sessions LIMIT 1;
SELECT * FROM sessions WHERE id = UUID_TO_BIN('3f8a4c2e-9b1d-11f0-8de9-0242ac120002', 1);

DATETIME versus TIMESTAMP, and automatic times

created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
birthday   DATE,
opens_at   TIME,

SET time_zone = '+00:00';                  -- per session
SELECT @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();
SELECT CONVERT_TZ(created_at, '+00:00', 'Europe/Bucharest') FROM orders;   -- named zones need the tz tables loaded

VECTOR, MySQL 9.0

CREATE TABLE docs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  body TEXT,
  embedding VECTOR(1536)
);
INSERT INTO docs (body, embedding) VALUES ('Oak desk', STRING_TO_VECTOR('[0.12, -0.04, 0.33]'));
SELECT VECTOR_DIM(embedding), VECTOR_TO_STRING(embedding) FROM docs;

BOOLEAN and ENUM behaviour

is_active BOOLEAN NOT NULL DEFAULT TRUE          -- stored as TINYINT(1)
WHERE is_active                                   -- same as WHERE is_active <> 0
status ENUM('draft','live','archived')
ORDER BY status                                   -- draft, live, archived: list order, not alphabetical
ALTER TABLE posts MODIFY status ENUM('draft','live','archived','scheduled');   -- appending is instant
flags SET('featured','sale','new')                -- zero or more of the values: 'sale,new'
WHERE FIND_IN_SET('sale', flags)

Nearest stores with spatial types

CREATE TABLE stores (
  id INT PRIMARY KEY, name VARCHAR(80),
  location POINT NOT NULL SRID 4326,
  SPATIAL INDEX (location)
);
INSERT INTO stores VALUES (1, 'Bucharest 1', ST_GeomFromText('POINT(44.4268 26.1025)', 4326));
SELECT name, ROUND(ST_Distance_Sphere(location, ST_GeomFromText('POINT(44.43 26.05)', 4326)) / 1000, 2) AS km
FROM stores ORDER BY km LIMIT 5;
-- SRID 4326 axis order is latitude, longitude

Columns

ALTER TABLE orders ADD COLUMN coupon VARCHAR(40) NULL AFTER currency;
ALTER TABLE orders MODIFY COLUMN note VARCHAR(1000) NULL;          -- change type, keep the name
ALTER TABLE orders CHANGE COLUMN note customer_note VARCHAR(1000);  -- rename and redefine
ALTER TABLE orders RENAME COLUMN customer_note TO note;            -- rename only, 8.0+
ALTER TABLE orders ALTER COLUMN currency SET DEFAULT 'USD';
ALTER TABLE orders DROP COLUMN coupon;
ALTER TABLE orders ALTER COLUMN internal_ref SET VISIBLE;

Keys, constraints, the table itself

ALTER TABLE orders ADD INDEX ix_status_created (status, created_at);
ALTER TABLE orders DROP INDEX ix_status_created;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE orders DROP CHECK ck_orders_subtotal;
ALTER TABLE orders RENAME TO customer_orders;           -- or RENAME TABLE a TO b, c TO d
ALTER TABLE orders AUTO_INCREMENT = 100000;
ALTER TABLE orders ENGINE=InnoDB;                       -- rebuild in place, reclaims space

Online DDL: ask for INSTANT and fail fast

ALTER TABLE orders ADD COLUMN source VARCHAR(20) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX ix_source (source), ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ... Try ALGORITHM=COPY/INPLACE.
SELECT * FROM performance_schema.events_stages_current;     -- progress of a running ALTER
-- big tables on busy servers: gh-ost or pt-online-schema-change

Clone structure, clone data

CREATE TABLE orders_backup LIKE orders;                   -- same columns, indexes, no data
INSERT INTO orders_backup SELECT * FROM orders;
CREATE TABLE paid_2026 AS SELECT * FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01';   -- no indexes copied
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY);  -- visible to this session only, dropped on disconnect

TRUNCATE versus DELETE versus DROP

TRUNCATE TABLE logs;              -- empty it, reset AUTO_INCREMENT, no rollback
DELETE FROM logs;                 -- row by row, transactional, keeps AUTO_INCREMENT
DROP TABLE IF EXISTS logs;        -- remove the table itself
SET FOREIGN_KEY_CHECKS = 0;       -- session only; for imports and ordered drops, then set back to 1

Range partitions by month, and dropping old data instantly

CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));
ALTER TABLE events DROP PARTITION p2026_08;         -- a month gone in milliseconds

Inspect partitions and pruning

SELECT partition_name, table_rows FROM information_schema.partitions
WHERE table_schema = 'shop' AND table_name = 'events';
EXPLAIN SELECT COUNT(*) FROM events WHERE created_at >= '2026-10-01';   -- partitions column shows p2026_10,pmax

RESTRICT, CASCADE, SET NULL

FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE RESTRICT   -- keep history safe
FOREIGN KEY (order_id)    REFERENCES orders (id)    ON DELETE CASCADE    -- lines die with their order
FOREIGN KEY (manager_id)  REFERENCES employees (id) ON DELETE SET NULL   -- keep the row, drop the link
FOREIGN KEY (sku)         REFERENCES products (sku) ON UPDATE CASCADE    -- follow a renamed key

Find orphans before adding a constraint

SELECT o.id, o.customer_id FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;                          -- these rows would make ALTER TABLE ... ADD FOREIGN KEY fail

Columns, filter, sort, limit

SELECT id, name, price
FROM products
WHERE category_id = 3 AND price BETWEEN 100 AND 250
ORDER BY price DESC, name
LIMIT 10;

SELECT DISTINCT country FROM customers;
SELECT name AS product, price * 1.19 AS gross FROM products;   -- aliases; AS is optional
SELECT * FROM products LIMIT 20 OFFSET 40;                     -- or LIMIT 40, 20

Every WHERE operator you will use

WHERE status = 'paid'                  WHERE status <> 'paid'    -- or !=
WHERE status IN ('paid', 'shipped')    WHERE id NOT IN (1, 2, 3)
WHERE price BETWEEN 10 AND 20          -- inclusive both ends
WHERE name LIKE 'Oak%'                 -- % any run, _ one character
WHERE name LIKE '%50\%%'               -- a literal percent sign
WHERE name REGEXP '^(oak|walnut) '     -- regular expression, ICU based since 8.0
WHERE deleted_at IS NULL               WHERE note IS NOT NULL
WHERE (a = 1 OR b = 2) AND c = 3       -- AND binds tighter than OR: use parentheses
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

NULL handling

SELECT NULL = NULL, NULL IS NULL, NULL <=> NULL, COALESCE(NULL, 'n/a');
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE status <> 'blocked' OR status IS NULL;   -- keep the NULL rows too
SELECT IFNULL(discount, 0), NULLIF(qty, 0) FROM order_items;           -- NULLIF avoids division by zero
ORDER BY shipped_at IS NULL, shipped_at;                               -- NULLs last in ascending order

CASE and IF

SELECT name, price,
       CASE
         WHEN price < 50 THEN 'budget'
         WHEN price < 300 THEN 'mid'
         ELSE 'premium'
       END AS band
FROM products;

SELECT IF(stock > 0, 'in stock', 'sold out') FROM products;
SELECT CASE status WHEN 'paid' THEN 1 WHEN 'shipped' THEN 2 ELSE 9 END FROM orders;

Pagination: OFFSET for small, keyset for large

-- page 3 with OFFSET: simple, slows down as the page number grows
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- keyset: pass the last id you showed
SELECT id, name FROM products WHERE id > 1040 ORDER BY id LIMIT 20;

-- keyset on a non unique sort column: add the id as a tie breaker
SELECT id, created_at FROM orders
WHERE (created_at, id) < ('2026-09-21 09:04:11', 10042)
ORDER BY created_at DESC, id DESC LIMIT 20;

Random rows without sorting the whole table

SELECT * FROM products ORDER BY RAND() LIMIT 5;       -- fine below ~10k rows

SELECT p.* FROM products p
JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products)) AS rid) r
WHERE p.id >= r.rid ORDER BY p.id LIMIT 1;            -- fast, slightly biased by id gaps

Tuples, case sensitive matches, several columns

WHERE (customer_id, status) IN ((12, 'paid'), (15, 'shipped'))   -- row constructors
WHERE BINARY sku = 'oak-1'                                        -- case sensitive compare
WHERE email = 'Ana@x.com' COLLATE utf8mb4_bin
WHERE CONCAT_WS(' ', first_name, last_name, email) LIKE '%ana%'   -- simple multi column search (no index)
WHERE sku REGEXP BINARY '^OAK'                                    -- case sensitive regular expression

Empty or missing, in one test

WHERE phone IS NULL OR phone = ''
WHERE NULLIF(TRIM(phone), '') IS NULL
UPDATE customers SET phone = NULL WHERE TRIM(phone) = '';   -- normalise once, then keep it clean

Several tables, self joins, USING

SELECT o.id, c.name, p.name AS product, oi.qty
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p    ON p.id = oi.product_id
WHERE o.id = 10042;

SELECT e.name, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;   -- self join

SELECT * FROM orders JOIN customers USING (customer_id);        -- when both columns share a name

FULL OUTER JOIN, the MySQL way

SELECT a.id, b.id FROM a LEFT JOIN b ON b.a_id = a.id
UNION ALL
SELECT a.id, b.id FROM a RIGHT JOIN b ON b.a_id = a.id WHERE a.id IS NULL;

LATERAL: the latest three orders per customer

SELECT c.name, last3.id, last3.created_at
FROM customers c,
LATERAL (
  SELECT o.id, o.created_at FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC LIMIT 3
) AS last3;

Combine result sets

SELECT email FROM customers
UNION                      -- distinct rows
SELECT email FROM newsletter;

SELECT email FROM customers UNION ALL SELECT email FROM newsletter;   -- keep duplicates, faster
SELECT email FROM customers INTERSECT SELECT email FROM newsletter;   -- in both, 8.0.31+
SELECT email FROM customers EXCEPT SELECT email FROM newsletter;      -- in the first only, 8.0.31+
(SELECT id FROM a ORDER BY id LIMIT 5) UNION ALL (SELECT id FROM b ORDER BY id LIMIT 5);

Index the join columns

ALTER TABLE order_items ADD INDEX ix_order (order_id), ADD INDEX ix_product (product_id);
EXPLAIN FORMAT=TREE
SELECT c.name, SUM(oi.qty) FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.name;
-- look for "Index lookup on oi using ix_order" rather than "Inner hash join"

Force the join order when statistics mislead

SELECT STRAIGHT_JOIN c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'RO';
SELECT /*+ JOIN_ORDER(c, o) */ c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id;
SELECT /*+ NO_HASH_JOIN(c, o) */ ...;

GROUP BY and HAVING

SELECT status,
       COUNT(*) AS orders,
       SUM(total) AS revenue,
       ROUND(AVG(total), 2) AS avg_order
FROM orders
WHERE created_at >= '2026-09-01'       -- filters rows before grouping
GROUP BY status
HAVING COUNT(*) > 100                   -- filters groups after grouping
ORDER BY status;

COUNT variants and NULLs

SELECT COUNT(*), COUNT(phone), COUNT(DISTINCT country) FROM customers;
SELECT MIN(created_at), MAX(created_at) FROM orders;
SELECT SUM(qty * price) FROM order_items WHERE order_id = 10042;
SELECT COALESCE(SUM(total), 0) FROM orders WHERE customer_id = 999;   -- SUM of no rows is NULL

GROUP_CONCAT and JSON aggregation

SELECT o.customer_id, GROUP_CONCAT(DISTINCT p.sku ORDER BY p.sku SEPARATOR ', ') AS skus
FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id
GROUP BY o.customer_id;
SET SESSION group_concat_max_len = 1000000;     -- default 1024 bytes, output is silently cut
SELECT JSON_ARRAYAGG(sku), JSON_OBJECTAGG(sku, price) FROM products;

ONLY_FULL_GROUP_BY and error 1055

SELECT customer_id, created_at, COUNT(*) FROM orders GROUP BY customer_id;       -- error 1055
SELECT customer_id, MAX(created_at), COUNT(*) FROM orders GROUP BY customer_id;  -- aggregate it
SELECT c.id, c.name, COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;                               -- fine: name depends on the primary key
SELECT customer_id, ANY_VALUE(currency) FROM orders GROUP BY customer_id;

Pivot with conditional aggregation

SELECT c.country,
       SUM(o.status = 'paid')     AS paid,
       SUM(o.status = 'shipped')  AS shipped,
       SUM(o.status = 'refunded') AS refunded
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.country;
-- a boolean is 1 or 0 in MySQL, so SUM(condition) counts matching rows

Subtotals and grand total with ROLLUP

SELECT country, status, SUM(total) AS revenue
FROM orders JOIN customers c ON c.id = orders.customer_id
GROUP BY country, status WITH ROLLUP;
SELECT IF(GROUPING(country), 'ALL', country) AS country, SUM(total)
FROM orders JOIN customers c ON c.id = orders.customer_id
GROUP BY country WITH ROLLUP;               -- GROUPING() tells a subtotal NULL from a real NULL, 8.0+

Per hour, day, week, month

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour, COUNT(*) AS orders
FROM orders WHERE created_at >= CURDATE() GROUP BY hour ORDER BY hour;
GROUP BY DATE(created_at)                                   -- per day
GROUP BY YEARWEEK(created_at, 3)                            -- ISO weeks
GROUP BY DATE_FORMAT(created_at, '%Y-%m')                   -- per month
GROUP BY FLOOR(UNIX_TIMESTAMP(created_at) / 900)            -- 15 minute buckets

Include empty buckets

WITH RECURSIVE d AS (SELECT CURDATE() - INTERVAL 29 DAY AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM d WHERE day < CURDATE())
SELECT d.day, COALESCE(x.n, 0) AS orders
FROM d LEFT JOIN (SELECT DATE(created_at) AS day, COUNT(*) AS n FROM orders
                  WHERE created_at >= CURDATE() - INTERVAL 29 DAY GROUP BY DATE(created_at)) x USING (day)
ORDER BY d.day;

Scalar, list, derived table, correlated

SELECT name, price, (SELECT AVG(price) FROM products) AS avg_price FROM products;           -- scalar
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);    -- list
SELECT t.status, t.n FROM (SELECT status, COUNT(*) n FROM orders GROUP BY status) AS t;     -- derived, needs an alias
SELECT p.* FROM products p
WHERE p.price > (SELECT AVG(price) FROM products x WHERE x.category_id = p.category_id);   -- correlated

EXISTS, and why NOT IN bites

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);   -- empty if product_id has one NULL
SELECT * FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

WITH: name the steps

WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
  FROM orders WHERE status IN ('paid', 'shipped')
  GROUP BY month
),
ranked AS (
  SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev
  FROM monthly
)
SELECT * FROM ranked ORDER BY month DESC LIMIT 12;

WITH RECURSIVE: trees and series

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(500)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1, CONCAT(t.path, ' > ', c.name)
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT id, name, depth, path FROM tree ORDER BY path;

WITH RECURSIVE days AS (
  SELECT DATE('2026-09-01') AS d UNION ALL SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-09-30'
)
SELECT d, COUNT(o.id) FROM days LEFT JOIN orders o ON DATE(o.created_at) = d GROUP BY d;   -- days with zero orders too
-- cte_max_recursion_depth (default 1000) stops runaway recursion

Reuse an expensive intermediate result

CREATE TEMPORARY TABLE tmp_top_customers (PRIMARY KEY (customer_id)) AS
SELECT customer_id, SUM(total) AS revenue FROM orders
WHERE created_at >= '2026-01-01' GROUP BY customer_id HAVING revenue > 5000;

SELECT c.name, t.revenue FROM tmp_top_customers t JOIN customers c ON c.id = t.customer_id;
SELECT COUNT(*) FROM tmp_top_customers;
DROP TEMPORARY TABLE tmp_top_customers;

Rewrite a slow correlated subquery as a join

-- runs the subquery once per customer
SELECT c.id, (SELECT MAX(created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order FROM customers c;
-- one pass over orders, grouped once
SELECT c.id, lo.last_order FROM customers c
LEFT JOIN (SELECT customer_id, MAX(created_at) AS last_order FROM orders GROUP BY customer_id) lo ON lo.customer_id = c.id;

Window function, join to MAX, NOT EXISTS

SELECT customer_id, id, created_at FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn FROM orders o
) t WHERE rn = 1;

SELECT o.customer_id, o.id, o.created_at FROM orders o
JOIN (SELECT customer_id, MAX(created_at) AS m FROM orders GROUP BY customer_id) x
  ON x.customer_id = o.customer_id AND x.m = o.created_at;        -- returns ties twice

SELECT o.customer_id, o.id, o.created_at FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders n WHERE n.customer_id = o.customer_id AND n.created_at > o.created_at);

JOIN multiplies rows, EXISTS does not

SELECT c.* FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid';   -- duplicates
SELECT DISTINCT c.* FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid';   -- extra sort
SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');

Update from an aggregate

UPDATE customers c
JOIN (SELECT customer_id, SUM(total) AS ltv, MAX(created_at) AS last_at FROM orders GROUP BY customer_id) x
  ON x.customer_id = c.id
SET c.lifetime_value = x.ltv, c.last_order_at = x.last_at;

Top N per group, and removing duplicates

SELECT * FROM (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
  FROM products p
) t WHERE rn <= 3;                                  -- best 3 per category

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users
  ) d WHERE rn > 1
);                                                  -- keep the oldest row per email

LAG, LEAD, moving averages, shares

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       CONCAT(ROUND((revenue / LAG(revenue) OVER (ORDER BY month) - 1) * 100, 1), '%') AS growth
FROM monthly_revenue;

SELECT day, visits, AVG(visits) OVER (ORDER BY day ROWS 6 PRECEDING) AS avg_7d FROM traffic;
SELECT name, sales, ROUND(100 * sales / SUM(sales) OVER (), 1) AS pct_of_total FROM products;
SELECT id, NTILE(4) OVER (ORDER BY total) AS quartile FROM orders;
SELECT id, FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS cheapest FROM products;

ROWS versus RANGE, and LAST_VALUE

SUM(total) OVER (ORDER BY day)                                              -- RANGE: ties added together
SUM(total) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG(total) OVER (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)   -- calendar window
LAST_VALUE(price) OVER (PARTITION BY sku ORDER BY changed_at
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)       -- the real last

Consecutive day streaks (gaps and islands)

WITH d AS (
  SELECT DISTINCT user_id, DATE(created_at) AS day FROM logins
), g AS (
  SELECT user_id, day, day - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) DAY AS grp FROM d
)
SELECT user_id, MIN(day) AS streak_from, MAX(day) AS streak_to, COUNT(*) AS days
FROM g GROUP BY user_id, grp HAVING days >= 3 ORDER BY user_id, streak_from;

PERCENT_RANK, CUME_DIST, NTILE

SELECT id, total,
       ROUND(PERCENT_RANK() OVER (ORDER BY total), 4) AS percent_rank,
       CUME_DIST() OVER (ORDER BY total) AS cume_dist,
       NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders WHERE customer_id = 12;

Median without a MEDIAN function

SELECT ROUND(AVG(total), 2) AS median FROM (
  SELECT total, ROW_NUMBER() OVER (ORDER BY total) AS rn, COUNT(*) OVER () AS n
  FROM orders WHERE status = 'paid'
) t
WHERE rn IN (FLOOR((n + 1) / 2), CEIL((n + 1) / 2));

Sessionise events: a new session after 30 idle minutes

SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_no
FROM (
  SELECT user_id, ts,
         IF(TIMESTAMPDIFF(MINUTE, LAG(ts) OVER (PARTITION BY user_id ORDER BY ts), ts) <= 30, 0, 1) AS new_session
  FROM events
) e;

The ones you will use every day

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       LENGTH('Ana Popescuș') AS len, CHAR_LENGTH('Ana Popescuș') AS chars,
       SUBSTRING_INDEX(email, '@', -1) AS domain
FROM customers;
CONCAT_WS(', ', street, city, zip)          -- skips NULLs, CONCAT returns NULL if any part is NULL
LOWER(email)   UPPER(sku)   TRIM(name)   LTRIM(x)   RTRIM(x)
SUBSTRING(sku, 1, 3)   LEFT(sku, 3)   RIGHT(sku, 2)   LPAD(id, 6, '0')
REPLACE(phone, ' ', '')   LOCATE('@', email)   REVERSE(s)   REPEAT('-', 20)
REGEXP_REPLACE(phone, '[^0-9+]', '')   REGEXP_SUBSTR(note, '[A-Z]{3}-[0-9]+')   REGEXP_LIKE(sku, '^OAK')

Now, format, add, difference

SELECT NOW(), CURDATE(), DATE_FORMAT(NOW(), '%a %d %b %Y') AS fmt,
       DATEDIFF(NOW(), '2026-09-01') AS days,
       TIMESTAMPDIFF(HOUR, '2026-09-01', NOW()) AS hrs;
NOW() + INTERVAL 7 DAY   DATE_SUB(NOW(), INTERVAL 1 MONTH)   LAST_DAY(CURDATE())
DATE(created_at)   YEAR(d)   MONTH(d)   WEEK(d, 3)   DAYOFWEEK(d)   HOUR(t)
STR_TO_DATE('21.09.2026', '%d.%m.%Y')        -- text in any format to a DATE
UNIX_TIMESTAMP('2026-09-21 09:04:11')   FROM_UNIXTIME(1790067851)
DATE_FORMAT(d, '%Y-%m-01')                   -- first day of the month, for grouping

Filter by day without killing the index

WHERE DATE(created_at) = '2026-09-21'                             -- full scan
WHERE created_at >= '2026-09-21' AND created_at < '2026-09-22'    -- index range
WHERE created_at >= CURDATE() - INTERVAL 7 DAY                    -- last 7 days
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')            -- this month

Rounding and casting

SELECT ROUND(2.5), TRUNCATE(2.5678, 2), CEIL(2.1), FORMAT(12345.678, 2), CAST('42' AS SIGNED);
FLOOR(x)   ABS(x)   MOD(a, b)   POW(2, 10)   SQRT(x)   RAND()   GREATEST(a, b, c)   LEAST(a, b)
CAST(price AS CHAR)   CAST('2026-09-21' AS DATE)   CAST(x AS DECIMAL(10,2))   CONVERT(s USING utf8mb4)
'10' + 5                     -- 15: strings are converted to numbers silently
'10abc' + 5                  -- 15 with a warning; strict mode matters on INSERT, not in SELECT

Information and misc

LAST_INSERT_ID()   ROW_COUNT()   FOUND_ROWS() (deprecated)   DATABASE()   USER()   CONNECTION_ID()
UUID()   UUID_SHORT()   MD5(s)   SHA2(s, 256)   HEX(b)   UNHEX(h)   INET_ATON('10.0.0.1')   INET6_ATON(ip)
BENCHMARK(1000000, SHA2('x', 256))       -- time an expression
SLEEP(2)                                 -- testing only

Locale numbers, percentages, slugs

SELECT FORMAT(1234.5, 2, 'de_DE') AS price_de,
       CONCAT(ROUND(0.125 * 100, 1), '%') AS pct,
       LOWER(REGEXP_REPLACE(TRIM('Oak Desk 140'), '[^A-Za-z0-9]+', '-')) AS slug;
DATE_FORMAT(created_at, '%d.%m.%Y %H:%i')          -- 21.09.2026 09:04
SEC_TO_TIME(3725)                                   -- 01:02:05
LPAD(invoice_no, 8, '0')                            -- 00001042

Search and split strings

SUBSTRING_INDEX('a,b,c', ',', 2)                  -- a,b
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1)   -- b, the second item
FIND_IN_SET('b', 'a,b,c')                          -- 2
INSTR(email, '@')   POSITION('@' IN email)
SOUNDEX(name) = SOUNDEX('Popesku')                 -- rough phonetic match
JSON_UNQUOTE(JSON_EXTRACT(CONCAT('["', REPLACE('a,b,c', ',', '","'), '"]'), '$[2]'))   -- split via JSON: c

One row, many rows, from a query

INSERT INTO products (sku, name, price) VALUES ('OAK-1', 'Oak desk', 199.00);
INSERT INTO products (sku, name, price) VALUES
  ('LAMP-3', 'Lamp', 25.25),
  ('RUG-9', 'Rug', 89.00),
  ('SHELF-2', 'Oak shelf', 149.00);
SELECT LAST_INSERT_ID();                 -- the first id of the last multi row insert, per connection
INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2025-01-01';
INSERT INTO settings SET name = 'theme', value = 'dark';   -- MySQL only syntax

Upsert: insert or update on a duplicate key

INSERT INTO stock (sku, qty) VALUES ('OAK-1', 5) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;          -- 8.0.19+

INSERT INTO stock (sku, qty) VALUES ('OAK-1', 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);            -- older form, MariaDB

INSERT IGNORE INTO tags (name) VALUES ('oak');              -- skip duplicates (and turn other errors into warnings)
REPLACE INTO settings (name, value) VALUES ('theme', 'light');   -- delete then insert: new id, fires delete triggers

Update with a safety net

START TRANSACTION;
SELECT COUNT(*) FROM products WHERE category_id = 3 AND price < 100;   -- 16
UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category_id = 3 AND price < 100;
-- Rows matched: 16  Changed: 14
COMMIT;                                                                   -- or ROLLBACK

UPDATE orders SET status = 'shipped', shipped_at = NOW() WHERE id = 10042;
UPDATE products SET stock = stock - 1 WHERE id = 12 AND stock > 0;       -- atomic decrement

Update and delete across tables

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.vip = 1
WHERE c.lifetime_value > 5000;

DELETE o FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.deleted_at IS NOT NULL;                -- deletes from orders only

DELETE o, oi FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.id = 10042;   -- both tables

Delete millions of rows in batches

DELETE FROM logs WHERE created_at < '2026-01-01' ORDER BY id LIMIT 10000;
-- repeat until ROW_COUNT() = 0, with a short pause between runs
SELECT ROW_COUNT();

-- or keep only what you need and swap
CREATE TABLE logs_new LIKE logs;
INSERT INTO logs_new SELECT * FROM logs WHERE created_at >= '2026-01-01';
RENAME TABLE logs TO logs_old, logs_new TO logs;       -- atomic
DROP TABLE logs_old;

LOAD DATA for CSV imports

LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, name, @price, category_id)
SET price = NULLIF(@price, '');

mysql --local-infile=1 -u app -p shop          -- client side switch
SET GLOBAL local_infile = 1;                   -- server side switch
SHOW VARIABLES LIKE 'secure_file_priv';

Export to CSV

SELECT id, sku, name, price INTO OUTFILE '/var/lib/mysql-files/products.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM products;                                 -- written on the server, inside secure_file_priv

mysql -u app -p -B -e "SELECT id, sku, name FROM products" shop | sed 's/\t/,/g' > products.csv   -- from the client

TABLE and VALUES statements, 8.0.19+

TABLE products ORDER BY id LIMIT 5;                  -- SELECT * FROM products, shorter
VALUES ROW(1, 'a'), ROW(2, 'b');                     -- a literal row set
INSERT INTO t1 TABLE t2;                             -- copy every row

Soft deletes with a unique email that still works

ALTER TABLE users
  ADD COLUMN deleted_at DATETIME NULL,
  ADD COLUMN email_active VARCHAR(254) AS (IF(deleted_at IS NULL, email, NULL)) STORED,
  ADD UNIQUE KEY uq_users_email_active (email_active);        -- NULLs do not collide
UPDATE users SET deleted_at = NOW() WHERE id = 42;            -- delete
UPDATE users SET deleted_at = NULL WHERE id = 42;             -- restore
SELECT * FROM users WHERE deleted_at IS NULL;

Counters and idempotent writes

INSERT INTO page_views (page_id, day, views) VALUES (7, CURDATE(), 1) AS n
ON DUPLICATE KEY UPDATE views = page_views.views + 1;              -- needs UNIQUE (page_id, day)
INSERT INTO payments (idempotency_key, order_id, amount) VALUES ('7d3f...', 10042, 249.50)
ON DUPLICATE KEY UPDATE id = id;                                   -- retry safe, the second call is a no op
UPDATE inventory SET qty = qty - 2 WHERE sku = 'OAK-1' AND qty >= 2;   -- check ROW_COUNT() = 1

Paths with -> and ->>

CREATE TABLE products (id BIGINT PRIMARY KEY, meta JSON);
INSERT INTO products VALUES (12, '{"color": "oak", "tags": ["desk", "office"], "size": {"w": 140, "d": 70}}');

SELECT meta->'$.color', meta->>'$.color' AS color, meta->>'$.tags[0]' AS first FROM products;
SELECT JSON_EXTRACT(meta, '$.size.w'), JSON_UNQUOTE(JSON_EXTRACT(meta, '$.color')) FROM products;
SELECT * FROM products WHERE meta->>'$.color' = 'oak';
SELECT JSON_KEYS(meta), JSON_LENGTH(meta, '$.tags'), JSON_TYPE(meta->'$.size'), JSON_VALID('{}') FROM products;

Change part of a document

UPDATE products SET meta = JSON_SET(meta, '$.color', 'walnut', '$.size.h', 75) WHERE id = 12;   -- set or add
UPDATE products SET meta = JSON_INSERT(meta, '$.color', 'x') WHERE id = 12;    -- add only if missing
UPDATE products SET meta = JSON_REPLACE(meta, '$.color', 'ash') WHERE id = 12; -- change only if present
UPDATE products SET meta = JSON_REMOVE(meta, '$.size.h') WHERE id = 12;
UPDATE products SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'sale') WHERE id = 12;
UPDATE products SET meta = JSON_MERGE_PATCH(meta, '{"color": null, "stock": 4}') WHERE id = 12;   -- null deletes

Search, and JSON_TABLE to turn arrays into rows

SELECT * FROM products WHERE JSON_CONTAINS(meta->'$.tags', '"desk"');
SELECT * FROM products WHERE 'desk' MEMBER OF (meta->'$.tags');               -- 8.0.17+
SELECT * FROM products WHERE JSON_OVERLAPS(meta->'$.tags', '["desk", "sale"]');

SELECT p.id, jt.tag
FROM products p,
JSON_TABLE(p.meta, '$.tags[*]' COLUMNS (tag VARCHAR(40) PATH '$')) AS jt;

Generated column, functional and multi valued indexes

ALTER TABLE products ADD COLUMN color VARCHAR(20) AS (meta->>'$.color') STORED, ADD INDEX ix_color (color);
ALTER TABLE products ADD INDEX ix_width ((CAST(meta->>'$.size.w' AS UNSIGNED)));              -- functional, 8.0.13+
ALTER TABLE products ADD INDEX ix_tags ((CAST(meta->'$.tags' AS CHAR(40) ARRAY)));             -- multi valued, 8.0.17+
SELECT * FROM products WHERE 'desk' MEMBER OF (meta->'$.tags');                                -- uses ix_tags

Enforce a shape with JSON Schema

ALTER TABLE products ADD CONSTRAINT ck_meta_shape CHECK (JSON_SCHEMA_VALID('{
  "type": "object",
  "required": ["color"],
  "properties": {"color": {"type": "string"}, "tags": {"type": "array", "items": {"type": "string"}}}
}', meta));
SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @doc);         -- why a document fails

JSON duality views, 9.7 LTS

CREATE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT, UPDATE, DELETE)
  '_id' : id,
  'status' : status,
  'total' : total,
  'customer' : (
    SELECT JSON_DUALITY_OBJECT( WITH(UPDATE)
      'id' : c.id,
      'name' : c.name
    )
    FROM customers c WHERE c.id = orders.customer_id
  )
)
FROM orders;

SELECT JSON_PRETTY(data) FROM order_dv WHERE data->'$._id' = 10042\G

An order with its lines, as one document

SELECT JSON_OBJECT(
  'id', o.id, 'status', o.status, 'total', o.total,
  'items', (SELECT JSON_ARRAYAGG(JSON_OBJECT('sku', p.sku, 'qty', oi.qty))
            FROM order_items oi JOIN products p ON p.id = oi.product_id
            WHERE oi.order_id = o.id)
) AS doc
FROM orders o WHERE o.id = 10042;

Pretty print, size, and comparing documents

SELECT JSON_PRETTY(meta), JSON_STORAGE_SIZE(meta) FROM products WHERE id = 12;
SELECT JSON_CONTAINS_PATH(meta, 'one', '$.color', '$.finish') FROM products;
SELECT * FROM products WHERE meta = CAST('{"color": "oak"}' AS JSON);   -- JSON equality ignores key order
SELECT CAST(meta->>'$.size.w' AS UNSIGNED) + 10 FROM products;        -- cast before arithmetic

All or nothing

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                          -- or ROLLBACK to undo both

SAVEPOINT before_items;
ROLLBACK TO SAVEPOINT before_items;
SELECT @@autocommit;             SET autocommit = 0;   -- rarely a good idea in apps

Isolation levels

SELECT @@transaction_isolation;                                -- REPEATABLE-READ by default
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;                  -- next transaction only
START TRANSACTION READ ONLY;                                   -- tells InnoDB no writes are coming
START TRANSACTION WITH CONSISTENT SNAPSHOT;

Read, lock, decide, write

START TRANSACTION;
SELECT stock FROM products WHERE id = 12 FOR UPDATE;       -- others wait here
UPDATE products SET stock = stock - 1 WHERE id = 12;
INSERT INTO order_items (order_id, product_id, qty) VALUES (10042, 12, 1);
COMMIT;

SELECT * FROM accounts WHERE id = 1 FOR SHARE;              -- block writers, allow other readers

A job queue with SKIP LOCKED, 8.0+

START TRANSACTION;
SELECT id, payload FROM jobs
WHERE status = 'queued' ORDER BY id
LIMIT 10 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'running', worker = 'w3' WHERE id IN (...);
COMMIT;

SELECT * FROM seats WHERE id = 42 FOR UPDATE NOWAIT;   -- ERROR 3572 if someone holds it

Optimistic locking with a version column

UPDATE documents SET body = ?, version = version + 1 WHERE id = ? AND version = ?;
-- 0 rows affected: someone saved first; reload and ask the user to merge

Find who blocks whom

SHOW ENGINE INNODB STATUS\G                         -- LATEST DETECTED DEADLOCK section
SELECT * FROM sys.innodb_lock_waits\G               -- waiting and blocking queries, with a kill hint
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;   -- long open transactions
SET GLOBAL innodb_print_all_deadlocks = ON;         -- log every deadlock to the error log
SET SESSION innodb_lock_wait_timeout = 5;

Fewer deadlocks, by design

-- touch rows in the same order everywhere (for example ORDER BY id in locking reads)
-- keep transactions short: no HTTP calls or user input between START and COMMIT
-- lock through a selective index so fewer rows and gaps are locked
-- READ COMMITTED removes most gap locks
-- retry 1213 in the application, two or three times with a small random delay

The lost update, and two ways to stop it

-- A: START TRANSACTION; SELECT stock FROM products WHERE id = 12;          -- 1
-- B: START TRANSACTION; SELECT stock FROM products WHERE id = 12;          -- 1
-- A: UPDATE products SET stock = stock - 1 WHERE id = 12; COMMIT;          -- 0
-- B: SELECT stock ... ;  still 1 in B's snapshot
-- B: UPDATE products SET stock = stock - 1 WHERE id = 12; COMMIT;          -- -1

-- fix 1: SELECT stock FROM products WHERE id = 12 FOR UPDATE;
-- fix 2: UPDATE products SET stock = stock - 1 WHERE id = 12 AND stock > 0;   then check rows affected

Named locks for application level mutual exclusion

SELECT GET_LOCK('nightly-report', 0);      -- 1 = acquired, 0 = someone else runs it
-- ... do the work ...
SELECT RELEASE_LOCK('nightly-report');
SELECT IS_USED_LOCK('nightly-report');     -- connection id of the holder, or NULL
-- the lock is released automatically if the connection drops

LOCK TABLES and a global read lock

LOCK TABLES orders WRITE, customers READ;     -- this session only; others wait
-- ... maintenance ...
UNLOCK TABLES;

FLUSH TABLES WITH READ LOCK;                  -- whole server read only, for a snapshot
-- take the LVM or cloud disk snapshot here
UNLOCK TABLES;
FLUSH TABLES orders WITH READ LOCK;           -- one table, for exporting its tablespace

XA: two phase commit

XA START 'order-812';
UPDATE stock SET qty = qty - 1 WHERE sku = 'OAK-1';
XA END 'order-812';
XA PREPARE 'order-812';
XA COMMIT 'order-812';          -- or XA ROLLBACK 'order-812'
XA RECOVER;                     -- prepared transactions waiting for a decision

The statements

CREATE INDEX ix_orders_customer_created ON orders (customer_id, created_at);
CREATE UNIQUE INDEX uq_users_email ON users (email);
ALTER TABLE orders ADD INDEX ix_status (status), ADD INDEX ix_created (created_at);
DROP INDEX ix_status ON orders;
SHOW INDEX FROM orders;
ANALYZE TABLE orders;                          -- refresh the statistics the optimizer uses

Composite indexes and the leftmost prefix

INDEX (customer_id, status, created_at)

WHERE customer_id = 12                                       -- uses it
WHERE customer_id = 12 AND status = 'paid'                   -- uses it
WHERE customer_id = 12 AND status = 'paid' ORDER BY created_at DESC   -- uses it, no sort
WHERE customer_id = 12 AND created_at > '2026-09-01'         -- uses customer_id only, then filters
WHERE status = 'paid'                                        -- cannot use it: no customer_id
WHERE customer_id = 12 OR status = 'paid'                    -- usually cannot: OR across columns

Covering, prefix, descending, functional

CREATE INDEX ix_cover ON orders (status, created_at, total);   -- SELECT created_at, total WHERE status = ...
CREATE INDEX ix_url ON pages (url(191));                         -- prefix: first 191 characters only
CREATE INDEX ix_recent ON orders (created_at DESC);              -- real descending indexes, 8.0+
CREATE INDEX ix_email_lower ON users ((LOWER(email)));           -- functional, 8.0.13+; query must use LOWER(email)

FULLTEXT search

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
SELECT id, title, MATCH(title, body) AGAINST ('oak desk') AS score
FROM articles WHERE MATCH(title, body) AGAINST ('oak desk')
ORDER BY score DESC;
SELECT id FROM articles WHERE MATCH(title, body) AGAINST ('+oak -pine desk*' IN BOOLEAN MODE);
-- ngram parser for Chinese, Japanese, Korean: FULLTEXT ... WITH PARSER ngram

Invisible indexes: test a drop safely, 8.0+

ALTER TABLE orders ALTER INDEX ix_status INVISIBLE;
-- watch the slow log for a day
ALTER TABLE orders ALTER INDEX ix_status VISIBLE;     -- instant undo
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';

From query to index

-- query
SELECT id, total FROM orders WHERE customer_id = ? AND status = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 20;
-- 1. equality columns first:   customer_id, status
-- 2. then the range or sort column:   created_at
-- 3. optionally the selected columns to cover it:   total
CREATE INDEX ix_orders_cust_status_created ON orders (customer_id, status, created_at, total);
-- an index on (customer_id) alone is now redundant: the new one starts with it

When an index will not help

WHERE status = 'paid'            -- 90% of rows are paid: a scan is cheaper, the optimizer ignores the index
WHERE LOWER(email) = ?           -- function on the column: needs a functional index
WHERE name LIKE '%oak%'          -- leading wildcard: FULLTEXT or an external search engine
WHERE phone = 407123456          -- phone is VARCHAR: implicit conversion, index skipped; quote the value
WHERE a = 1 OR b = 2             -- may use index_merge; UNION of two indexed queries is often faster

Which indexes earn their keep

SELECT object_name AS table_name, index_name, count_read AS rows_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'shop' AND index_name IS NOT NULL
ORDER BY count_read;                          -- counts since the last restart
SELECT table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1048576, 2) AS size_mb
FROM mysql.innodb_index_stats WHERE database_name = 'shop' AND stat_name = 'size';

What actually happened

EXPLAIN ANALYZE
SELECT status, SUM(total) AS revenue FROM orders
WHERE created_at >= '2026-09-01' GROUP BY status ORDER BY revenue DESC;

EXPLAIN FORMAT=TREE SELECT ...;           -- the plan as a tree, no execution
EXPLAIN FORMAT=JSON SELECT ...\G          -- costs, used columns, attached conditions

Plans as JSON you can store: 8.4, and ANALYZE in 9.0+

EXPLAIN FORMAT=JSON INTO @plan SELECT * FROM orders WHERE customer_id = 12;   -- 8.4
SELECT JSON_PRETTY(@plan);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12;                   -- 8.4: TREE only
EXPLAIN ANALYZE FORMAT=JSON INTO @run SELECT * FROM orders WHERE customer_id = 12;   -- 9.0+

The slow query log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;                       -- seconds, fractions allowed
SET GLOBAL log_queries_not_using_indexes = ON;          -- noisy, but finds full scans
SHOW VARIABLES LIKE 'slow_query_log_file';
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log         -- top 10 by total time
pt-query-digest /var/log/mysql/slow.log                  -- Percona Toolkit, the better report

The heaviest statements, from performance_schema

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

Hints, histograms, and the hypergraph optimizer

SELECT * FROM orders FORCE INDEX (ix_created) WHERE created_at > '2026-09-01';
SELECT /*+ INDEX(o ix_created) NO_BNL() */ * FROM orders o WHERE created_at > '2026-09-01';
SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM big_table;      -- milliseconds
SELECT /*+ SET_VAR(sort_buffer_size = 16777216) */ * FROM orders ORDER BY note;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;
SET optimizer_switch = 'hypergraph_optimizer=on';                      -- 9.7 Community, still marked experimental

OR across columns, large OFFSETs, COUNT(*)

-- OR on two indexed columns: often a full scan
SELECT id FROM users WHERE email = ? OR phone = ?;
-- two index lookups instead
SELECT id FROM users WHERE email = ? UNION SELECT id FROM users WHERE phone = ?;

-- deep OFFSET: read only ids, then fetch the page (deferred join)
SELECT p.* FROM products p JOIN (SELECT id FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 50000) x USING (id);

-- exact COUNT(*) on InnoDB reads an index; for a dashboard an estimate is fine
SELECT table_rows FROM information_schema.tables WHERE table_schema = 'shop' AND table_name = 'orders';

When the plan changed overnight

ANALYZE TABLE orders;                                             -- resample statistics
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country WITH 32 BUCKETS;
SELECT * FROM information_schema.column_statistics WHERE table_name = 'orders'\G
ALTER TABLE orders STATS_SAMPLE_PAGES = 64;                       -- more accurate statistics for a skewed table

Histograms that keep themselves fresh, 8.4+

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS AUTO UPDATE;   -- refreshed with the table statistics
ANALYZE TABLE orders UPDATE HISTOGRAM ON status MANUAL UPDATE;                 -- back to manual

SHOW, SET GLOBAL, SET PERSIST

SHOW VARIABLES LIKE 'innodb_buffer%';
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gb;
SET GLOBAL max_connections = 500;                   -- until restart
SET PERSIST max_connections = 500;                  -- now and after restart, 8.0+
SET PERSIST_ONLY innodb_log_file_size = 1073741824; -- next restart only
RESET PERSIST max_connections;
SELECT * FROM performance_schema.persisted_variables;
SELECT variable_name, variable_source, variable_path FROM performance_schema.variables_info
WHERE variable_source <> 'COMPILED';               -- where each non default value came from

sql_mode: keep strict mode on

SELECT @@sql_mode;
SET SESSION sql_mode = CONCAT(@@sql_mode, ',ANSI_QUOTES'); -- "x" becomes an identifier
SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');   -- legacy code only

The settings that matter on a dedicated server

# /etc/mysql/mysql.conf.d/mysqld.cnf  or  /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 12G          # 60 to 75% of RAM on a dedicated box
# innodb_dedicated_server = ON         # or let MySQL size buffer pool and redo log itself
innodb_redo_log_capacity = 4G          # 8.0.30+
innodb_flush_log_at_trx_commit = 1     # 1 = durable; 2 = faster, may lose ~1s on OS crash
max_connections = 300
table_open_cache = 4000
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = ON
long_query_time = 0.5
log_bin = mysql-bin
binlog_expire_logs_seconds = 604800    # 7 days
server_id = 1
character_set_server = utf8mb4
bind-address = 127.0.0.1               # or the private interface; never 0.0.0.0 on a public host

Is the buffer pool big enough

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SELECT * FROM sys.memory_global_total;
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';     -- grows fast: raise tmp_table_size or fix the query
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
mysqld --verbose --help | grep -A1 'Default options'   -- which option files are read, in order

Where the memory goes

SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10;
SELECT user, current_allocated FROM sys.memory_by_user_by_current_bytes;
SHOW VARIABLES WHERE Variable_name IN ('sort_buffer_size','join_buffer_size','read_rnd_buffer_size','tmp_table_size');
-- leave per session buffers near their defaults; raise per query with /*+ SET_VAR(...) */

Timeouts that end connections

wait_timeout = 28800                 # idle client connections, seconds
interactive_timeout = 28800          # idle mysql client sessions
max_allowed_packet = 64M             # the largest single statement or row; big BLOBs and dumps need more
net_read_timeout = 30   net_write_timeout = 60
max_execution_time = 0               # milliseconds, for SELECT only; set per session for reporting users

Load the zone tables, then use named zones

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql      # Linux, once, and after tzdata updates
SELECT CONVERT_TZ('2026-09-29 07:00:00', 'UTC', 'Europe/Bucharest') AS shown_bucharest;
SELECT COUNT(*) FROM mysql.time_zone_name;                            -- 0 means the tables are empty
SET time_zone = 'Europe/Bucharest';                                    -- per session
SET PERSIST time_zone = '+00:00';                                      -- server default; keep the server on UTC

Why a TIMESTAMP moves and a DATETIME does not

SET time_zone = '+00:00';
INSERT INTO t (created_ts, created_dt) VALUES ('2026-09-29 07:00:00', '2026-09-29 07:00:00');
SET time_zone = 'Europe/Bucharest';
SELECT created_ts, created_dt FROM t;
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;

file_per_table, ibdata1, general tablespaces

SHOW VARIABLES LIKE 'innodb_file_per_table';        -- ON: one .ibd file per table
SELECT file_name, tablespace_name, total_extents * extent_size / 1048576 AS mb
FROM information_schema.files WHERE file_type = 'TABLESPACE' ORDER BY mb DESC LIMIT 10;
CREATE TABLESPACE ts_archive ADD DATAFILE 'ts_archive.ibd' ENGINE = InnoDB;   -- a general tablespace
ALTER TABLE logs_2025 TABLESPACE ts_archive;
ALTER TABLE orders ENGINE = InnoDB;                   -- rebuild and shrink one .ibd after big deletes

Undo and redo: what can grow, and how to reclaim it

SELECT NAME, STATE FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE = 'Undo';
SET GLOBAL innodb_undo_log_truncate = ON;              -- default ON: large undo tablespaces are truncated
SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';  -- keeps growing: a long open transaction blocks purge
SET PERSIST innodb_redo_log_capacity = 4294967296;     -- 8.0.30+, resizes the redo log online

InnoDB defaults in 8.4 LTS

innodb_adaptive_hash_index = OFF        # was ON
innodb_change_buffering = none          # was all
innodb_io_capacity = 10000              # was 200
innodb_log_buffer_size = 64M            # was 16M
SELECT variable_name, variable_value FROM performance_schema.global_variables
WHERE variable_name IN ('innodb_adaptive_hash_index','innodb_change_buffering','innodb_io_capacity','innodb_log_buffer_size');

Replication and logging defaults in 9.7 LTS

binlog_transaction_dependency_history_size = 1000000    # was 25000
innodb_log_writer_threads                              # new default, check it after upgrading
-- removed in 9.7: replica_parallel_type (parallel appliers always use LOGICAL_CLOCK)
SELECT @@binlog_transaction_dependency_history_size, @@innodb_log_writer_threads;

Create, replace, inspect

CREATE OR REPLACE VIEW v_customer_revenue AS
SELECT c.id, c.name, COALESCE(SUM(o.total), 0) AS revenue
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status IN ('paid', 'shipped')
GROUP BY c.id, c.name;

SELECT * FROM v_customer_revenue ORDER BY revenue DESC LIMIT 10;
SHOW CREATE VIEW v_customer_revenue\G
DROP VIEW IF EXISTS v_customer_revenue;
CREATE SQL SECURITY INVOKER VIEW v_mine AS SELECT * FROM orders WHERE owner = CURRENT_USER();

A procedure with parameters and a handler

DELIMITER //
CREATE PROCEDURE customer_summary(IN p_customer BIGINT, OUT p_orders INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  SELECT COUNT(*), SUM(total) FROM orders WHERE customer_id = p_customer;
  SELECT COUNT(*) INTO p_orders FROM orders WHERE customer_id = p_customer;
END //
DELIMITER ;

CALL customer_summary(12, @n);
SELECT @n;
SHOW PROCEDURE STATUS WHERE Db = 'shop';
DROP PROCEDURE IF EXISTS customer_summary;

A function, and control flow

DELIMITER //
CREATE FUNCTION price_with_vat(p DECIMAL(10,2), country CHAR(2))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
  DECLARE rate DECIMAL(4,2) DEFAULT 0.19;
  IF country = 'RO' THEN SET rate = 0.21;
  ELSEIF country = 'DE' THEN SET rate = 0.19;
  END IF;
  RETURN ROUND(p * (1 + rate), 2);
END //
DELIMITER ;
SELECT name, price_with_vat(price, 'RO') FROM products;
-- with binary logging on, functions must be DETERMINISTIC, NO SQL or READS SQL DATA
-- (or log_bin_trust_function_creators = 1)

Errors, cursors, dynamic SQL

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock cannot go negative';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;     -- for cursors
DECLARE cur CURSOR FOR SELECT id FROM products WHERE stock = 0;

SET @sql = CONCAT('SELECT COUNT(*) FROM ', 'orders', ' WHERE status = ?');
PREPARE stmt FROM @sql;
SET @s = 'paid';
EXECUTE stmt USING @s;
DEALLOCATE PREPARE stmt;
-- never CONCAT user input into @sql; bind it with ?

JavaScript stored programs, 9.0+ with the MLE component

INSTALL COMPONENT 'file://component_mle';
SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'mle%';
CREATE FUNCTION slugify(s VARCHAR(200)) RETURNS VARCHAR(200) LANGUAGE JAVASCRIPT AS $$
  return s.toLowerCase().normalize('NFD').replace(/[̀-ͯ]/g, '').replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
$$;
SELECT slugify('Birou din stejar, 140 cm');

An audit trigger

CREATE TRIGGER trg_orders_status
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
SELECT NEW.id, OLD.status, NEW.status, NOW()
FROM DUAL WHERE NOT (OLD.status <=> NEW.status);

SHOW TRIGGERS LIKE 'orders';
DROP TRIGGER IF EXISTS trg_orders_status;

Scheduled events

SET GLOBAL event_scheduler = ON;       -- ON by default since 8.0
CREATE EVENT ev_purge_sessions
ON SCHEDULE EVERY 1 HOUR
DO DELETE FROM sessions WHERE expires_at < NOW() LIMIT 10000;

CREATE EVENT ev_nightly_rollup ON SCHEDULE EVERY 1 DAY STARTS '2026-09-30 02:00:00'
DO REPLACE INTO daily_revenue SELECT DATE(created_at), SUM(total) FROM orders
   WHERE created_at >= CURDATE() - INTERVAL 1 DAY AND created_at < CURDATE() GROUP BY 1;
SHOW EVENTS;   ALTER EVENT ev_purge_sessions DISABLE;

Definers that no longer exist

SELECT routine_schema, routine_name, definer FROM information_schema.routines WHERE routine_schema = 'shop';
SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'shop';
SELECT trigger_name, definer FROM information_schema.triggers WHERE trigger_schema = 'shop';
-- ERROR 1449 (HY000): The user specified as a definer ('old'@'%') does not exist
ALTER DEFINER = 'shop_owner'@'localhost' VIEW v_customer_revenue AS SELECT ...;

When logic belongs in the application instead

-- hard to test, version and debug; tied to one database; scale only as far as the primary does
-- good fits: a small set of integrity rules, audit triggers, batch jobs close to the data
-- keep them in migrations like any schema change, never edited by hand on production

Create an application user with least privilege

CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.%';
SHOW GRANTS FOR 'app'@'10.0.%';

CREATE USER 'report'@'%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT ON shop.orders TO 'report'@'%';
GRANT SELECT (id, name, email) ON shop.customers TO 'report'@'%';   -- column level
REVOKE DELETE ON shop.* FROM 'app'@'10.0.%';
DROP USER 'report'@'%';
SELECT user, host, plugin, account_locked FROM mysql.user;

Privilege levels at a glance

GRANT ALL PRIVILEGES ON shop.* TO 'owner'@'localhost';          -- everything on one database
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON shop.* TO 'backup'@'localhost';   -- for mysqldump
GRANT PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';                  -- global only
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';               -- the privilege keeps its old name
GRANT EXECUTE ON PROCEDURE shop.customer_summary TO 'app'@'10.0.%';
GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;       -- a real admin, local only

Roles

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';
GRANT 'app_read', 'app_write' TO 'app'@'10.0.%';
SET DEFAULT ROLE ALL TO 'app'@'10.0.%';                 -- or activate per session: SET ROLE 'app_read';
SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'app'@'10.0.%' USING 'app_read';

Passwords, locking, expiry

ALTER USER 'app'@'10.0.%' IDENTIFIED BY 'new-long-random-password';
ALTER USER 'app'@'10.0.%' IDENTIFIED BY 'new' RETAIN CURRENT PASSWORD;   -- dual passwords during rotation
ALTER USER 'app'@'10.0.%' DISCARD OLD PASSWORD;
ALTER USER 'intern'@'%' ACCOUNT LOCK;
ALTER USER 'ana'@'%' PASSWORD EXPIRE INTERVAL 90 DAY FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
ALTER USER 'tmp'@'%' IDENTIFIED BY RANDOM PASSWORD;                       -- prints the generated one

Move accounts off mysql_native_password before upgrading

SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password';
ALTER USER 'legacy'@'%' IDENTIFIED WITH caching_sha2_password BY 'new-password';
-- 8.4: mysql_native_password is off unless mysql_native_password=ON is in my.cnf
-- 9.0+: the plugin no longer exists

Reset a forgotten root password

sudo systemctl stop mysql
sudo mysqld --skip-grant-tables --skip-networking --user=mysql &
mysql -u root
FLUSH PRIVILEGES;                                        -- loads the grant tables so ALTER USER works
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';
-- then stop that mysqld and start the service normally
-- Ubuntu packages: root@localhost uses auth_socket, so sudo mysql logs in without a password

Accounts with global power

SELECT user, host FROM mysql.user WHERE Super_priv = 'Y' OR Grant_priv = 'Y' OR File_priv = 'Y';
SELECT grantee, privilege_type FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','FILE','PROCESS','SHUTDOWN','SYSTEM_VARIABLES_ADMIN') ORDER BY grantee;
SELECT * FROM information_schema.schema_privileges WHERE table_schema = 'shop';

Who is connected, and from where

SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host;
SELECT * FROM sys.user_summary;
SELECT user, total_connections, current_connections FROM performance_schema.users;
SELECT user, host, password_last_changed, account_locked FROM mysql.user ORDER BY password_last_changed;

A consistent dump of one database

mysqldump --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF -u backup -p shop | gzip > shop-$(date +%F).sql.gz

mysqldump -u backup -p --single-transaction shop orders order_items > orders.sql   -- some tables
mysqldump -u backup -p --no-data shop > schema.sql                                 -- structure only
mysqldump -u backup -p --no-create-info --where="created_at >= '2026-09-01'" shop orders > sept.sql
mysqldump -u root -p --all-databases --single-transaction --source-data=2 > all.sql

Restore, and prove the backup works

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS shop"
gunzip < shop-2026-09-29.sql.gz | mysql -u root -p shop
mysql -u root -p shop < schema.sql
pv shop.sql | mysql -u root -p shop                      -- with a progress bar
-- a backup you have never restored is a hope, not a backup: restore to a scratch server on a schedule

MySQL Shell dump and load, in parallel

mysqlsh root@localhost -- util dump-instance /backups/full --threads=8
mysqlsh root@localhost -- util dump-schemas shop --outputUrl=/backups/shop --threads=8
mysqlsh root@target -- util load-dump /backups/shop --threads=8 --deferTableIndexes=all
-- JS mode: util.dumpInstance("/backups/full", {threads: 8, compression: "zstd"})

Physical backups: XtraBackup and the clone plugin

xtrabackup --backup --target-dir=/backups/base --user=backup --password=...
xtrabackup --prepare --target-dir=/backups/base
# restore: stop mysqld, empty the datadir, then
xtrabackup --copy-back --target-dir=/backups/base && chown -R mysql:mysql /var/lib/mysql

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/backups/clone-2026-09-29';

Point in time recovery with the binary log

SHOW BINARY LOGS;
mysqlbinlog --start-datetime="2026-09-29 00:00:00" --stop-datetime="2026-09-29 09:41:06" \
  /var/lib/mysql/mysql-bin.000412 /var/lib/mysql/mysql-bin.000413 | mysql -u root -p
mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000413 | less   -- find the bad statement
mysqlbinlog --stop-position=4821773 mysql-bin.000413 | mysql -u root -p

A nightly backup script with retention

#!/usr/bin/env bash
set -euo pipefail
DIR=/backups/mysql; STAMP=$(date +%F-%H%M)
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --triggers --events \
  --source-data=2 shop | zstd -q > "$DIR/shop-$STAMP.sql.zst"
find "$DIR" -name 'shop-*.sql.zst' -mtime +14 -delete
rclone copy "$DIR" remote:db-backups/shop --max-age 24h

# crontab -e
15 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Backups from Docker and managed clouds

docker exec mysql sh -c 'exec mysqldump --single-transaction -uroot -p"$MYSQL_ROOT_PASSWORD" shop' > shop.sql
docker exec -i mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" shop' < shop.sql
aws rds create-db-snapshot --db-instance-identifier shop-prod --db-snapshot-identifier shop-2026-09-29
gcloud sql export sql shop-prod gs://db-backups/shop-2026-09-29.sql.gz --database=shop

Source and replica settings, then connect

# my.cnf on both (unique server_id each)
server_id = 1                 # 2 on the replica
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
read_only = ON                # replica only
super_read_only = ON          # replica only

-- on the source
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY '...';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';

-- on the replica, after loading a dump or a clone of the source
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.10', SOURCE_USER = 'repl', SOURCE_PASSWORD = '...',
  SOURCE_AUTO_POSITION = 1, SOURCE_SSL = 1;
START REPLICA;

Is it healthy

SHOW REPLICA STATUS\G                       -- Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
STOP REPLICA;   START REPLICA;
SHOW REPLICAS;                              -- on the source

Binary log position, 8.4 spelling

SHOW BINARY LOG STATUS;                     -- 8.4+, replaces SHOW MASTER STATUS
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
-- removed in 8.4: SHOW MASTER STATUS, CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS

Lag and read your own writes

SELECT @@global.gtid_executed;                              -- on the source, after the write
SELECT WAIT_FOR_EXECUTED_GTID_SET('3e11fa47-...:915522', 2); -- on the replica: 0 = caught up, 1 = timeout
SET GLOBAL replica_parallel_workers = 8;                     -- parallel apply
-- every replicated table needs a primary key; sql_require_primary_key = ON enforces it

Skip one broken transaction on a GTID replica

STOP REPLICA;
SET GTID_NEXT = '3e11fa47-...:915523';      -- the failing GTID from Last_SQL_Error
BEGIN; COMMIT;                              -- an empty transaction takes its place
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;
-- then find out why it failed: skipped transactions mean the replica no longer matches

InnoDB Cluster in MySQL Shell

mysqlsh root@db1
dba.configureInstance('root@db1'); dba.configureInstance('root@db2'); dba.configureInstance('root@db3');
var cluster = dba.createCluster('shop');
cluster.addInstance('root@db2', {recoveryMethod: 'clone'});
cluster.addInstance('root@db3', {recoveryMethod: 'clone'});
cluster.status();
mysqlrouter --bootstrap root@db1 --user=mysqlrouter    # apps connect to the router: 6446 read write, 6447 read only
SELECT * FROM performance_schema.replication_group_members;

Send reads to replicas

// Laravel config/database.php
'mysql' => ['read' => ['host' => ['10.0.0.11', '10.0.0.12']], 'write' => ['host' => ['10.0.0.10']], 'sticky' => true, ...]
# Django: DATABASE_ROUTERS with db_for_read / db_for_write
# MySQL Router: port 6446 read write (primary), 6447 read only (replicas)
# ProxySQL: query rules route ^SELECT to the reader hostgroup, everything else to the writer

Delayed replicas, and filters

CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;          -- a replica one hour behind: an undo button for DROP TABLE
CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (shop.sessions);
SHOW VARIABLES LIKE 'binlog_format';                       -- ROW, the default and the one to keep

Process list and KILL

SHOW FULL PROCESSLIST;
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep' AND time > 10 ORDER BY time DESC;
KILL QUERY 812;          -- stop the statement, keep the connection
KILL 812;                -- close the connection, rolls back its open transaction
SELECT * FROM sys.session WHERE conn_id <> CONNECTION_ID() ORDER BY time DESC LIMIT 10;

Health numbers worth graphing

SHOW GLOBAL STATUS WHERE Variable_name IN
 ('Uptime','Threads_connected','Threads_running','Max_used_connections','Questions',
  'Slow_queries','Aborted_connects','Innodb_row_lock_waits','Innodb_buffer_pool_reads');
SELECT * FROM sys.host_summary;
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;
mysqladmin -u root -p extended-status -r -i 10 | grep -E 'Questions|Threads_running'   # per 10 s deltas

Size per database

SELECT table_schema AS `schema`,
       ROUND(SUM(data_length) / 1048576, 2)  AS data_mb,
       ROUND(SUM(index_length) / 1048576, 2) AS idx_mb,
       ROUND(SUM(data_free) / 1048576, 2)    AS free_mb
FROM information_schema.tables
GROUP BY table_schema ORDER BY data_mb DESC;

ANALYZE, OPTIMIZE, CHECK

ANALYZE TABLE orders;                -- statistics, fast, safe any time
OPTIMIZE TABLE logs;                 -- rebuild, reclaims space after big deletes
CHECK TABLE orders;                  -- corruption check
mysqlcheck -u root -p --analyze --all-databases
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;   -- binlogs are often the real disk hog

Host cache and connection errors, 8.4 spelling

SELECT * FROM performance_schema.host_cache WHERE SUM_CONNECT_ERRORS > 0;
TRUNCATE TABLE performance_schema.host_cache;     -- replaces FLUSH HOSTS, removed in 8.4
SET GLOBAL max_connect_errors = 1000;

Hottest tables and files

SELECT table_name, total_latency, rows_fetched, rows_updated
FROM sys.schema_table_statistics WHERE table_schema = 'shop' ORDER BY total_latency DESC LIMIT 10;
SELECT file, total_read, total_written FROM sys.io_global_by_file_by_bytes LIMIT 10;
SELECT * FROM sys.wait_classes_global_by_latency;

Reset counters and trace one session

CALL sys.ps_truncate_all_tables(FALSE);                    -- start a clean measurement window
CALL sys.ps_trace_thread(812, '/tmp/thread812.dot', 60, 0.1, TRUE, FALSE, FALSE);
SELECT * FROM performance_schema.events_statements_history WHERE thread_id = 812 ORDER BY event_id DESC LIMIT 5\G

External monitoring

mysqld_exporter --config.my-cnf=/etc/mysql/exporter.cnf        # Prometheus metrics, Grafana dashboards
pt-mysql-summary   pt-stalk   pt-kill                            # Percona Toolkit
innotop                                                          # top for InnoDB

Export traces and metrics over OTLP

INSTALL COMPONENT 'file://component_telemetry';
SET PERSIST telemetry.otel_exporter_otlp_traces_endpoint = 'http://otel-collector:4318/v1/traces';
SET PERSIST telemetry.otel_exporter_otlp_metrics_endpoint = 'http://otel-collector:4318/v1/metrics';
SHOW VARIABLES LIKE 'telemetry%';

Group Replication health in 9.7

SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats\G   -- queue sizes, conflicts
-- 9.7 Community gains the Group Replication resource and flow control metrics
-- that were Enterprise only before; they appear in performance_schema status variables
SHOW GLOBAL STATUS LIKE 'Gr\_%';

Connection URLs you will paste

mysql://app:secret@db.example.com:3306/shop                     # most libraries, Prisma, SQLAlchemy (mysql+pymysql://)
DATABASE_URL=mysql://app:secret@127.0.0.1:3306/shop?ssl-mode=REQUIRED
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=shop DB_USERNAME=app DB_PASSWORD=...   # Laravel .env
jdbc:mysql://db.example.com:3306/shop?sslMode=VERIFY_IDENTITY     # Java
app:secret@tcp(db.example.com:3306)/shop?parseTime=true           # Go
# URL encode special characters in the password: @ is %40, : is %3A, / is %2F

ORMs: see what SQL they send

DB::enableQueryLog(); ...; dd(DB::getQueryLog());                 // Laravel
print(Product.objects.filter(price__lt=250).query)                 # Django
engine = create_engine(url, echo=True)                             # SQLAlchemy
new PrismaClient({log: ["query"]})                                 // Prisma
SET GLOBAL general_log = ON;  -- every statement the server receives, for a few minutes only

Pool sizing and timeouts

-- total connections = pool size x app instances; keep it well under max_connections
SHOW VARIABLES LIKE 'wait_timeout';          -- idle connections are closed after this (default 8 hours)
-- set the pool's max lifetime below wait_timeout, or you get "MySQL server has gone away" (2006 / 2013)
SHOW GLOBAL STATUS LIKE 'Aborted_clients';

Tools, one line each

php artisan migrate                       # Laravel
python manage.py migrate                  # Django
npx prisma migrate deploy                 # Prisma
flyway -url=jdbc:mysql://db/shop migrate  # Flyway: V1__init.sql, V2__add_orders.sql
liquibase update                          # Liquibase changelogs
gh-ost --alter="ADD COLUMN source VARCHAR(20)" --database=shop --table=orders --execute
pt-online-schema-change --alter "ADD INDEX ix_source (source)" D=shop,t=orders --execute

Safe migration habits

-- add a column as NULL (or with a default) first, backfill in batches, then add NOT NULL
-- create indexes with ALGORITHM=INPLACE, LOCK=NONE, or gh-ost on big tables
-- never rename or drop a column the running app still reads: expand, deploy, then contract
-- set lock_wait_timeout low in the migration session so a blocked ALTER fails instead of queuing every query
SET SESSION lock_wait_timeout = 5;

MySQL REST Service: tables as endpoints

-- in MySQL Shell, SQL mode
CONFIGURE REST METADATA;
CREATE REST SERVICE /myService;
CREATE REST SCHEMA /shop ON SERVICE /myService FROM `shop`;
-- then publish tables and views from MySQL Shell or the VS Code extension;
-- MySQL Router serves the endpoints

The N+1 pattern in any ORM, and the fix

Order::with('items')->where('customer_id', 12)->get();                  // Laravel eager loading
Order.objects.filter(customer_id=12).prefetch_related("items")           # Django
prisma.order.findMany({where: {customerId: 12}, include: {items: true}})  // Prisma
SELECT * FROM order_items WHERE order_id IN (10042, 10049, 10051);       -- what eager loading sends

Parameters, never string building

-- vulnerable
query = "SELECT * FROM users WHERE email = '" + email + "'"

-- safe: a placeholder, the driver sends the value separately
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

-- dynamic column or direction: allow list, then build
ORDER_BY = {"price": "price", "newest": "created_at"}
sql = f"SELECT * FROM products ORDER BY {ORDER_BY.get(sort, 'id')} {'DESC' if desc else 'ASC'} LIMIT %s"

The checklist

mysql_secure_installation                       # root password, remove anonymous users and the test db
bind-address = 10.0.0.10                        # a private interface, never a public one
require_secure_transport = ON                   # refuse unencrypted connections
SELECT user, host FROM mysql.user WHERE host = '%';           -- who can connect from anywhere
SELECT user, host FROM mysql.user WHERE authentication_string = '';   -- accounts without a password
INSTALL COMPONENT 'file://component_validate_password';        -- password strength rules
SET PERSIST validate_password.policy = 'STRONG';
SHOW STATUS LIKE 'Ssl_cipher';                  -- empty means this session is not encrypted

Encryption at rest and auditing

-- keyring component configured in the data directory (component_keyring_file or a vault)
ALTER TABLE customers ENCRYPTION = 'Y';
SET PERSIST default_table_encryption = ON;
SET PERSIST binlog_encryption = ON;
SELECT table_schema, table_name, create_options FROM information_schema.tables WHERE create_options LIKE '%ENCRYPTION%';
-- secrets in columns: encrypt in the application, AES_ENCRYPT() keys end up in logs and history

One account per job

CREATE USER 'shop_app'@'10.0.%' IDENTIFIED BY '...';        -- runtime: DML only
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.%';
CREATE USER 'shop_migrate'@'10.0.%' IDENTIFIED BY '...';    -- deploys: DDL, used only by the pipeline
GRANT ALL ON shop.* TO 'shop_migrate'@'10.0.%';
CREATE USER 'shop_read'@'%' IDENTIFIED BY '...' REQUIRE SSL;  -- reporting, BI tools
GRANT SELECT ON shop.* TO 'shop_read'@'%';
ALTER USER 'shop_read'@'%' WITH MAX_USER_CONNECTIONS 5;

Keep secrets out of places they leak from

-- no passwords on command lines (shell history, ps): use option files or login paths
-- no credentials in the repository: environment variables or a secret manager
-- rotate with dual passwords: ALTER USER ... RETAIN CURRENT PASSWORD, deploy, DISCARD OLD PASSWORD
-- general_log and slow logs can contain literal values from queries: protect and rotate them

Is this connection encrypted, and is the server verified

SHOW SESSION STATUS WHERE Variable_name IN ('Ssl_cipher', 'Ssl_version');
SHOW VARIABLES LIKE 'tls_version';                    -- TLSv1.2,TLSv1.3
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ca.pem -h db.example.com -u app -p
ALTER USER 'app'@'%' REQUIRE X509;                     -- client certificate required
ALTER INSTANCE RELOAD TLS;                             -- pick up renewed certificates without a restart
# my.cnf: ssl_ca, ssl_cert, ssl_key; require_secure_transport = ON

Slow down password guessing

INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
SET PERSIST connection_control_failed_connections_threshold = 5;
SET PERSIST connection_control_min_connection_delay = 2000;    -- milliseconds, grows with each failure
SHOW GLOBAL STATUS LIKE 'Connection_control%';
ALTER USER 'app'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;   -- per account, 8.0.19+

Data masking: Enterprise functions, Community views

INSTALL COMPONENT 'file://component_masking';                 -- Enterprise, 8.0.33+
SELECT mask_pan(card_number) AS card FROM payments LIMIT 1;
SELECT mask_inner(phone, 2, 2) FROM customers LIMIT 1;
-- Community: a view that masks, and SELECT granted on the view only
CREATE VIEW v_payments AS SELECT id, CONCAT('**** ', RIGHT(card_number, 4)) AS card, amount FROM payments;
GRANT SELECT ON shop.v_payments TO 'support'@'%';

Logs and warnings

SHOW WARNINGS;                                   -- right after the statement
SHOW VARIABLES LIKE 'log_error';                 -- /var/log/mysql/error.log on most Linux packages
sudo tail -f /var/log/mysql/error.log
SELECT * FROM performance_schema.error_log ORDER BY logged DESC LIMIT 20;   -- 8.0.22+
perror 1213                                      -- explain any error code from the shell
journalctl -u mysql -n 100                       -- when the service will not start

Reserved words and case sensitivity

CREATE TABLE `order` (`key` INT, `rank` INT);   -- reserved words need backticks, or better, other names
SELECT * FROM information_schema.keywords WHERE reserved = 1 AND word IN ('RANK', 'GROUPS', 'KEY');
SHOW VARIABLES LIKE 'lower_case_table_names';   -- 0 on Linux: Orders and orders are different tables
-- lower_case_table_names can only be set when the data directory is initialised

Work from the network up

systemctl status mysql                      # 1. is it running
ss -ltnp | grep 3306                         # 2. listening on which address (bind-address)
nc -zv db.example.com 3306                   # 3. reachable through firewalls and security groups
SELECT user, host, plugin FROM mysql.user WHERE user = 'app';   # 4. an account for this client host
SHOW GRANTS FOR 'app'@'10.0.%';              # 5. privileges on the database it opens
tail -n 50 /var/log/mysql/error.log          # 6. the server's side of the story

Too many connections, gone away, packets

SHOW GLOBAL STATUS LIKE 'Max_used_connections';       -- near max_connections: a leak or no pool
SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user ORDER BY 2 DESC;
SHOW VARIABLES LIKE 'max_allowed_packet';              -- raise on both server and client for big rows
SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;

Retry deadlocks, treat duplicates as data

RETRYABLE = {1213, 1205, 2006, 2013}
for attempt in range(3):
    try:
        with cnx.cursor() as cur:
            cur.execute("START TRANSACTION")
            place_order(cur, order)
            cnx.commit()
        break
    except mysql.connector.Error as e:
        cnx.rollback()
        if e.errno == 1062: raise AlreadyExists() from e
        if e.errno not in RETRYABLE or attempt == 2: raise
        time.sleep(0.05 * (2 ** attempt) + random.random() / 20)

1118 row size too large, 1114 table is full

ALTER TABLE wide_import ROW_FORMAT=DYNAMIC;                   -- the default since 5.7, check old tables
ALTER TABLE wide_import MODIFY notes TEXT, MODIFY extra TEXT;  -- move big columns off page
-- ERROR 1114 (HY000): The table 'x' is full
df -h /var/lib/mysql                                           -- usually the disk, or tmpdir during a big ALTER
SHOW VARIABLES LIKE 'tmpdir';   SHOW VARIABLES LIKE 'temptable_max_ram';

The server will not start after a crash

sudo tail -n 100 /var/log/mysql/error.log         # the first ERROR line explains most failures
sudo -u mysql mysqld --validate-config             # syntax errors in my.cnf
# corrupted InnoDB pages: in my.cnf, one step at a time, 1 to 3 first
innodb_force_recovery = 1
# once it starts: dump everything, then rebuild a clean data directory and reload
mysqldump --all-databases --routines --events > rescue.sql

What changes on managed services

-- no SUPER: use the provider's stored procedures, for example on RDS
CALL mysql.rds_kill(812);
CALL mysql.rds_set_configuration('binlog retention hours', 168);
-- settings: RDS and Aurora parameter groups, Cloud SQL flags, Azure server parameters
-- replicas and failover: provider features, not CHANGE REPLICATION SOURCE TO
aws rds describe-db-engine-versions --engine mysql --query 'DBEngineVersions[].EngineVersion'

Behaviour that surprises people coming from other databases

'abc' = 'ABC'             -- true: the default collation is case insensitive
SELECT 1 + '1abc';        -- 2 with a warning: silent string to number conversion
GROUP BY a                -- no implicit ORDER BY since 8.0
"text"                    -- a string literal, unless sql_mode has ANSI_QUOTES
||                        -- logical OR, unless sql_mode has PIPES_AS_CONCAT
DDL inside a transaction  -- commits it: no transactional schema changes
TRUNCATE                  -- cannot be rolled back
NOW() in a transaction    -- the statement start time, not the transaction start time

No mysql_upgrade: the server does it on start

mysqlsh -- util check-for-server-upgrade root@localhost:3306 --target-version=9.7.2
sudo systemctl stop mysql
# install the new packages, keep the data directory
sudo systemctl start mysql
sudo grep -i upgrade /var/log/mysql/error.log
# my.cnf: upgrade = AUTO (default) | MINIMAL | FORCE | NONE

RETURNING and sequences

INSERT INTO tags (name) VALUES ('ash') RETURNING id;          -- 10.5+
DELETE FROM sessions WHERE expires_at < NOW() RETURNING id, user_id;
CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1;   -- 10.3+
SELECT NEXTVAL(invoice_seq), LASTVAL(invoice_seq);

System versioned tables: query the past

CREATE TABLE prices (sku VARCHAR(20) PRIMARY KEY, price DECIMAL(10,2)) WITH SYSTEM VERSIONING;
UPDATE prices SET price = 209 WHERE sku = 'OAK-1';
SELECT * FROM prices FOR SYSTEM_TIME AS OF TIMESTAMP '2026-09-01 00:00:00';
SELECT * FROM prices FOR SYSTEM_TIME ALL WHERE sku = 'OAK-1';

Tools, auth and JSON the MariaDB way

mariadb -u app -p shop                     # the client; mysql still works as an alias on most packages
mariadb-dump --single-transaction shop > shop.sql
mariadb-backup --backup --target-dir=/backups/base   # physical backups
CREATE USER 'app'@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');
SELECT JSON_VALUE(meta, '$.color') FROM products;    -- no -> operators for JSON
SHOW BINLOG STATUS;                         -- MariaDB spelling
-- Galera Cluster: synchronous multi primary replication, wsrep_* variables

Where similarity search runs

-- MySQL Community: store embeddings, compute similarity elsewhere
SELECT id, VECTOR_TO_STRING(embedding) FROM docs WHERE id IN (...);
-- HeatWave: search in the database
SELECT id, DISTANCE(embedding, STRING_TO_VECTOR(@q), 'COSINE') AS d FROM docs ORDER BY d LIMIT 5;
-- MariaDB 11.7+: VECTOR with an HNSW index and VEC_DISTANCE_COSINE()