{
  "markdown": "<!-- mcp-name: io.github.AIops-tools/mysql-aiops -->\n\n# MySQL AIops\n\n> **Disclaimer**: Community-maintained open-source project. **Not affiliated with, endorsed by, or sponsored by Oracle Corporation or the MariaDB Foundation.** \"MySQL\" is a trademark of Oracle Corporation; \"MariaDB\" is a trademark of MariaDB plc; all product/trademark names belong to their respective owners. MIT licensed.\n\nGoverned AI-ops for **MySQL 8.x and MariaDB 10.6+ DBA operations** — connecting\nto a server with **PyMySQL** and reading `information_schema` /\n`performance_schema` — with a **built-in governance harness**: unified audit\nlog, token/runaway budget guard, undo-token recording, and descriptive\nrisk-tier labels. The server **flavor** (mysql vs mariadb) is\ndetected from `version()` and flavor-dependent statements branch automatically\n(`SHOW REPLICA STATUS` vs `SHOW SLAVE STATUS`).\n\n## What it does\n\nFour flagship signature analyses, plus the guarded reads and writes around them:\n\n- **Slow-query RCA** — take the worst `events_statements_summary_by_digest`\n  entry (plus an optional `EXPLAIN FORMAT=JSON` plan) and map its numbers —\n  no-index share (`SUM_NO_INDEX_USED`), lock-time share, rows-examined/sent\n  ratio, tmp-disk spill, call count, plan access types — to a cited cause and a\n  concrete action. Every finding carries its measured number, not a black-box\n  verdict.\n- **InnoDB lock-wait & deadlock chain RCA** — build the wait-for tree from\n  `performance_schema.data_lock_waits` (MariaDB:\n  `information_schema.innodb_lock_waits`), name the **root blocker** (blocks\n  others, waits on none), and parse the **last deadlock** out of\n  `SHOW ENGINE INNODB STATUS`.\n- **Replication lag RCA** — map the replica's IO/SQL thread state,\n  `Seconds_Behind_Source` and error fields to a cited cause + action\n  (stopped IO thread, failed applier statement, lagging applier, intentional\n  `SQL_Delay`).\n- **Table fragmentation analysis** — rank tables by reclaimable `data_free`\n  from `information_schema.tables` into cited `OPTIMIZE TABLE` candidates.\n\n## What works\n\n- **CLI** (`mysql-aiops ...`): `init`, `overview`, `server`, `activity`, `query`, `index`, `table`, `repl`, `analyze`, `remediate`, `secret`, `doctor`, `mcp`.\n- **MCP server** (`mysql-aiops mcp` or `mysql-aiops-mcp`): **35 tools** (26 read, 9 write), every one wrapped with the bundled `@governed_tool` harness.\n- **Encrypted credentials**: the account password lives in an encrypted store `~/.mysql-aiops/secrets.enc` (Fernet + scrypt) — **never plaintext on disk**. Unlock with a master password from `MYSQL_AIOPS_MASTER_PASSWORD` (MCP/CI) or an interactive prompt (CLI).\n- **Reversibility**: mutating writes fetch the **real before-state first** and record a faithful inverse — `create_index`↔`drop_index`; `drop_index` captures the index definition out of `SHOW CREATE TABLE` so undo recreates it exactly; `set_global_variable` captures the prior value from `SHOW GLOBAL VARIABLES` so undo sets it back. Irreversible ops (`kill_session`, `kill_query`, `optimize_table`, `analyze_table`, `reset_query_stats`) record prior state for audit but declare no undo.\n- **Safety**: every state-changing CLI op supports `--dry-run` and requires double confirmation; every write MCP tool takes a `dry_run` preview. All identifiers that cannot be parameterised (schema/table/index/column/variable names) are validated against a strict charset and backtick-quoted; all values are bound query parameters.\n\n## What this tool does, and does not, decide\n\nIt delivers MySQL / MariaDB DBA operations — reads and writes — accurately and\nefficiently, and records every one of them. It does **not** decide whether a write is\nallowed to happen. That is the agent's judgement, or the permission of the account you\nconnect it with: point it at a MySQL/MariaDB account granted only SELECT / PROCESS /\nREPLICATION CLIENT and no write privileges (no INSERT/UPDATE/DELETE/DDL), and the\nwrites fail at the server — the place that actually owns the permission.\n\nSo there is no read-only switch, no policy file, no approval gate to configure. The one\nthing the tool guarantees is that nothing is silent: **every call, over MCP and over the\nCLI alike, lands an audit row** in `~/.mysql-aiops/audit.db`, and destructive writes still\ncapture their before-state and record an inverse where one exists.\n\n> Each tool declares a `risk_level`, carried into the audit row as a descriptive tier\n> (none/confirm/review) — so a reviewer can see at a glance that a row was a high-risk delete. It\n> is a label, not a gate.\n\nRunning a smaller / local model? See\n[agent-guardrails.md](skills/mysql-aiops/references/agent-guardrails.md) — it lists\nthe guardrails this tool now enforces for you (so you don't spend prompt budget\nrestating them) and gives a ready-made system prompt for what's left.\n\n## Capability matrix (35 MCP tools)\n\n| Domain | Tools | Count | R/W |\n|--------|-------|:-----:|:---:|\n| **Overview** | `overview` | 1 | read |\n| **Server** | `server_version`, `show_variables`, `show_status`, `list_databases`, `list_engines`, `connection_stats` | 6 | read |\n| **Activity** | `list_sessions`, `long_running_queries`, `list_transactions`, `lock_waits` | 4 | read |\n| **Queries** | `top_queries`, `explain_query` | 2 | read |\n| **Indexes** | `unused_indexes`, `redundant_indexes`, `index_stats` | 3 | read |\n| **Tables** | `table_sizes`, `table_fragmentation`, `table_status` | 3 | read |\n| **Replication** | `replica_status`, `binlog_status` | 2 | read |\n| **Analysis (flagship)** | `slow_query_rca`, `lock_wait_rca`, `replication_lag_rca`, `fragmentation_analysis` | 4 | read |\n| **Writes** | `kill_session`, `kill_query`, `drop_index` | 3 | write (high) |\n| | `optimize_table`, `analyze_table`, `create_index`, `set_global_variable`, `reset_query_stats` | 5 | write (medium) |\n| **Undo** | `undo_list`, `undo_apply` | 2 | read / write |\n\nThe flagship analyses accept injected records for pure/offline analysis, or pull\nlive from a configured target. `top_queries`/`slow_query_rca` require\n`performance_schema=ON`; the read account should have `PROCESS`,\n`REPLICATION CLIENT` and `SELECT` on `performance_schema`.\n\n## Support scope\n\n| Platform | Status |\n|----------|--------|\n| MySQL 8.0 / 8.4 | targeted (`SHOW REPLICA STATUS`, `performance_schema.data_lock_waits`) |\n| MariaDB 10.6+ / 11.x | targeted (`SHOW SLAVE STATUS`, `information_schema.innodb_lock_waits`) |\n| MySQL 5.7 and older | not targeted (EOL; pre-8.0 digest/lock views untested) |\n| Cloud-managed MySQL (RDS/Aurora/Cloud SQL flavors) | wire-compatible reads should work; managed restrictions (KILL, SET GLOBAL) apply — untested |\n\n## Quick start\n\n### As a Claude Code plugin\n\nOne install gives an agent both the skill and the MCP server:\n\n```\n/plugin marketplace add AIops-tools/marketplace\n/plugin install mysql-aiops@aiops-tools\n```\n\nThe MCP server is fetched with [uv](https://docs.astral.sh/uv/) and pinned to the\npackage version this plugin declares, so an audit row can be traced back to the\ncode that wrote it. Credentials are still configured with `mysql-aiops init` — see below.\n\n### As a CLI or standalone MCP server\n\n```bash\nuv tool install mysql-aiops             # or: pipx install mysql-aiops\nmysql-aiops init                        # wizard: add a target + store the password (encrypted)\nmysql-aiops doctor                      # verify config, secrets, connectivity, flavor, perf-schema\nmysql-aiops overview                    # one-shot server health snapshot\nmysql-aiops analyze slow-query          # RCA the worst statement digest\nmysql-aiops analyze fragmentation       # OPTIMIZE TABLE candidates\n```\n\nRun as an MCP server (stdio):\n\n```bash\nexport MYSQL_AIOPS_MASTER_PASSWORD=...  # unlock secrets non-interactively\nmysql-aiops-mcp\n```\n\nClaude Desktop / MCP client config:\n\n```json\n{\n  \"mcpServers\": {\n    \"mysql-aiops\": {\n      \"command\": \"uvx\",\n      \"args\": [\"--from\", \"mysql-aiops\", \"mysql-aiops-mcp\"],\n      \"env\": { \"MYSQL_AIOPS_MASTER_PASSWORD\": \"your-master-password\" }\n    }\n  }\n}\n```\n\n> **Env-block caveat**: the `env` block above is the only environment the MCP\n> server sees — GUI-launched clients do **not** inherit your shell profile. Put\n> `MYSQL_AIOPS_MASTER_PASSWORD` (and `MYSQL_AIOPS_HOME` / `MYSQL_AUDIT_APPROVED_BY`\n> if you use them) there, or the server cannot unlock the secret store.\n\n## Governance\n\nEvery MCP tool passes through the bundled `@governed_tool` harness:\n\n- **Audit** — every call (params, result, status, duration, risk tier, and any operator-supplied approver/rationale) is logged to `~/.mysql-aiops/audit.db` (relocatable via `MYSQL_AIOPS_HOME`). The CLI writes the same row the MCP path does — there is no unaudited entry point.\n- **Runaway guard** — a safety backstop, not an authorization gate: the same call hammered in a tight loop trips a circuit breaker. Disable with `MYSQL_RUNAWAY_MAX=0`; optional hard ceilings via `MYSQL_MAX_TOOL_CALLS` / `MYSQL_MAX_TOOL_SECONDS`.\n- **Undo recording** — reversible writes record an inverse descriptor built from the fetched before-state.\n- **Risk tier** — a descriptive label on the audit row derived from `risk_level`; it gates nothing.\n\n## Scope\n\nThis is the **MySQL / MariaDB DBA-ops** member of the AIops-tools family\n(governed AI-ops with audit + budget + undo + risk tiers). Do **NOT** use it\nfor PostgreSQL — use **postgres-aiops**. Do **NOT** use it for OT / industrial\nedge — see the separate `industrial-aiops` line — nor for application-schema\nmigrations or ORM management.\n\n## Missing a capability?\n\nCoverage is intentionally a curated subset of MySQL's catalogs and maintenance\nsurface. Missing a view, a metric, or a maintenance command? **Open an issue or\nPR** — contributions welcome. 缺功能提 issue/PR 欢迎留言。\n\n## Verification status\n\n**Live-verified against MySQL 8.4.10 and MariaDB 11.8.8 (2026-07-19/20).**\nConnectivity, the reads, `analyze slow-query` on genuine full scans, and the full\ngovernance loop (real `create_index` → audit row → undo actually dropping it) were\nexercised against a real server. That run found and fixed a\nreal bug the mock suite could not see: `SUM()` aggregates come back as `Decimal`,\nwhich is not JSON serializable.\n\nThe MariaDB branch is now verified too, including `analyze lock-waits` against real\nrow contention (it correctly identified the root blocker and the measured wait).\n**Replication against a real replica** remains unverified.\n\n[docs/VERIFICATION.md](docs/VERIFICATION.md) records exactly what was checked and what\nis still open. `mysql-aiops doctor` is the fastest live check (connectivity, flavor,\nperformance_schema, replica role).\n",
  "bytes": 10603,
  "sha": "c649cca3041d95282ed41deaedeb70e039f28a0f81e8d435fd7c857e21cef126",
  "repo_slug": "aiops-tools/mysql-aiops",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_aiops_tools_mysql_aiops_2ab6a89c/readme"
}