mysql-ops-mcp

A read-only-first MCP server that lets an AI agent query a private MySQL database — and, only if you say so, operate the server it lives on.

Built for the common real-world case: the database has no public port, no VPN, only SSH. This server opens and maintains its own SSH tunnel, exposes the data as MCP tools, and refuses anything that is not a single read statement.

 MCP host                      mysql-ops-mcp                      your server
┌───────────────┐  stdio   ┌────────────────────┐   paramiko   ┌──────────────────┐
│ Claude Code   │─────────▶│ server.py          │─────────────▶│ sshd             │
│ Cursor / Codex│  JSON-RPC│  · tool whitelist  │   SSH tunnel │  └─ 127.0.0.1:3306│
│ Claude Desktop│◀─────────│  · SQL guard       │◀─────────────│     MySQL        │
└───────────────┘          └────────────────────┘              └──────────────────┘

Why

Connecting an agent to production data usually fails for one of three reasons:

1. the database is not reachable from the machine running the agent;

2. giving an LLM a database account means giving it a write account;

3. "just expose 3306" is not an answer anyone accepts.

This server solves all three: it tunnels over SSH itself (pure paramiko, no sshtunnel dependency), it validates every statement against a read-only whitelist before it reaches MySQL, and it keeps every state-changing tool unregistered unless you explicitly turn them on.

Related MCP server: MySQL MCP Server

What the agent gets

17 tools are registered by default. Everything below is read-only.

Plus 1 resource (schema) and 2 prompt templates (daily_report, health_check).

Opt-in only (MCP_ENABLE_MUTATING_TOOLS=1): docker_restart, deploy_backend_jar, deploy_frontend and the deploy_backend prompt. With the flag off they are not merely "not called" — they are not registered, so an agent cannot be talked into using them.

The eight domain tools (list_activities, activity_stats, prize_stock, …) are a worked example from a real WeChat marketing-campaign platform. They show what production-shaped tools look like — joins, aggregates, stock warnings, PII masking. Point them at your own schema or delete them; custom_query and the ops tools are generic.

Quick start

git clone https://github.com/CieveMe/mysql-ops-mcp.git
cd mysql-ops-mcp

python -m venv .venv && . .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt

cp .env.example .env    # then fill in your host / key / db

Register it with your MCP host. For Claude Code:

claude mcp add mysql-ops --env-file .env -- python /absolute/path/to/server.py

For any host that reads a JSON config (Claude Desktop, Cursor, Codex, …):

{
  "mcpServers": {
    "mysql-ops": {
      "command": "python",
      "args": ["/absolute/path/to/mysql-ops-mcp/server.py"],
      "env": {
        "MCP_SSH_HOST": "your.server.example.com",
        "MCP_SSH_USER": "deploy",
        "MCP_SSH_PEM": "/home/you/.ssh/id_ed25519",
        "MCP_DB_USER": "readonly_user",
        "MCP_DB_PASSWORD": "…",
        "MCP_DB_NAME": "your_database"
      }
    }
  }
}

Verify:

claude mcp list          # expect: mysql-ops: ✓ Connected
python -m pytest -q      # 24 passed

Configuration

Everything comes from the environment; nothing is hardcoded. See .env.example for the full list.

Safety model

Defence in depth, cheapest layer first:

1. Use a read-only database account. The SQL guard is a backstop, not the primary control.

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT ON your_database.* TO 'readonly_user'@'localhost';
FLUSH PRIVILEGES;

The tunnel connects from the server itself, so MySQL sees localhost — grant accordingly.

2. Every statement passes safety.is_read_only_sql() before it is executed:

• comments are stripped first, so SELECT 1 -- ; DROP TABLE t cannot smuggle a second statement;

• only a single statement is accepted (SELECT / WITH / SHOW / DESCRIBE / EXPLAIN);

• write keywords are rejected as whole words, so a legit column such as deleted or created_at still works;

• results are capped at MCP_MAX_ROWS.

3. Shell arguments are never interpolated raw. Container names, log paths and grep patterns are validated against strict patterns and passed through shlex.quote().

4. Secrets stay out of output. docker_inspect dumps container environment variables through a redactor, so DB_PASSWORD=… comes back as DB_PASSWORD=***.

5. Mutating tools are off by default and gated by an allowlist when on.

6. Nothing secret is committed. .gitignore covers .env, *.pem, *.key.

Example

you › how many people joined the campaign today, and is any prize about to run out?

  ⏺ mysql-ops › today_records(activity_id=3)
    { "summary": { "today_total": 128, "today_winners": 41,
                   "today_revenue": "1024.00" }, "recent": [ … ] }
  ⏺ mysql-ops › prize_stock(activity_id=3)
    [ { "name": "Bluetooth speaker", "remain_count": 2,
        "consumed_pct": "96.0%", "stock_status": "LOW" } ]

assistant › 128 entries today (41 winners, ¥1024). One prize is at
            2 units left — Bluetooth speaker, 96% consumed.

Tests

python -m pytest -q
# 24 passed

tests/test_safety.py covers the guard specifically: comment smuggling, stacked statements, INTO OUTFILE, SLEEP()-style abuse, and the false-positive cases (deleted, created_at) that a naive blacklist gets wrong.

Roadmap

• Streamable HTTP transport for teams (stdio only today)

• Config-driven domain tools so the SQL example is not lottery-specific

• A bootstrap script that creates the read-only MySQL role for you

中文说明

这是一个只读优先的 MCP Server：给 AI agent 一个查询内网 MySQL 的入口，同时默认不给任何写权限。

• 自己维护 SSH 隧道（纯 paramiko，不依赖 sshtunnel），数据库不需要开公网端口；

• 每条 SQL 先过只读校验（去注释、只允许单条只读语句、关键字整词拦截、结果行数上限）；

• 容器名 / 日志路径 / grep 关键词一律白名单校验 + shlex.quote，docker_inspect 会打码密钥；

• docker_restart、deploy_* 这类会改状态的工具默认不注册，要开必须显式设 MCP_ENABLE_MUTATING_TOOLS=1；

• 所有配置走环境变量，仓库里不含任何主机、账号、密钥。

License

MIT © 2026 Zhen He