mcp-server-mysql

English | 简体中文

A security-first MySQL MCP server. Every SQL statement must survive a full AST parse by an industrial-grade SQL parser (TiDB parser) before it can touch your database — backed by a read-only transaction fallback and a driver-level multi-statement lockout. Three independent layers of defense-in-depth: let AI query your database, without letting it walk off with your database.

Why this one?

Most MySQL MCP servers enforce "read-only" with regex/keyword matching, or by wrapping queries in a read-only transaction. Both are broken:

• Regex checks are defeated by SQL comments and creative rewriting.
• A read-only transaction alone is defeated by COMMIT; DROP TABLE ... stacked-statement injection — the exact attack Datadog demonstrated against the official Postgres reference server, which has since been archived.

This project puts the security boundary on real SQL semantic parsing instead. Every statement is parsed into an AST by the TiDB parser (MySQL 8.0-grammar compatible); anything the parser cannot understand is rejected — fail-closed, so incomplete grammar coverage can only over-block, never under-block. And because the parser sees real MySQL semantics, tricks like hiding a JOIN mysql.user inside a versioned comment /*!80000 ... */ are extracted and checked like any other table reference.

Highlights

• Independent connection profiles — one process manages multiple MySQL connections, each with its own pool, security rules, audit files, and statistics. Discover names and descriptions with list_profile, then pass an explicit profile to every SQL tool.
• Statement-class gating — SELECT / INSERT / UPDATE / DELETE / DDL are individually switchable; the default is read-only. SET, GRANT, CALL, USE, LOAD DATA, LOCK TABLES, and transaction control (BEGIN/COMMIT/ROLLBACK) are rejected unconditionally — classification itself is an allowlist, so unknown statement types land on the deny side by construction.
• Default-deny table whitelist — nothing is visible until whitelisted; patterns like db.*, db.table, app_*.logs (glob per side, case-insensitive). Every table reference is extracted from the AST: JOINs, subqueries, derived tables, CTEs (scope-aware — a CTE name can't shadow a real table to smuggle it past the check), multi-table DML, INSERT ... SELECT, and versioned comments.
• MCP table-schema resources — after the MCP connection initializes, every privilege-visible, whitelisted base table is exposed as mysql:///schema/{profile}/{database}/{table}. Reading a resource returns live SHOW CREATE TABLE SQL with only the volatile table-level AUTO_INCREMENT=N counter removed.
• Execution guardrails — hard row cap, per-query timeout, single-statement enforcement, and a tripwire for UPDATE/DELETE without WHERE.
• Built-in observability — per-query latency and row counts, slow-query flagging, and a mysql_stats tool so you can ask "which query was slowest?" right in the conversation.
• Structured audit, opt-in — JSONL with daily rotation; denied SQL is recorded with the exact rule that fired. Off by default: no log files unless you enable it.
• Atomic scripts — mysql_script runs a multi-statement script in a single transaction with every statement individually re-validated; any failure rolls back everything. DDL is banned inside scripts because MySQL's implicit commit would break atomicity.
• Query-plan analysis — mysql_explain with traditional / json / tree formats and EXPLAIN ANALYZE support.
• Interactive query results — MCP Apps-capable hosts render mysql_query as a filterable, sortable table with per-view history, selection, column controls, and TSV/CSV/JSON copy; other hosts keep receiving the original text result.
• Easy to run, small to trust — a single static Go binary with stdio by default, built on the official MCP Go SDK; the Docker image is distroless and runs as a non-root user.

Tools

All seven mysql_* tools require a non-empty profile string matching a configured name. There is no default connection: missing or unknown profiles are rejected before database access. mysql_describe_table defaults its optional database argument to the selected profile's database.

For example, call list_profile with {}, then mysql_query with {"profile":"dev","sql":"SELECT * FROM myapp.orders LIMIT 10"} or mysql_stats with {"profile":"dev","top_n":5}. Keep the same profile when inspecting, changing, and verifying data.

Resources

The server takes a table snapshot for each profile during MCP initialization/discovery and registers one direct resource per visible base table. Resource names also identify the profile, so identical database and table names on different servers stay distinct:

"Visible" is the intersection of what the profile's MySQL account can see and its security.table_whitelist. Views are not registered. Resource discovery is not capped by security.max_rows, and resource reads remain available even when select is absent from allowed_statements, because both operations execute fixed server-owned metadata SQL rather than user-submitted SQL.

The resource set is a discovery-time snapshot: a table created later appears after the next discovery or reconnect, while a dropped or newly inaccessible table returns MCP Resource Not Found. The resource content is live, so ALTER TABLE is reflected on the next read. Resource discovery and reads do not enter the audit log or mysql_stats; a discovery failure is logged with the profile name and clears only that profile's table resources; other profiles, tools, and the shared query-results App remain available.

The root-level resources.enabled switch applies to every profile. Set it to false to disable the entire resource feature. It defaults to true; when disabled, the server does not advertise Resources, register table or MCP App resources, or query any profile's MySQL during initialization/discovery. The interactive MCP App is therefore unavailable, while all eight tools, including mysql_query's text and structured results, remain available.

Quick start

1. Get the binary

Prebuilt — download the tarball for your platform (linux_amd64 / linux_arm64 / darwin_arm64) from Releases (checksums included), or install with Go:

go install github.com/Kurok1/mcp-server-mysql/cmd/mcp-server-mysql@latest

Docker — multi-arch images are published to GitHub Container Registry:

docker pull ghcr.io/kurok1/mcp-server-mysql:latest

2. Configure

Copy config.example.yaml and adjust:

mkdir -p ~/.mcp-server-mysql
cp config.example.yaml ~/.mcp-server-mysql/config.yaml

A minimal config:

resources:
  enabled: true
profiles:
  dev:
    description: Development database
    mysql:
      host: 127.0.0.1
      port: 3306
      user: mcp_dev                  # use a dedicated least-privilege account, not root
      password: ${MYSQL_MCP_PASSWORD}
      database: myapp

    security:
      allowed_statements: [select]   # add insert/update/delete/ddl only if you need them
      table_whitelist:
        - "myapp.*"

Add sibling entries under profiles for other connections; each accepts the same mysql, security, and audit fields. The annotated example includes two profiles. The client examples below use this minimal config; for other configurations, pass every referenced password environment variable to the MCP process.

3. Wire up your MCP client

Claude Code:

claude mcp add mysql --env MYSQL_MCP_PASSWORD=your-password -- \
  ~/go/bin/mcp-server-mysql --config ~/.mcp-server-mysql/config.yaml

Claude Desktop or any JSON-configured client:

{
  "mcpServers": {
    "mysql": {
      "command": "/usr/local/bin/mcp-server-mysql",
      "args": ["--config", "/Users/me/.mcp-server-mysql/config.yaml"],
      "env": { "MYSQL_MCP_PASSWORD": "..." }
    }
  }
}

Docker:

{
  "mcpServers": {
    "mysql": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-v", "/Users/me/.mcp-server-mysql:/data",
        "-e", "MYSQL_MCP_PASSWORD",
        "ghcr.io/kurok1/mcp-server-mysql:latest",
        "--config", "/data/config.yaml"
      ],
      "env": { "MYSQL_MCP_PASSWORD": "..." }
    }
  }
}

Docker note 1 — audit logs must live on a mounted volume. The container is destroyed with the session; if you enable audit logging, point audit.log_dir at the mounted volume (e.g. /data/logs) or the logs vanish with the container.

Docker note 2 — reaching MySQL on the host. On macOS/Windows set mysql.host: host.docker.internal; on Linux also append "--add-host=host.docker.internal:host-gateway" to args.

Interactive query results (MCP Apps)

mysql_query advertises the embedded ui://mcp-server-mysql/query-results resource to hosts that support MCP Apps. Successful calls include both the existing human-readable text and structured query data with a required profile field, so older or text-only hosts degrade without losing any result information. Query errors retain their text and carry the original {profile, sql} in result metadata so the App can identify the request. list_profile also returns text and structured data; the six remaining tools use text results.

The result view keeps up to 20 snapshots inside the current View, supports global and optional status filtering, natural numeric sorting, column visibility, row selection, and TSV/CSV/JSON copy. Results and history identify their profile, including failed and cancelled requests. Refresh invokes mysql_query through the host with the selected snapshot's original {profile, sql}, including after switching history; if the host does not expose tool calling to apps, the view explains that refresh is unavailable while all read-only controls continue to work. View history is memory-only and disappears when the View closes.

When overlapping requests receive a Host error or cancellation without enough information to identify the request, history marks its source as undetermined and preserves the candidate inputs. It does not assign that notification to a profile based on arrival order.

Local Basic Host development

The production/default transport remains stdio. A stateless Streamable HTTP endpoint is available specifically for local MCP Apps development:

MYSQL_MCP_PASSWORD=your-password mcp-server-mysql \
  --config ~/.mcp-server-mysql/config.yaml \
  --transport streamable-http \
  --listen 127.0.0.1:3001

Connect the official Basic Host to http://127.0.0.1:3001/mcp. The HTTP listener rejects wildcard and non-loopback addresses, and its CORS policy only accepts the Basic Host origins on local port 8080; it is not an authenticated remote deployment mode.

Security model

            MCP client  (Claude Code / Claude Desktop / …)
                               │  stdio
                               ▼
┌───────────────────────  mcp-server-mysql  ───────────────────────┐
│                                                                  │
│  Table resources: mysql:///schema/{profile}/{database}/{table}  │
│  fixed metadata SQL · base tables only · whitelist-filtered      │
│                                                                  │
│  mysql_query · mysql_execute · mysql_script · mysql_explain      │
│  mysql_list_tables · mysql_describe_table · mysql_stats          │
│  required profile → Manager.Get → independent runtime           │
│  list_profile → configured names/descriptions (no database IO)  │
│                             │                                    │
│                             ▼                                    │
│  ┌ Layer 1 · AST main gate (TiDB parser) ───────────────────┐    │
│  │ unparseable ⇒ denied (fail-closed) → single statement    │    │
│  │ → statement-class allowlist + read/write tool check      │    │
│  │ → per-class switches (default: select only)              │    │
│  │ → dangerous constructs (INTO OUTFILE / LOAD_FILE)        │    │
│  │ → missing-WHERE tripwire                                 │    │
│  │ → default-deny table whitelist (JOIN / subquery /        │    │
│  │   CTE scope-aware / versioned comments)                  │    │
│  └────────────┬───────────────────────────────┬─────────────┘    │
│               │ allowed                       │ denied           │
│               ▼                               ▼                  │
│  ┌ Layer 2 · executor ─────────────┐    DENIED [rule]: reason    │
│  │ single-stmt reads: READ ONLY tx │    is returned to the model │
│  │ row cap · query timeout         │    with the rule name       │
│  └────────────┬────────────────────┘                             │
│               ▼                                                  │
│  ┌ Layer 3 · driver ───────────────┐                             │
│  │ multiStatements=false: stacked  │                             │
│  │ injection impossible            │                             │
│  └────────────┬────────────────────┘                             │
│               │   guard decisions — allowed & denied — go to     │
│               │   audit: ring buffer (+ optional JSONL files)    │
└───────────────┬──────────────────────────────────────────────────┘
                ▼
      MySQL  —  Layer 0: dedicated least-privilege account

Layer 0 — your MySQL account (strongly recommended). Run the server with a dedicated account that has only the privileges you intend to use (read-only workloads get SELECT only). Never root. This is the containment layer everything below reinforces.

Layer 1 — the AST main gate. Every statement is parsed by the TiDB parser (parse failure ⇒ denied), then must pass, in order: single-statement enforcement → statement-class allowlist (with a read/write tool cross-check: a write sent through mysql_query is denied even if writes are enabled) → per-class enable switches → dangerous-construct scan (SELECT ... INTO OUTFILE/DUMPFILE, LOAD_FILE() at any nesting depth) → missing-WHERE tripwire → full table-reference extraction checked against the default-deny whitelist.

Layer 2 — read-only transaction fallback. Reads executed through the single-statement read path (mysql_query, mysql_explain, mysql_list_tables, mysql_describe_table) and the fixed resource metadata path run inside START TRANSACTION READ ONLY — if the parser ever misclassified a write as a read, MySQL itself rejects it. (Write statements you explicitly enabled, and everything inside mysql_script — reads included — run outside this backstop; there, Layer 1 and Layer 0 are the controls.)

Layer 3 — driver-level lockout. The connection sets multiStatements=false, so COMMIT; DROP TABLE ...-style stacked injection is impossible at the protocol level even if every layer above failed.

Every denial comes back as machine-readable text — DENIED [rule_name]: reason — and the rule names are stable:

mysql_script denials prefix the reason with the position of the offending statement: DENIED [rule]: statement N: reason.

The guard is the test suite's center of gravity: ~100 table-driven cases cover stacked-statement injection, versioned-comment smuggling, CTE-shadowing whitelist bypasses, INSERT ... SELECT table extraction, and more; end-to-end tests — whitelist enforcement, the READ ONLY backstop rejecting writes, script rollback, EXPLAIN-tree denials — run against a real MySQL 8.0 in testcontainers.

The fine print

Security documentation you can't verify is marketing. The precise boundaries:

• The read-only transaction backstop covers the single-statement read path. Write types you explicitly enable — and every statement inside mysql_script, reads included, since they share the script's read-write transaction — execute without it; there, the AST gate plus your database account privileges (Layer 0) are the controls.
• unfiltered_write is a missing-WHERE tripwire, not full-table-write prevention: UPDATE t SET a=1 WHERE 1=1 passes it. It catches mistakes, not malice.
• Fixed, non-user metadata SQL is used by mysql_list_tables and MCP resource discovery/read. Table discovery queries information_schema for base tables and filters every result through the whitelist; resource reads re-check the whitelist before SHOW CREATE TABLE. EXPLAIN FORMAT=TREE is another fixed-prefix path: the inner SELECT still passes the full guard pipeline first (the TiDB parser cannot parse FORMAT=TREE as a whole statement).
• Audit records cover SQL that reaches the guard pipeline, allowed and denied. Not audited: MCP resource discovery/reads, mysql_stats calls, mysql_describe_table pre-check denials (invalid_identifier and its table_whitelist name check), and mysql_explain parameter denials (invalid_query, not_select, invalid_format). Script auditing follows actual execution: a guard-denied script yields one record for the whole script, and statements after a failed one — validated but never executed — are not recorded.
• The MySQL connection is plain TCP — no TLS option and no Unix socket yet. Keep the server and the database on a trusted network, or tunnel the connection.

Configuration

profiles is a non-empty map. Names must match [a-z0-9][a-z0-9_-]*; description is optional and defaults to an empty string. Each profile independently applies the same defaults, without inheriting from other profiles. In the table below, mysql.*, security.*, audit.*, and description live under profiles.<name>; only resources.enabled is global.

Full annotated example: config.example.yaml. The governing principle is secure by default: omit allowed_statements and you're read-only; omit table_whitelist and everything is denied; leave block_unfiltered_writes unset and it's on.

And it fails closed at startup: an unreadable file, an unknown/misspelled key, an invalid duration, a malformed whitelist pattern, an unknown statement type, a negative script cap, or a missing mysql.user/mysql.database all abort the process — it refuses to run sick rather than degrade silently.

Secrets never need to live in the file: the whole config is passed through environment-variable expansion before parsing, so ${ENV_VAR} works in any field. The config path itself can come from the MYSQL_MCP_CONFIG environment variable instead of --config.

Migrating a single-connection config

1. Move the old root mysql, security, and audit sections intact under a name such as profiles.dev; keep resources at the root.
2. Add profile to all seven mysql_* tool calls. Use list_profile to discover available names. There is no implicit default profile, even with one connection.
3. Restart the server and reconnect clients to refresh tool schemas and the new profile-qualified Resource URIs.

Legacy root connection settings, empty/invalid profiles, and unknown configuration fields are rejected. Profiles are static until restart. Pool construction is lazy: an unreachable database fails when that profile's tools/resources access it, while list_profile still lists the configured entry. A SQL statement or script uses one profile; existing whitelist-approved cross-schema access within that connection remains available. Cross-profile transactions and runtime profile management are not supported.

Audit log

Disk logging is controlled independently by each profile's audit.enabled — default false: no log files, no log directory created. Each profile has its own in-memory statistics ring and slow-query threshold; mysql_stats reads only the requested profile's window and includes its name. Statistics are shared by callers using that profile in the process, work regardless of disk logging, and reset on restart.

When enabled, JSONL files rotate daily as audit-<profile>-<YYYY-MM-DD>.jsonl (for example, audit-dev-2026-09-15.jsonl). Profiles can share audit.log_dir without sharing files. Each line is one JSON object:

Claude Code skill

skills/mysql-mcp is a companion skill that teaches Claude to use these tools well: pick the right tool, respect the security boundaries (single statement, whitelist, WHERE tripwire), and read DENIED [rule] messages correctly instead of blindly retrying. Install:

cp -r skills/mysql-mcp ~/.claude/skills/

Compatibility

• MySQL 8.x — the E2E suite runs against MySQL 8.0 (8.0.45) via testcontainers. MySQL 5.7 and MariaDB are untested.
• MCP — Go SDK v1.7.0, with direct MySQL table-schema resources, the io.modelcontextprotocol/ui extension, and one embedded MCP App resource. Text fallback remains available to hosts without MCP Apps.
• Transport — stdio by default; loopback-only stateless Streamable HTTP is available for local development. Server identity is mcp-server-mysql; it exposes 8 tools, direct table-schema resources, one shared UI resource, and no prompts.

Development

go test ./... -short           # unit tests (no Docker needed)
go test -race ./... -short     # concurrency checks, including profile isolation
go test ./... -timeout 600s    # full suite incl. testcontainers integration/E2E (needs Docker)

cd ui/query-results
npm ci
npm run typecheck
npm test
npm run build                  # rebuilds the committed internal/ui/query-results.html
npm run test:sites

The frontend uses React/TypeScript, @modelcontextprotocol/ext-apps, and vite-plugin-singlefile. Its build emits one fully inlined HTML file and copies it to internal/ui/query-results.html, which Go embeds into the binary; a normal go build therefore does not require Node. The Dockerfile rebuilds the frontend in a Node stage and overwrites that committed bundle before compiling Go, preventing stale UI in release images.

Design docs live in docs/superpowers — each feature ships with a spec and an implementation plan.

License

Apache-2.0