FFFF GitHub - dezer32/mcp-mysql · GitHub
[go: up one dir, main page]

Skip to content
dezer32Public

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

28 Commits

Folders and files

Repository files navigation

mcp-mysql

A Go implementation of an MCP (Model Context Protocol) server that gives LLM clients managed, safety-first access to MySQL databases. It exposes a single mysql_query tool for SQL execution and two introspection resources (mysql://tables, mysql://tables/{name}).

Features

  • Read-only by default. Write operations (INSERT, UPDATE, DELETE, DDL) are disabled unless explicitly enabled via environment variables.
  • Two-level write permissions. Global flags (ALLOW_INSERT_OPERATION etc.) combined with per-schema maps (SCHEMA_INSERT_PERMISSIONS etc.); an explicit per-schema rule always wins over the global flag.
  • PII redaction. Column-level masking and introspection filtering; configurable column name substrings and regex patterns.
  • Single-DB and multi-DB modes. Leave MYSQL_DB unset to operate across multiple schemas; the target schema is derived from the SQL itself (db.table or USE).
  • Two transports. Local stdio (default) and stateless remote HTTP with Bearer authentication.

Requirements

  • Go 1.25 or later
  • MySQL 8.0 or compatible

Installation / Build

# Build the binary
go build -o mcp-mysql ./cmd/mcp-mysql

# Or using make
make build

Environment Variables

All configuration is read once at process startup and never reloaded.

Boolean semantics. Unless noted otherwise, a boolean variable is true only when its value is the exact string "true". Any other value (including "1", "TRUE", "yes", empty string, or unset) is treated as false. Inverse-boolean variables are noted explicitly.

Connection

Variable Type Default Description
MYSQL_CONNECTION_STRING string — MySQL CLI-style connection string (mysql -h... -P... -u... -p... db). Takes priority over the discrete variables below for host / port / user / password / database / socket.
MYSQL_HOST string 127.0.0.1 TCP host (ignored when a socket path is set). Empty string resolves to the default.
MYSQL_PORT number 3306 TCP port. Empty string resolves to 3306.
MYSQL_USER string root Database user. Empty string resolves to root.
MYSQL_PASS string "" Password. Both unset and empty resolve to an empty string.
MYSQL_DB string — Target database. Empty / unset activates multi-DB mode.
MYSQL_SOCKET_PATH string — Path to a Unix socket. Takes priority over TCP when non-empty.
MYSQL_QUEUE_LIMIT number 100 Connection-pool queue depth.
MYSQL_CONNECT_TIMEOUT number (ms) 10000 Connection establishment timeout in milliseconds.
MYSQL_BIG_NUMBER_STRINGS boolean false Return BIGINT/DECIMAL columns as strings instead of numbers.
MYSQL_TIMEZONE string — Session timezone. Only applied when non-empty.
MYSQL_DATE_STRINGS boolean false Return date/datetime columns as strings.

TLS / SSL

Variable Type Default Description
MYSQL_SSL boolean false Master switch for the TLS block. TLS is only configured when this is "true".
MYSQL_SSL_REJECT_UNAUTHORIZED boolean true (deviation) Reject connections to MySQL servers with unverified certificates. See deviations.
MYSQL_SSL_CA string (path) — Path to a PEM CA certificate.
MYSQL_SSL_CERT string (path) — Path to a PEM client certificate (mTLS).
MYSQL_SSL_KEY string (path) — Path to a PEM client key (mTLS).

Write Permissions

Read operations are always allowed. All write categories are disabled by default.

Variable Type Default Description
ALLOW_INSERT_OPERATION boolean false Global permission for INSERT.
ALLOW_UPDATE_OPERATION boolean false Global permission for UPDATE.
ALLOW_DELETE_OPERATION boolean false Global permission for DELETE.
ALLOW_DDL_OPERATION boolean false Global permission for DDL (CREATE, ALTER, DROP, TRUNCATE).
SCHEMA_INSERT_PERMISSIONS map schema:bool,... {} Per-schema INSERT overrides, e.g. dev:true,prod:false. An explicit rule always wins over the global flag.
SCHEMA_UPDATE_PERMISSIONS map schema:bool,... {} Per-schema UPDATE overrides.
SCHEMA_DELETE_PERMISSIONS map schema:bool,... {} Per-schema DELETE overrides.
SCHEMA_DDL_PERMISSIONS map schema:bool,... {} Per-schema DDL overrides.
MULTI_DB_WRITE_MODE boolean false In multi-DB mode: gates the global ALLOW_* flags. Per-schema rules always win regardless of this setting. See deviations.
MYSQL_DISABLE_READ_ONLY_TRANSACTIONS boolean false Disables wrapping read queries in a SET SESSION TRANSACTION READ ONLY guard. Required for DDL execution.

Permission decision tree (for each write category X):

Query contains category X?
├─ No  → no permission check (treated as read)
└─ Yes → determine target schema S from SQL
          ├─ S unknown → ALLOW_X_OPERATION ? permit : deny
          └─ S known   → S has key in SCHEMA_X_PERMISSIONS?
                         ├─ yes → use map value (true → permit, false → deny)
                         └─ no  → ALLOW_X_OPERATION ? permit : deny

PII Redaction

Variable Type Default Description
ENABLE_PII_REDACTION boolean false Master switch for all PII mechanisms.
PII_EXTRA_COLUMNS comma-separated list [] Additional column-name substrings to treat as PII (trimmed, lowercased, blanks dropped).
PII_EXTRA_COLUMN_PATTERNS semicolon-separated list [] Additional regex patterns for column names (case-insensitive). Invalid patterns are skipped with a log entry.
PII_REDACT_JSON_STRINGS inverse boolean true Redact PII inside JSON string values. Disabled only by the exact string "false".
PII_ALLOW_SELECT_STAR boolean false Disable the SELECT * guard when PII redaction is active.
PII_ALLOW_REFERENCES boolean false Disable the PII-column reference guard.
PII_ALLOW_INTROSPECTION boolean false Bypass all introspection PII logic (highest priority).
PII_BLOCK_INTROSPECTION boolean false Block all introspection (ignored when PII_ALLOW_INTROSPECTION is set).

Transport

Variable Type Default Description
IS_REMOTE_MCP boolean false Enable remote HTTP mode. Both this and a non-empty REMOTE_SECRET_KEY must be set for the HTTP server to start; otherwise stdio is used.
REMOTE_SECRET_KEY string "" Bearer token secret. Must be non-empty to enter remote mode.
PORT string | number 3000 HTTP server port (remote mode only).

Logging

Variable Type Default Description
ENABLE_LOGGING string disabled Enable the logger when set to "true" or "1".

Note: The variable name MYSQL_ENABLE_LOGGING appears in some documentation but is a typo. The variable actually read at runtime is ENABLE_LOGGING.

Query Timeout (Deviation from Appendix B)

Variable Type Default Description
MYSQL_QUERY_TIMEOUT number (ms) 0 Per-query context deadline in milliseconds. 0 or empty means no deadline. See deviations.

Modes

stdio vs. Remote HTTP

By default the server runs in stdio mode: the MCP client launches the process and communicates over its stdin/stdout.

Remote HTTP mode is activated when both conditions are met:

  1. IS_REMOTE_MCP=true
  2. REMOTE_SECRET_KEY is a non-empty string

The server then listens on PORT (default 3000) and handles POST /mcp requests authenticated by an Authorization: Bearer <REMOTE_SECRET_KEY> header. The transport is stateless: each POST /mcp spawns an independent MCP server + transport instance, handles one JSON-RPC request, then tears both down. GET /mcp and DELETE /mcp return HTTP 405.

Read-only vs. Write

All four write categories (INSERT, UPDATE, DELETE, DDL) are blocked by default. Enable them individually with ALLOW_*_OPERATION=true or with per-schema SCHEMA_*_PERMISSIONS maps.

The MCP tool annotation readOnlyHint reflects the effective setting: it is true only when all four global ALLOW_* flags are false.

Single-DB vs. Multi-DB

Set MYSQL_DB to a database name for single-DB mode: all queries run against that database.

Leave MYSQL_DB empty or unset for multi-DB mode: the target schema is extracted from each query (db.table syntax or a USE statement). Writes require a determinable target schema; queries with an unknown schema fall back to the global ALLOW_* flags (or are denied if those flags are off and MULTI_DB_WRITE_MODE is not "true").

Running the Server

Stdio (default)

export MYSQL_HOST=127.0.0.1
export MYSQL_PORT=3306
export MYSQL_USER=root
export MYSQL_PASS=secret
export MYSQL_DB=myapp
./mcp-mysql

Remote HTTP mode

export IS_REMOTE_MCP=true
export REMOTE_SECRET_KEY=my-long-secret
export PORT=8080
export MYSQL_HOST=127.0.0.1
export MYSQL_DB=myapp
./mcp-mysql
# → MCP Stateless Streamable HTTP Server listening on port 8080

Make a query:

curl -X POST http://127.0.0.1:8080/mcp \
  -H "Authorization: Bearer my-long-secret" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"mysql_query","arguments":{"sql":"SELECT 1 AS x"}}}'

Docker

A docker-compose.yml is included for running a local MySQL instance:

# Start MySQL in the background
docker compose up -d mysql

# Connect the server to it
MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASS=root MYSQL_DB=mcp_test_db \
  ./mcp-mysql

MCP Client Configuration

Claude Desktop

Add to ~/Library/Application Support/Claude/claude_desktop_config.json:

{
  "mcpServers": {
    "mysql": {
      "command": "/path/to/mcp-mysql",
      "args": [],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "secret",
        "MYSQL_DB": "myapp"
      }
    }
  }
}

Claude Code

Add to .mcp.json in your project root (or ~/.claude/mcp.json for global config):

{
  "mcpServers": {
    "mysql": {
      "command": "/path/to/mcp-mysql",
      "args": [],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "secret",
        "MYSQL_DB": "myapp"
      }
    }
  }
}

For remote HTTP mode, most clients that support custom HTTP MCP servers can be pointed at http://<host>:<PORT>/mcp with Authorization: Bearer <REMOTE_SECRET_KEY>.

Clean-Implementation Deviations

This implementation intentionally diverges from the original in the following places. Each deviation is documented here rather than silently present in the code.

1. HTTP Bearer auth uses constant-time full-token comparison. The original uses a suffix check (endsWith) that accepts any header ending with the secret, including Bearer foo<SECRET>. This implementation performs a constant-time comparison of the full extracted token (the part after Bearer ) against REMOTE_SECRET_KEY, which closes the trivial bypass without changing legitimate client behavior.

2. MYSQL_SSL_REJECT_UNAUTHORIZED defaults to true. The original defaults this to false (accept unverified certificates). This implementation defaults to true (reject unverified certificates) as the safer choice. To replicate the original's insecure default, set MYSQL_SSL_REJECT_UNAUTHORIZED=false explicitly.

3. Server/tool version uses a real build version or "dev". The original falls back to the text "[vundefined]" when npm_package_version is unset. This implementation embeds a build-time version (via -ldflags) or reports "dev" when built without version injection; the broken placeholder string is never emitted.

4. MULTI_DB_WRITE_MODE gates the global ALLOW_* flags in multi-DB mode. In the original the variable is read but has no effect on permissions; the ALLOW_* flags work regardless. This implementation makes MULTI_DB_WRITE_MODE=true a prerequisite for the global write flags to take effect in multi-DB mode. Per-schema SCHEMA_*_PERMISSIONS rules always win regardless of MULTI_DB_WRITE_MODE.

5. Resource URIs in multi-DB mode use qualified schema.table segments. The spec (§IV.7) defines the single-DB canonical form as mysql://tables/{tableName}. In multi-DB mode this server qualifies the segment as mysql://tables/{schema}.{table} to avoid collisions when the same table name exists in multiple schemas. This is an intentional multi-DB extension of the spec, not contract drift: single-DB clients are unaffected.

6. Multi-statement queries run as a safe sequential batch on one connection/transaction. The multipleStatements DSN flag (which sends multiple statements in a single network round-trip and is known to be prone to injection) is not enabled. Instead, multi-statement input is split and each statement is executed sequentially on the same connection within the current transaction context.

7. MYSQL_QUERY_TIMEOUT maps to a per-query context deadline. Appendix B of the spec advised against adding a query timeout. This implementation provides one via MYSQL_QUERY_TIMEOUT (milliseconds). When set to 0 or left empty the deadline is disabled and behavior matches the spec's original recommendation. Setting a non-zero value deviates from Appendix B; at 0 there is no effect.

Reserved / Not-Yet-Implemented Variables

The following variables are recognized (present in .env.example) but either not yet fully wired or explicitly reserved as no-ops in the current release:

Variable Status
MYSQL_POOL_SIZE Wired: sets the connection pool size. Default 10.
MYSQL_RATE_LIMIT Reserved — no-op.
MYSQL_CACHE_TTL Reserved — no-op.
MYSQL_MAX_QUERY_COMPLEXITY Reserved — no-op.
MYSQL_LOG_LEVEL Reserved — no-op.
MYSQL_METRICS_ENABLED Reserved — no-op.

MYSQL_ENABLE_LOGGING is a documentation typo in the original. The variable actually read is ENABLE_LOGGING.

Bug Incompatibilities with the Original

This server deliberately fixes known bugs in the original @benborla29/mcp-server-mysql. Where the original's behavior was itself a bug, this server will behave differently:

  • Bearer auth accepts only a correctly-formed Bearer <token> header (not a suffix match).
  • TLS defaults to rejecting unverified certificates (not accepting them).
  • The version string is a valid semver or "dev" (not "[vundefined]").
  • MULTI_DB_WRITE_MODE actually gates writes in multi-DB mode (not ignored).
  • Resource URIs in multi-DB mode are schema-qualified (no silent name collisions).

If you need exact bug-for-bug compatibility with the original, refer to the spec's "Appendix A: Pitfalls" and set the relevant override variables accordingly.

Known Limitations

  • Table/schema names containing a literal backtick. Such a table appears in resources/list with a backtick-escaped URI, but it is not readable via resources/read: the RFC 6570 resource template does not match a URI segment containing a backtick, so the read returns JSON-RPC -32002 (resource not found). Names containing dots (the common multi-DB disambiguation case) work normally. Backticks in identifier names are rare; inspect such a table with the mysql_query tool against information_schema instead (subject to PII policy).
  • Statement-type token for a non-first DESC/DESCRIBE/EXPLAIN <table> in a multi-statement query is internally explain/show_columns rather than desc/describe. This has no observable effect — both land in the read-only permission category and the same PII-filter bucket — so query authorization and redaction are unaffected.

Development and Testing

# Run unit tests
make test

# Run tests with race detector
make test-race

# Vet
make vet

Running end-to-end tests

The end-to-end suite (files tagged //go:build e2e) drives the compiled server binary against a live MySQL 8.4 (via docker-compose) and is skipped by a normal go test ./.... The suite spawns the binary from the module root (./mcp-mysql), so build it first:

make build         # → ./mcp-mysql (the binary the e2e suite spawns)
make e2e           # docker compose up -d mysql, then: go test -tags e2e ./test/e2e/...
make down          # docker compose down -v  (tear down when finished)

Or step by step:

make up                                      # MySQL 8.4 on host port 3306 (docker-compose)
go build -o mcp-mysql ./cmd/mcp-mysql
go test -tags e2e ./test/e2e/... -v          # e2e tests connect to MYSQL_PORT (default 3306)
make down

If host port 3306 is already in use, override the compose host port and the suite's target port together — docker-compose.yml maps ${MYSQL_HOST_PORT:-3306}:3306 and the suite reads MYSQL_PORT:

MYSQL_HOST_PORT=3307 MYSQL_PORT=3307 make build e2e
make down

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages

0