mysql-mcp-server

# MySQL MCP Server - Copilot Instructions

## Project Overview

This is the **MySQL MCP Server**, a Model Context Protocol (MCP) server that gives
AI tools a safe, read-only window into a running MySQL 8 instance. It is
built for incident response and day-to-day database troubleshooting: sessions,
locks, transactions, slow queries, InnoDB internals, schema, and replication.

**Key details:**
- **Language:** Go 1.25+ (single module, no external build system beyond the Makefile)
- **Module:** `github.com/jakubpliszka/mysql-mcp-server`
- **Type:** stdio MCP server with a flag/env driven CLI
- **Transport:** MCP over stdio (`mcp.StdioTransport`); the client closing stdin is a normal shutdown
- **Entry point:** `cmd/mysql-mcp-server/main.go`
- **MCP framework:** `github.com/modelcontextprotocol/go-sdk` (`v1.6.1`)
- **DB driver:** `github.com/go-sql-driver/mysql` (`v1.9.3`)
- **SSH tunnel:** `golang.org/x/crypto/ssh`
- **Targets MySQL 8.0.22+ only.** MariaDB and MySQL 5.7 are rejected at connect (see `ensureSupported` in `connector.go`). Tools assume modern syntax and gate 8.4-only features on the detected version.

**The audience is a database engineer during an incident.** Tool output should be
calm, accurate, and easy to scan. Prefer investigation and diagnosis over change.
Never add tools or behavior that mutate data or server state unless they are
clearly gated and opt-in.

## The Two Safety Rules (read this before touching anything)

These are the load-bearing invariants of the whole project. Most code review
feedback here is about one of them. Do not weaken either one.

1. **Read-only by default.** The generic `mysql_query` / `mysql_explain` tools
   only accept `SELECT`/`SHOW`/`EXPLAIN`/`DESCRIBE`/`WITH`/`USE` (plus MySQL 8
   `TABLE` / `VALUES`). Enforced by `mysqldb.EnsureReadOnly`, which strips
   comments and quoted literals first, requires a single leading read-only verb,
   rejects stacked statements (embedded `;`), and blocks `INTO OUTFILE` /
   `INTO DUMPFILE`. The driver is also hardened: `MultiStatements=false` and
   `InterpolateParams=false`.

2. **System schemas only, by default.** The server must never read user or
   application data. It may only touch `information_schema`, `performance_schema`,
   `mysql`, and `sys`. Enforced by `mysqldb.EnsureSystemSchemaOnly` (a
   conservative table-reference scanner) for the arbitrary-SQL tools, and live
   statement text from other tools is redacted with `mysqldb.MaskSQLLiterals`.
   The escape hatch is `--allow-user-data` / `MYSQL_ALLOW_USER_DATA`, off by
   default.

The impactful `mysql_kill` tool is a third, separate gate: it is only registered
when `--allow-kill` / `MYSQL_ALLOW_KILL` is set, and is annotated destructive.

When you add or change any tool, ask: does it issue SQL? If so, does it stay
inside the system schemas, and does any statement text it echoes get masked?

## Build & Validation

There is no codegen, no snapshot system, and no linter beyond `gofmt` + `go vet`.
The whole cycle is fast (about a second each). The Makefile is the source of truth.

**Run this before every commit (it is the same as `make all`):**

```sh
make fmt-check   # gofmt -l . must be empty
make vet         # go vet ./...
make test        # go test ./...
make build       # go build -o mysql-mcp-server ./cmd/mysql-mcp-server
```

Other targets:

```sh
make fmt         # gofmt -w .   (auto-format; run this if fmt-check fails)
make run         # go run ./cmd/mysql-mcp-server
make tidy        # go mod tidy
make clean       # remove the built binary
```

Useful direct commands:

```sh
go test ./internal/mysqldb -run TestEnsureSystemSchemaOnly -v
go build -o /tmp/mysql-mcp-server ./cmd/mysql-mcp-server
```

**gofmt gotcha:** struct field and map-literal alignment is gofmt-sensitive. After
editing config structs or table-driven test cases, run `make fmt` before
`fmt-check` or CI-style checks will flag the alignment.

## Project Structure

```
.
├── cmd/
│   └── mysql-mcp-server/
│       └── main.go              # CLI entry, server wiring, the `instructions` const
├── internal/
│   ├── config/
│   │   ├── config.go            # all flags + env vars, validation, ConnectionSummary
│   │   └── config_test.go
│   ├── mysqldb/                 # connection, execution, rendering, SAFETY
│   │   ├── connector.go         # Connect (direct/socket/SSH), version + capability detection, guardrail DSN
│   │   ├── capabilities.go      # privilege probing (PROCESS, REPLICATION CLIENT, perf_schema, sys)
│   │   ├── ssh.go               # SSH tunnel dialer + host key handling
│   │   ├── result.go            # Result type, Query/QueryOnSchema, RenderTable/RenderVertical
│   │   ├── result_test.go
│   │   ├── safety.go            # EnsureReadOnly, EnsureSystemSchemaOnly, MaskSQLLiterals
│   │   └── safety_test.go
│   └── tools/                   # one file per diagnostic domain
│       ├── registry.go          # Registry, RegisterAll (returns error), shared helpers
│       ├── toolsets.go          # toolset membership map, selection, addTool gating, StartupSummary
│       ├── prompts.go           # investigation runbooks registered as MCP prompts
│       ├── health.go            # ping, server_status, health_report, show_variables, show_status
│       ├── process.go           # processlist, kill (gated)
│       ├── locks.go             # lock_waits, transactions, metadata_locks
│       ├── innodb.go            # innodb_status
│       ├── performance.go       # top_queries, table_scans, index_usage
│       ├── schema.go            # databases, tables, describe_table, largest_tables
│       ├── replication.go       # replication_status
│       ├── query.go             # query, explain (the two arbitrary-SQL tools)
│       └── util.go              # formatting helpers (bytes, durations, sections, kv lines)
├── Makefile
├── README.md
├── go.mod / go.sum
└── .github/copilot-instructions.md
```

**Layering:** `config` has no internal deps. `mysqldb` depends on `config`.
`tools` depends on both. `main` wires them together. Keep this direction;
do not import `tools` from `mysqldb`.

## The Tools (21 total)

All are read-only except `mysql_kill`. Tool names are the public API surface, so
renames are breaking changes for clients.

Every tool belongs to a **toolset** (`toolsets.go`) and may declare the MySQL
**capabilities** it needs. At registration `addTool` skips a tool when its toolset
is not selected, or (when `--privilege-filtering` is on) when the connected user
lacks a required capability. So of the 21 tools, a default root connection
registers 20 (everything but the `--allow-kill` gated `mysql_kill`), and a
low-privilege user may see fewer. The startup banner reports what was exposed.

**Connectivity & health** (`health.go`)
- `mysql_ping`, `mysql_server_status`, `mysql_health_report`,
  `mysql_show_variables`, `mysql_show_status`

**Sessions, locks & transactions** (`process.go`, `locks.go`, `innodb.go`)
- `mysql_processlist`, `mysql_lock_waits`, `mysql_transactions`,
  `mysql_metadata_locks`, `mysql_innodb_status`, `mysql_kill` *(gated by `--allow-kill`)*

**Queries & performance** (`query.go`, `performance.go`)
- `mysql_query`, `mysql_explain`, `mysql_top_queries`, `mysql_table_scans`,
  `mysql_index_usage`

**Schema** (`schema.go`)
- `mysql_databases`, `mysql_tables`, `mysql_describe_table`, `mysql_largest_tables`

**Replication** (`replication.go`)
- `mysql_replication_status`

## How a Tool Is Built (the pattern to copy)

Every domain file follows the same shape. To add a tool, mirror an existing one
(`schema.go` is a clean example).

1. **Define a typed args struct** with `json` and `jsonschema` tags. Omit it and
   use the shared `noArgs` if the tool takes no parameters.

   ```go
   type tablesArgs struct {
       Schema string `json:"schema,omitempty" jsonschema:"database to list tables for (defaults to the connection's database)"`
       Limit  int    `json:"limit,omitempty" jsonschema:"maximum rows to return (default 100)"`
   }
   ```

2. **Register it** in a `registerXxxTools(s *mcp.Server)` method via the
   `addTool` helper (not `mcp.AddTool` directly), with a clear `Description`, the
   right annotation (`readOnly()` or, only for `mysql_kill`, `destructive()`), and
   any MySQL capabilities it needs as trailing args:

   ```go
   addTool(r, s, &mcp.Tool{Name: "mysql_innodb_status", Description: ..., Annotations: readOnly()},
       r.handleInnoDBStatus, mysqldb.CapProcess)
   ```

   `addTool` handles toolset selection and privilege filtering for you. It also
   wraps the handler so that an access-denied error at call time is returned with
   the raw MySQL error plus a short hint naming the capability the tool needs
   (see `annotateAccessDenied`/`capRequirement`), which is why the capability
   arguments matter even when filtering is off.

3. **Add the tool to the `toolsets` membership map** in `toolsets.go`. This map is
   the single source of truth, and `TestMembershipMatchesRegistration` fails if a
   registered tool is missing from it, so update both together.

4. **Wire registration into `RegisterAll`** in `registry.go` if you added a new
   domain file.

5. **Implement `handleXxx`** as a `*Registry` method with the signature
   `func(ctx, *mcp.CallToolRequest, argsT) (*mcp.CallToolResult, any, error)`.
   - Derive a bounded context with `r.queryCtx(ctx)` and always `defer cancel()`.
   - Execute through `r.DB.Query` / `r.DB.QueryOnSchema` (respect `r.Cfg.MaxRows`).
   - Render with `mysqldb.RenderTable` or `RenderVertical`, append `res.Footer()`,
     return via the `text(...)` helper. For multi-section reports, use `renderQuery`
     and the `section` / `kvLine` helpers from `util.go`.

6. **If the tool echoes live statement text** (anything from `processlist`,
   `innodb_trx`, lock waits, or replication errors), mask it with
   `maskColumns(res, "col", ...)` when `!r.Cfg.AllowUserData`. See `process.go`,
   `locks.go`, and `replication.go`.

7. **If the tool runs caller-supplied SQL**, it MUST call `EnsureReadOnly` and,
   when `!r.Cfg.AllowUserData`, compute `RestrictedDefaultSchema` and call
   `EnsureSystemSchemaOnly` before executing. See `query.go`.

## Version Awareness

The server enforces a minimum of **MySQL 8.0.22** at connect (`ensureSupported`
in `connector.go`), so every tool can assume 8.0.22+ features: SHOW REPLICA
STATUS, the `Replica_*`/`Source_*` status columns, and
`performance_schema.data_lock_waits`. There is no MariaDB or 5.7 branching.

The connected server is described by `r.DB.Version` (`Major`, `Minor`, `Patch`,
`Raw`).

- Use `r.DB.Version.AtLeast(major, minor, patch)` only to gate features newer
  than the 8.0.22 floor, e.g. `AtLeast(8, 4, 0)` for `SHOW BINARY LOG STATUS`
  (which replaced `SHOW MASTER STATUS` in 8.4); see `primaryStatusResult` in
  `replication.go`.
- Do not reintroduce SLAVE/MASTER naming or MariaDB fallbacks.

When a feature is missing, return a clear, friendly message rather than a raw
driver error.

## Configuration

All settings come from flags or env vars (flags win, then env, then defaults),
parsed in `internal/config/config.go`. When you add a setting, add the field, the
`fs.XxxVar` line with the `[ENV_NAME]` suffix in the usage string, a default, any
validation in `normalizeAndValidate`, and the matching entry in `config_test.go`'s
`clearEnv` list. Document it in the README table too.

| Concern | Env | Flag | Default |
| --- | --- | --- | --- |
| Connection | `MYSQL_HOST` `MYSQL_PORT` `MYSQL_USER` `MYSQL_PASSWORD` `MYSQL_DATABASE` `MYSQL_SOCKET` `MYSQL_TLS` | `--host` etc. | `127.0.0.1:3306` |
| SSH tunnel | `MYSQL_SSH_HOST` `MYSQL_SSH_PORT` `MYSQL_SSH_USER` `MYSQL_SSH_PASSWORD` `MYSQL_SSH_KEY` `MYSQL_SSH_KEY_PASSPHRASE` `MYSQL_SSH_KNOWN_HOSTS` `MYSQL_SSH_INSECURE_HOST_KEY` `MYSQL_SSH_AGENT` | `--ssh-*` | off unless `ssh-host` set |
| Read-only guard | `MYSQL_READONLY` | `--readonly` | `true` |
| **User-data access** | `MYSQL_ALLOW_USER_DATA` | `--allow-user-data` | `false` |
| Kill tool | `MYSQL_ALLOW_KILL` | `--allow-kill` | `false` |
| Limits | `MYSQL_MAX_ROWS` `MYSQL_QUERY_TIMEOUT` `MYSQL_CONNECT_TIMEOUT` | `--max-rows` etc. | `500` / `30s` / `10s` |
| Tool exposure | `MYSQL_TOOLSETS` `MYSQL_TOOLS` | `--toolsets` `--tools` | all toolsets, no extra tools |
| Privilege filtering | `MYSQL_PRIVILEGE_FILTERING` | `--privilege-filtering` | `true` |
| Session guardrails | `MYSQL_SESSION_GUARDRAILS` `MYSQL_LOCK_WAIT_TIMEOUT` | `--session-guardrails` `--lock-wait-timeout` | `true` / `10s` |
| Prompts | `MYSQL_PROMPTS` | `--prompts` | `true` |

Setting `MYSQL_SSH_HOST` enables the tunnel; the MySQL host/port are then resolved
from the SSH server's perspective.

## Testing Guidelines

- **Standard library `testing` only.** No testify, no mocks framework. Table-driven
  tests with plain `t.Errorf` / `t.Fatalf`, matching the existing style.
- Tests live in `*_test.go` next to the code, in the same package (white-box).
- **The safety layer is the highest-value test target.** Any change to
  `EnsureReadOnly`, `EnsureSystemSchemaOnly`, `RestrictedDefaultSchema`, or
  `MaskSQLLiterals` needs allow-and-reject cases, including the sneaky ones:
  backtick-quoted identifiers, comma-separated FROM lists, subqueries in
  SELECT/WHERE, table functions, and literals that look like table names.
- Most `tools` handlers are not unit-tested (they need a live DB). Validate them
  with a live smoke test instead (below). Keep pure logic in testable helpers.
- **Toolsets, privilege filtering, guardrails, and prompts are unit-tested**
  without a DB: see `toolsets_test.go`, `prompts_test.go`,
  `mysqldb/capabilities_test.go`, and `mysqldb/connector_test.go`. The
  privilege-filtering and guardrail behavior against a real server lives in
  `integration_features_test.go` (env-gated, see below).

### Live smoke testing

A real server is the only way to validate tool behavior end to end. The pattern
used during development:

1. Build the binary: `go build -o /tmp/mysql-mcp-server ./cmd/mysql-mcp-server`
2. Point it at a throwaway MySQL (e.g. a local Docker container) via `MYSQL_*` env.
3. Drive it over stdio with a tiny JSON-RPC client: `initialize`, then
   `notifications/initialized`, then `tools/call`. Confirm allowed queries return
   rows and disallowed ones come back as tool errors with the expected message.
4. To verify masking, run a query holding a recognizable literal in another
   session and check it shows as `?` in `mysql_processlist`.

Never point a destructive or `--allow-kill` configuration at anything you care
about.

## Code Style

- **`gofmt` is mandatory** (`make fmt`); `go vet` must be clean. These are the
  only enforced gates, so keep them green.
- Standard Go conventions: acronyms stay capitalized (`ID`, `SQL`, `DB`, `TLS`,
  `SSH`, `IO`), errors wrapped with `%w`, small focused functions.
- **Comment sparingly.** Explain *why*, not *what*; let the code speak. Short
  inline comments are lowercase and do not end with a period.
- **No em dashes and no AI-style punctuation** in comments, descriptions, or docs.
  Write plainly, the way an on-call engineer would.
- Tool `Description` strings are user-facing: state what the tool does, its key
  arguments, and any version requirement, in one or two sentences.
- SQL lives in `const` blocks or clearly named locals; keep column aliases stable
  because they are what `maskColumns` and renderers key on.

## Common Tasks

**Add a diagnostic tool** → pick or create the domain file, follow the seven-step
pattern above (register with `addTool`, declaring any capabilities), add it to the
`toolsets` map in `toolsets.go`, wire it into `RegisterAll`, write safety-relevant
tests, update the README tool list and (if user-facing behavior changed) the
`instructions` const in `main.go`, then `make all`.

**Add a runbook prompt** → append a `runbook` to the `runbooks` slice in
`prompts.go` (unique `name`, concrete tool references in the body), extend
`TestRunbooksContent`, and mention it in the README prompts list.

**Add a config flag** → see Configuration above; touch `config.go`, `config_test.go`,
and the README table.

**Change safety behavior** → edit `safety.go`, expand `safety_test.go` with new
allow/reject cases, run a live smoke test proving both the allowed and blocked
paths, and update the README "Data access policy" section plus the `main.go`
instructions if the policy wording changes.

**Touch connection logic** → `connector.go` / `ssh.go`. Preserve the three modes
(TCP, unix socket, SSH tunnel), keep the pool limits, and keep `ConnectionSummary`
secret-free since it is logged to stderr at startup.

## Important Reminders

1. **Never weaken the two safety rules.** Read-only and system-schemas-only are
   the point of this server. New SQL paths must respect both.
2. **Live statement text is sensitive.** If a curated tool surfaces a running
   query or replication error, mask it unless `AllowUserData` is set.
3. **Impactful actions stay opt-in.** Anything that mutates state must be gated
   like `mysql_kill` and annotated `destructive()`.
4. **Run `make all` before committing.** fmt-check, vet, test, build are all fast.
5. **Run `make fmt` after editing structs or table-driven tests** to satisfy
   gofmt alignment.
6. **Tool names are API.** Renaming a tool breaks clients; treat it as a breaking
   change.
7. **Assume MySQL 8.0.22+.** The connect path rejects older MySQL and MariaDB, so
   write modern syntax and only gate features newer than 8.0.22 with
   `Version.AtLeast` (e.g. 8.4's `SHOW BINARY LOG STATUS`).
8. **Update the README and the `main.go` instructions** when you change tools or
   policy. They are the human- and agent-facing docs.
9. **Prefer investigation over change** in both the product and your own edits:
   make the smallest correct change, and explain risk before suggesting anything
   impactful.
10. **Keep the `toolsets` map in sync.** Every registered tool must appear in
    exactly one toolset, and a tool's declared capabilities must match the SQL it
    runs. `TestMembershipMatchesRegistration` guards the first invariant; privilege
    filtering depends on the second.