Mysql Agent Skill

MySQL / MariaDB engine

You are working below portable SQL, at the layer where the answer depends on *which engine* is

running. This skill owns MySQL 8.4 LTS and MariaDB 11.8 LTS: the InnoDB clustered-index storage

model, MySQL-flavoured DDL and types, index design and the leftmost-prefix rule, reading EXPLAIN

and fixing the plan, online DDL, replication, locking/deadlocks, and day-2 server config.

The dividing line is simple: **if the answer is identical on PostgreSQL, it belongs in sql, not

here.** sql owns the dialect-independent SELECT grammar. mysql owns how *this* engine stores,

plans, locks, and replicates. postgresdb is the peer engine for the other database — same body

shape, different facts, never the same answer.

When to use

• Designing or reviewing a MySQL/MariaDB schema: engine choice, integer/DECIMAL/VARCHAR sizing,

utf8mb4 charset/collation, JSON + generated/STORED columns, PK design for InnoDB.

• A query is slow or scans too many rows; reading EXPLAIN / EXPLAIN ANALYZE / FORMAT=JSON.
• Choosing or adding an index: composite column order, covering indexes, prefix indexes on TEXT,

invisible indexes for safe rollout, why an index is *not* used.

• Schema change on a large/hot table without downtime: ALGORITHM=INSTANT/INPLACE/COPY, pt-osc, gh-ost.
• Replication: binlog row format, GTID (incl. tagged GTIDs), replica lag, semi-sync, group replication.
• Locking/concurrency: deadlocks, gap/next-key locks, REPEATABLE READ, SELECT ... FOR UPDATE.
• Operating the server: buffer pool, caching_sha2_password + TLS, slow-query log, performance_schema.
• Migrating 5.7/8.0 → 8.4 LTS, or reasoning about MySQL ↔ MariaDB divergence.

When NOT to use

| The ask | Goes to |

|---|---|

| Portable query craft — joins, window functions, CTEs, NULL 3VL | sql |

| PostgreSQL engine behaviour — MVCC, VACUUM, RLS, JSONB, PgBouncer | postgresdb |

| PlanetScale / Vitess branch + deploy-request workflow, no-FK design | planetscale |

| Vendor-neutral migration *theory* — expand-contract, batched backfill | db-migrations |

| ORM / query-builder API ergonomics | drizzle-orm, prisma-orm |

| Backup *strategy* / retention / restore drills as a discipline | backups |

| OLAP / columnar analytics | clickhouse-analytics, duckdb |

The boundaries with planetscale and db-migrations are sharp: this skill owns the raw-MySQL

mechanics (EXPLAIN, index choice, ALGORITHM=, gh-ost). PlanetScale wraps those in its platform

workflow; db-migrations wraps them in vendor-neutral strategy. You own the knobs they ride on.

Pick your version first

Get this wrong and every later decision (auth, vector, isolation defaults) is wrong too.

| Target | Use it when | Watch out |

|---|---|---|

| MySQL 8.4 LTS | Default for conservative production. GA 2024-04-30, supported through April 2032. | mysql_native_password is disabled by default here. |

| MySQL 9.x Innovation | Only if you need VECTOR or the newest features and accept short support. | Short-lived track; mysql_native_password is removed. Not for stable prod. |

| MariaDB 11.8 LTS | The fork; 2025 yearly LTS, first MariaDB LTS with native vector search. | Auth, vector syntax, and RETURNING differ from MySQL — not drop-in compatible; innodb_snapshot_isolation defaults ON. |

VECTOR is a MySQL 9.0 (Innovation) feature, *not* in 8.4 LTS. MariaDB 11.8 also has VECTOR but

with different functions (VEC_DISTANCE_COSINE() vs MySQL's STRING_TO_VECTOR()) — see

references/mysql-vs-mariadb.md. Do not assume one's vector SQL runs on the other.

Non-negotiables

• utf8mb4, always — at the column level. Legacy utf8 (alias utf8mb3) is 3-byte and

silently truncates emoji and supplementary characters. Default collation is utf8mb4_0900_ai_ci.

Setting it on the connection only is not enough; set it on the column.

• Small monotonic PRIMARY KEY. An InnoDB table *is* its PK B-tree, and every secondary index

stores the PK as its row pointer. A random UUID/CHAR(36) PK bloats every secondary index and

wrecks insert locality. Use BIGINT AUTO_INCREMENT or an ordered UUIDv7 stored as BINARY(16).

• binlog_format=ROW + GTID. ROW is the only reliable replication format; GTID gives each

transaction a globally unique id with auto-skip so it applies at most once per replica.

• caching_sha2_password + TLS. It is the default auth plugin and SHA-256 based; clients need

TLS for first-time auth. mysql_native_password is disabled by default in 8.4 and gone in 9.0 —

do not design around it.

• Index column order follows the leftmost prefix. INDEX (a,b,c) serves a, a,b, a,b,c —

never b alone. Put equality columns first, then the range/ORDER BY column.

• Never ALTER a hot table without choosing an algorithm. Default COPY locks and rebuilds.

Pick INSTANT/INPLACE, or use gh-ost / pt-osc, *before* you run it at peak.

• REPEATABLE READ + next-key (gap) locks → short, consistently-ordered transactions. This is

the InnoDB default and the usual deadlock source. Acquire rows in the same order everywhere.

• Measure with EXPLAIN ANALYZE, do not guess. The optimizer's rows is an estimate;

EXPLAIN ANALYZE runs the query and reports actual rows and timing.

Index decision

| You have | Use |

|---|---|

| One column in WHERE, high selectivity | Single-column index |

| Multiple WHERE columns + an ORDER BY | Composite index: equality cols first, then range/sort col (leftmost prefix) |

| Query reads only indexed columns | Covering index (add the selected cols) — avoids the PK back-lookup |

| Filtering a long TEXT/VARCHAR prefix | Prefix index col(20) — can't be covering, watch selectivity |

| Rolling out an index on a hot table safely | INVISIBLE index, then flip VISIBLE once verified |

-- Bad: separate single-column indexes; the optimizer uses at most one, then filesorts.
CREATE INDEX idx_uid ON orders (user_id);
CREATE INDEX idx_created ON orders (created_at);
-- Query: WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC

-- Good: one composite index — equality (user_id) first, then the range/sort column.
-- This serves the WHERE and the ORDER BY with no separate sort step.
CREATE INDEX idx_user_created ON orders (user_id, created_at);

Read EXPLAIN

EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports actual rows/time;

EXPLAIN FORMAT=JSON shows cost and used-key-parts. Read the access type first — it is the ladder

from worst to best:

ALL (full scan) → index (full index scan) → range → ref → eq_ref → const.

Anything ALL on a large table is a red flag. Then check rows (estimated rows examined),

filtered (% surviving the WHERE), and the Extra flags: Using filesort (extra sort pass),

Using temporary (materialised temp table), Using index (covering — good, no back-lookup).

The most common cause of a missed index is a non-sargable predicate — a function or implicit

charset/type cast wrapping the indexed column:

-- Bad: DATE() wraps the indexed column → the index on created_at can't be used → type=ALL.
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';

-- Good: range over the raw column → index range scan (type=range).
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';

A subtler version: joining a utf8mb4 column to a latin1 column, or a VARCHAR to an INT,

forces a per-row cast and disables the index. Make both sides the same type and collation. Full

field-by-field reading, the type ladder, and every "why no index" cause are in

references/indexing-and-explain.md.

Online DDL chooser

| Operation / situation | Use |

|---|---|

| Add column at end, rename column, set default, drop index | ALGORITHM=INSTANT — metadata-only, near-free (8.0+) |

| Add secondary index, change column nullability inplace | ALGORITHM=INPLACE, LOCK=NONE — rebuilds without blocking most writes |

| What INSTANT/INPLACE can't do, on a small/cold table | ALGORITHM=COPY — locks + rebuilds; fine off-hours |

| Same change on a large/hot table, zero downtime | gh-ost or pt-online-schema-change — shadow table + swap |

-- INSTANT: adding a column at the end is metadata-only in 8.0+. Always be explicit so a
-- silent fall-through to COPY (which locks) can't happen.
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT, LOCK=NONE;

# gh-ost: build a shadow table, copy + tail the binlog, then atomic cutover. Always --dry-run
# first; throttle on replica lag so you don't melt production.
gh-ost \
  --host=primary.db --database=shop --table=orders \
  --alter="ADD INDEX idx_user_created (user_id, created_at)" \
  --max-lag-millis=1500 --throttle-control-replicas="replica1.db" \
  --execute   # drop --execute to dry-run

If gh-ost refuses to read the binlog, run pt-online-schema-change, which uses triggers instead.

Both, plus the rollback path and how this composes with db-migrations expand-contract theory, are

in references/online-ddl-and-migrations.md.

Copy-paste patterns

-- Covering index: the query reads only (user_id, status, total), so put them all in the index.
-- EXPLAIN then shows "Using index" — no trip back to the PK leaf for each row.
SELECT status, total FROM orders WHERE user_id = ?;
CREATE INDEX idx_cover ON orders (user_id, status, total);

-- GTID replication on the replica: GTID auto-positioning, no log file/pos bookkeeping.
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.db', SOURCE_USER='repl', SOURCE_PASSWORD='***',
  SOURCE_SSL=1, SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Replica lag: read the field, don't eyeball. Seconds_Behind_Source is coarse; for accuracy use
-- performance_schema replication tables. NULL means replication is broken, not "0 lag".
SHOW REPLICA STATUS\G   -- Replica_IO_Running / Replica_SQL_Running / Seconds_Behind_Source
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- Deadlock post-mortem: InnoDB rolls back the cheaper transaction and logs the cycle here.
SHOW ENGINE INNODB STATUS\G   -- read the LATEST DETECTED DEADLOCK section

# Consistent logical dump without locking every table: single transaction over InnoDB.
mysqldump --single-transaction --set-gtid-purged=AUTO --routines --triggers shop > shop.sql

Replication topologies (async / semi-sync / group replication / InnoDB Cluster + MySQL Router),

failover, and read-replica routing are in references/replication-and-ha.md.

MySQL vs MariaDB divergence

They share a heritage and diverge in ways that break copy-pasted SQL. Do not assume parity.

| Area | MySQL 8.4 / 9.x | MariaDB 11.8 |

|---|---|---|

| Default auth | caching_sha2_password | mysql_native_password / ed25519 |

| VECTOR | MySQL 9.0+ only; STRING_TO_VECTOR() | Native in 11.8; VEC_DISTANCE_COSINE() — different syntax |

| RETURNING | INSERT ... RETURNING only (8.0+) | INSERT/UPDATE/DELETE ... RETURNING |

| Sequences | No CREATE SEQUENCE | CREATE SEQUENCE supported |

| System-versioned (temporal) tables | Not supported | WITH SYSTEM VERSIONING supported |

| Snapshot isolation | RR snapshot, no write-conflict detection | innodb_snapshot_isolation defaults ON |

| JSON | Native binary JSON type | Historically a LONGTEXT alias; check version |

Depth and both-direction migration gotchas: references/mysql-vs-mariadb.md.

Anti-patterns / rationalizations → STOP

| Rationalization | Reality | Do instead |

|---|---|---|

| "utf8 is Unicode, it's fine." | utf8 = 3-byte utf8mb3; emoji silently become ????. | utf8mb4 at the column level. |

| "A random UUID PK is clean and unique." | Random PK bloats every secondary index and kills insert locality in the clustered index. | BIGINT AUTO_INCREMENT or ordered UUIDv7 as BINARY(16). |

| "STATEMENT binlog is smaller, use it." | Non-deterministic statements replicate wrong; silent data drift on replicas. | binlog_format=ROW. |

| "Just keep using mysql_native_password." | Disabled by default in 8.4, removed in 9.0 — your upgrade breaks. | caching_sha2_password + TLS. |

| "Wrapping the column in DATE()/LOWER() is readable." | Function on an indexed column → full scan. | Rewrite to a sargable range; or add a generated column + index. |

| "SELECT * is convenient." | Pulls wide InnoDB rows off-disk and defeats covering indexes. | Select only needed columns. |

| "I'll hold the transaction open while I do other work." | RR + gap locks held long → deadlocks and lock waits everywhere. | Keep transactions short; commit fast; order rows consistently. |

| "ALTER it now, traffic is fine." | COPY algorithm locks a multi-GB table; outage at peak. | Pick INSTANT/INPLACE, or gh-ost off-peak. |

| "EXPLAIN says 12 rows, so it's fast." | rows is an *estimate* from stats. | Confirm with EXPLAIN ANALYZE (actual rows/time). |

| "MyISAM is faster for our table." | No transactions, no FKs, table-level locks, crash-unsafe. | InnoDB for anything transactional. |

Verify

Run scripts/verify.sh from your project root. It is read-only and **never connects to a

database** — it heuristically lints discovered *.sql and *.cnf/my.cnf files for the foot-guns

above (legacy utf8, MyISAM, random-UUID PK, binlog_format=STATEMENT, mysql_native_password,

function-wrapped indexed columns) and checks balanced delimiters. It exits non-zero only on

unbalanced delimiters or a committed binlog_format=STATEMENT; every schema heuristic is advisory.

It optionally runs sqlfluff --dialect mysql if installed.

See Also

• ../sql/SKILL.md — portable, engine-agnostic SELECT/JOIN/window-function craft.
• ../postgresdb/SKILL.md — the peer engine (PostgreSQL): MVCC, VACUUM, RLS, JSONB.
• ../planetscale/SKILL.md — the PlanetScale/Vitess platform workflow on top of MySQL.
• db-migrations — vendor-neutral migration strategy (expand-contract) that the ALGORITHM=/gh-ost

mechanics here ride on.

• backups — backup strategy, retention, and restore drills as a discipline.