mysql-aiops

---
name: mysql-aiops
description: >
license: MIT
---

# 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](https://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

| Domain | Tools | Count | Read or Write |
|--------|-------|:-----:|:-------------:|
| **Overview** | server health snapshot (version+flavor, connections, replica role) | 1 | 1 read |
| **Server** | version+flavor, variables, status, databases, engines, connection stats | 6 | 6 read |
| **Activity** | sessions, long-running queries, transactions, lock waits | 4 | 4 read |
| **Queries** | top-N statement digests, EXPLAIN FORMAT=JSON | 2 | 2 read |
| **Indexes** | unused, redundant/duplicate, cardinality stats | 3 | 3 read |
| **Tables** | sizes, data_free fragmentation, engine/row-format status | 3 | 3 read |
| **Replication** | replica status/lag, binlog/GTID | 2 | 2 read |
| **Analysis (flagship)** | slow-query RCA, lock-wait & deadlock RCA, replication-lag RCA, fragmentation | 4 | 4 read |
| **Writes** | kill-session, kill-query, drop-index | 3 | 3 write (high) |
| | optimize, analyze-table, create-index, SET GLOBAL, reset-stats | 5 | 5 write (medium) |
| **Undo** | undo list, undo apply | 2 | 1 read / 1 write |

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

```bash
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

| If the user wants… | Use |
|--------------------|-----|
| MySQL / MariaDB DBA-ops: slow queries, lock waits, replication, fragmentation | **mysql-aiops** (this skill) |
| PostgreSQL DBA-ops | **postgres-aiops** |
| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |
| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |
| Container/cluster lifecycle | a cluster ops skill |

## 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 "<sql>"` → 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 <table> <col> --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 "<sql>"` 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 <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 <id>` 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 <session-id> --dry-run` then for real
   (double-confirm). Escalate to `mysql-aiops remediate kill <session-id>` 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 <id>` 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 <table> <index-name> --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 <table> --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 <id>` 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