MySQL / MariaDB Operations AI Skill Guide

Overview

MySQL (and MariaDB) store relational data with InnoDB as the default OLTP engine. Agents help with schema design, indexing, backup/restore, user grants, and cautious DDL. Production changes should be online-friendly: avoid long table locks, always take backups before destructive SQL, and never run DELETE/UPDATE without a WHERE unless explicitly requested with confirmation.

Clients / app pools
        |
        v
MySQL primary (InnoDB)
        |
        +--> replicas (async/semi-sync)
        +--> backups (mysqldump / XTBackup / snapshots)

When to use

• Writing or reviewing schema migrations and indexes
• Diagnosing slow queries and missing indexes
• Creating users with least-privilege grants
• Dumping/restoring databases in lower environments

Operational directives

1. Prefer InnoDB; use explicit primary keys on every table.
2. Take a backup or confirm PITR before destructive DDL/DML.
3. Add indexes concurrent to traffic when tools allow; estimate table size first.
4. Use least-privilege users per app (SELECT/INSERT/UPDATE only as needed).
5. Never print production passwords; prefer socket auth or secret managers.

Concrete examples

Schema + index

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id   BIGINT UNSIGNED NOT NULL,
  status        VARCHAR(32) NOT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_customer_created (customer_id, created_at),
  KEY idx_orders_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Grants

CREATE USER 'api'@'%' IDENTIFIED BY RANDOM PASSWORD;
GRANT SELECT, INSERT, UPDATE ON app.orders TO 'api'@'%';
FLUSH PRIVILEGES;

Dump / restore

mysqldump --single-transaction --routines --triggers -u root -p app > app.sql
mysql -u root -p app < app.sql

Slow query clues

SHOW CREATE TABLE orders\G
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
SHOW INDEX FROM orders;

Operations matrix

Task	Safer approach
Add nullable column	Simple ALTER usually OK; still test on copy
Add index on large table	Online DDL / pt-online-schema-change
Delete old rows	Batched deletes with sleep; avoid one huge txn
Change column type	May rebuild table - schedule maintenance

Best practices

1. utf8mb4 + explicit collations; avoid legacy utf8 (3-byte) surprises.
2. Monitor replication lag before failing over.
3. Keep migrations idempotent and forward-only in app deploy pipelines.
4. Use connection pooling; set sensible max_connections and timeouts.

Limitations

• EXPLAIN plans vary by version/statistics - validate on production-like data.
• Group Replication / InnoDB Cluster topologies need specialized runbooks.
• Agents must not invent restore success without checksum or smoke tests.

Related skills

• sqlite - embedded alternative for local/dev
• vault - dynamic DB credentials
• docker - local MySQL via Compose
• opentelemetry - DB client spans and pool metrics