mysql-aiops

MySQL AIops

Disclaimer: Community-maintained open-source project, not affiliated with, endorsed by, or sponsored by Oracle Corporation or the MariaDB Foundation. "MySQL" and "MariaDB" trademarks belong to their owners. Source at github.com/AIops-tools/MySQL-AIops under the MIT license.

Governed MySQL / MariaDB DBA operations — 35 MCP tools, every one wrapped with the bundled @governed_tool harness: a local unified audit log under ~/.mysql-aiops/, token/runaway budget guard, undo-token recording, and descriptive risk-tier labels. The account password is stored encrypted (~/.mysql-aiops/secrets.enc, Fernet + scrypt) — never plaintext on disk.

Standalone: the governance harness is bundled in the package (mysql_aiops.governance) — mysql-aiops has no external skill-family dependency. Behaviour is covered by a mock-based test suite; docs/VERIFICATION.md is the checklist for a live run against a real MySQL / MariaDB server.

What This Skill Does

The flagship analyses accept injected records for pure/offline analysis, or pull live from a configured target. top_queries / slow_query_rca require performance_schema=ON; the read account should have PROCESS, REPLICATION CLIENT and SELECT on performance_schema.

Quick Install

uv tool install mysql-aiops
mysql-aiops init       # interactive wizard: connection + encrypted password
mysql-aiops doctor     # connectivity + flavor + performance_schema + replica role

When to Use This Skill

• Triage a server (overview): version + flavor, uptime, connection headroom, sessions by command, longest query, most fragmented table, replica role
• Root-cause a slow query (analyze slow-query / slow_query_rca): the worst statement digest + EXPLAIN → cited cause and action (full scan, lock-time dominant, tmp-disk spill, N+1)
• Untangle a lock pile-up or deadlock (analyze lock-waits / lock_wait_rca): the wait-for tree with the root blocker named + the last deadlock parsed from SHOW ENGINE INNODB STATUS
• Diagnose replication (analyze replication / replication_lag_rca): IO/SQL thread state, Seconds_Behind_Source, error fields → cause + action
• Decide what to OPTIMIZE (analyze fragmentation / fragmentation_analysis): tables ranked by reclaimable data_free
• Find unused / redundant indexes; check table sizes and engines; inspect binlog/GTID state
• Kill a session or its query, OPTIMIZE/ANALYZE a table, create/drop an index (reversible), or SET GLOBAL a variable — all with dry-run + double-confirm

Do NOT use for PostgreSQL — use postgres-aiops. Do NOT use when the target is OT/industrial equipment (use industrial-aiops), a hypervisor, a storage appliance, a backup product, or a container cluster.

Related Skills — Skill Routing

Common Workflows

1. "The application is slow" — from complaint to a working index

1. mysql-aiops doctor → connectivity, detected flavor, and whether performance_schema is actually enabled (if it is off, the digest-based analysis below has nothing to read — fix that first).
2. mysql-aiops overview → one-shot: version, connection counts, buffer-pool and activity headline, so you know whether this is a query problem or a load problem.
3. mysql-aiops analyze slow-query → the worst statement digests, each with cited findings (full scan / no index used, lock time dominant, rows examined per row sent, tmp-table spill to disk, high call count) and a concrete action per finding.
4. mysql-aiops query top --limit 20 → confirm the digest the RCA blamed really is the top consumer, not a one-off.
5. mysql-aiops query explain "" → read the actual plan. access_type: ALL on a large table is the signature that an index will help; a plan already using an index means the fix is elsewhere.
6. mysql-aiops index unused and mysql-aiops index redundant → before adding one, check you are not duplicating an index that already exists (a redundant index costs writes and buys nothing).
7. mysql-aiops remediate create-index --name idx_x --dry-run → prints the exact DDL; re-run without --dry-run (double-confirm). The write is reversible and records an inverse drop_index undo descriptor.
8. Re-run mysql-aiops query explain "" and analyze slow-query to prove the plan changed and the digest dropped.
9. Failure branch: if the plan did not change, the optimizer may be working from stale statistics — mysql-aiops remediate analyze-table and re-check. If the index made things worse (write amplification, or the optimizer picking it wrongly), reverse it: mysql-aiops undo list → mysql-aiops undo apply drops exactly the index that was created. Index DDL on a large table can be long-running — if it stalls, mysql-aiops activity long --min-seconds 60 will show it, and cancelling mid-DDL is its own risk, so size the table with mysql-aiops table sizes before step 7.

2. A lock pile-up is stalling writes

1. mysql-aiops activity lock-waits → the raw blocking/blocked pairs, straight from the server.
2. mysql-aiops analyze lock-waits → the wait-for tree resolved down to the root blocker session, with the last deadlock (victim + both statements) attached.
3. mysql-aiops activity transactions → what the root blocker is actually doing and how long it has been open. An idle-in-transaction blocker is an application bug, not a database one.
4. mysql-aiops activity sessions --no-sleeping → confirm the blocker's user, host, and statement before you touch it.
5. Cancel the statement, not the connection, if that is enough: mysql-aiops remediate kill-query --dry-run then for real (double-confirm). Escalate to mysql-aiops remediate kill only if the session must go.
6. Re-run mysql-aiops analyze lock-waits → the tree should be empty.
7. Failure branch: kill and kill-query are irreversible — they record no undo, and killing a long-running transaction triggers a rollback that can itself take a long time and hold locks meanwhile. If the tree does not clear, do not kill more sessions in a loop (the runaway budget guard will stop you anyway): re-read activity transactions to see whether the rollback is in progress, and go after the application holding the transaction open instead.

3. A replica has fallen behind

1. mysql-aiops analyze replication → the cited cause: IO thread stopped (with the real Last_IO_Error), SQL thread stopped (with Last_SQL_Error), applier simply lagging, or an intentional SQL_Delay.
2. mysql-aiops repl status → the raw replica record, so you can see the seconds-behind value and thread states the analysis quoted. Note the tool branches on flavor automatically (SHOW REPLICA STATUS on MySQL, SHOW SLAVE STATUS on MariaDB).
3. mysql-aiops repl binlog → binlog position and retention, to judge whether the replica can still catch up or has fallen off the end of the logs.
4. mysql-aiops overview on the replica → check the lag is not just resource pressure masquerading as a replication fault.
5. Apply the cause-specific fix: connectivity/credentials for a stopped IO thread, the diverged row for a stopped SQL thread, or parallel apply for a slow applier — mysql-aiops remediate set slave_parallel_workers 4 --dry-run first (reversible; the prior value is captured as the undo descriptor).
6. Failure branch: an intentional SQL_Delay is not a fault — the analysis says so, and "fixing" it defeats a deliberate safety window. If a SET GLOBAL made things worse, mysql-aiops undo apply restores the prior value. If the replica has fallen off the retained binlogs, no setting will recover it — it needs a reseed, which is out of this tool's scope.

4. Reclaim space from a bloated table

1. mysql-aiops analyze fragmentation → tables ranked by reclaimable data_free, each citing the measured bytes.
2. mysql-aiops table sizes and mysql-aiops table fragmentation → confirm the size and free space independently, and see how big the rebuild will actually be.
3. mysql-aiops index unused → while you are here, an index nothing has used is dead weight; mysql-aiops index stats shows the usage numbers behind that claim.
4. mysql-aiops remediate drop-index --dry-run then for real — the write rebuilds the index definition from SHOW CREATE TABLE before dropping, so the undo descriptor recreates exactly the index that existed.
5. mysql-aiops remediate optimize --dry-run → preview, then re-run to OPTIMIZE TABLE (double-confirm).
6. Re-run mysql-aiops analyze fragmentation to confirm the space came back.
7. Failure branch: OPTIMIZE TABLE rebuilds the table and can lock or block writes for the duration on a large table — run it in a maintenance window, and check mysql-aiops activity long if the system goes quiet. It records no undo (there is nothing to reverse). If dropping the index turned out to be wrong, mysql-aiops undo apply recreates it from the captured definition — this is the one step in this recipe that is reversible, which is why it comes before the OPTIMIZE.

Offline analysis (no live server)

Pass data straight to the analysis tools — slow_query_rca(statements=[...]), lock_wait_rca(pairs=[...]), replication_lag_rca(status={...}), or fragmentation_analysis(tables=[...]) — to analyse an exported dataset without connecting.

Governance & Safety

The skill delivers reads and writes and records them; it does not decide whether a write is permitted. That is your agent's judgement, or the permission of the account you connect it with (point it at a MySQL/MariaDB account granted only SELECT / PROCESS / REPLICATION CLIENT and no write privileges (no INSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy file, or approval gate.

• Audit is the guarantee, and it is not bypassable. Every operation — MCP and CLI alike — is logged to ~/.mysql-aiops/audit.db (relocatable via MYSQL_AIOPS_HOME): params, result, status, duration, and the risk tier. The CLI writes the same row the MCP path does.
• MYSQL_AUDIT_APPROVED_BY / MYSQL_AUDIT_RATIONALE are optional annotations recorded on the audit row (who/why); they are never required and never block.
• Runaway guard — a safety backstop, not authorization: the same call looped in a tight window trips a circuit breaker. Disable with MYSQL_RUNAWAY_MAX=0.
• Writes support --dry-run / dry_run=True and double confirmation at the CLI.
• Reversible writes fetch the real before-state and record an inverse descriptor; irreversible ops (kill session/query, optimize/analyze, reset stats) record prior state only.
• All values are bound query parameters; identifiers that cannot be parameterised are validated and backtick-quoted.

References

• references/capabilities.md — full tool + field reference
• references/cli-reference.md — CLI command reference
• references/setup-guide.md — onboarding, credentials, and connectivity