Mysql Readonly

A Python MCP (Model Context Protocol) server that provides readonly access to a MySQL database.

• Chinese documentation: README.zh-CN.md

• Transport: stdio (recommended for MCP clients) and HTTP/SSE (standalone service)

• Safety: Only SELECT, SHOW, DESCRIBE, DESC, and EXPLAIN statements are permitted

• Config: stdio — MySQL settings via mcp.json env; SSE — environment at process start, optional MCP_BEARER_TOKEN for HTTP auth

• Table blacklist: QUERY_TABLE_BLACKLIST blocks data access via query; describe_table still works for schema on listed tables

---

Requirements

• Installer and MCP runtime: Python 3.10+. install.py checks the interpreter that runs it before any other step; if the version is too low, it exits immediately (use python3.12 install.py, py -3.12 install.py, etc.).
• Optional: install.py --python /path/to/python3.12 creates .venv with that binary instead of sys.executable (that binary must also be 3.10+).
• A reachable MySQL instance.

Installation

First-time install (recommended)

Run install.py with a 3.10+ interpreter (python, python3, or py -3).

The script creates .venv with python -m venv, then python -m pip install -r requirements.txt.

After a successful install, an interactive wizard (if stdin is a TTY) asks for: transport (stdio or sse), core MySQL fields, QUERY_TABLE_BLACKLIST (always), optional timeouts / QUERY_DEFAULT_LIMIT / TLS paths, and for SSE MCP_HOST / MCP_PORT / MCP_BEARER_TOKEN. It prints a complete mcp.json snippet (and for SSE, shell export lines plus the server command). Use --no-wizard to skip (CI / automation). Non-interactive stdin skips the wizard automatically.

Windows (CMD or double-click in Explorer):

cd \path\to\MySQL_MCP
install.bat

macOS / Linux:

cd /path/to/MySQL_MCP
python3 install.py

Useful flags:

Flag	Meaning
--recreate	Delete .venv and reinstall
--dry-run	Show the planned venv/pip steps only (still requires 3.10+ to run the script)
--no-wizard	Do not run the post-install configuration wizard
--python EXE	Create .venv with this 3.10+ interpreter (EXE on PATH or full path)

If the Python Launcher is missing on Windows, install Python 3.10+ from python.org and enable “Add to PATH”.

Manual install (without the script)

cd /path/to/MySQL_MCP
python3 -m venv .venv
# Windows: .venv\Scripts\pip install -r requirements.txt
# Unix:    .venv/bin/pip install -r requirements.txt

After either method, set mcp.json command to the venv python / python.exe absolute path — not a bare python on PATH.

---

Configuration via mcp.json

All MySQL connection parameters are passed through the standard MCP configuration file. Choose Option A (stdio) or Option B (SSE) depending on your client.

Option A — stdio transport (recommended)

The MCP client launches server.py as a subprocess and injects MySQL credentials via the env block. No separate server process is needed.

Copy the following block into your client's MCP settings (e.g. Cursor mcp.json, Claude Desktop claude_desktop_config.json, or a project-level .cursor/mcp.json):

{
  "mcpServers": {
    "mysql-readonly": {
      "command": "/absolute/path/to/MySQL_MCP/.venv/bin/python",
      "args": ["/absolute/path/to/MySQL_MCP/server.py"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_mysql_user",
        "MYSQL_PASSWORD": "your_mysql_password",
        "MYSQL_DATABASE": "your_database_name",
        "MYSQL_CONNECT_TIMEOUT": "10",
        "MYSQL_SSL": "false",
        "QUERY_DEFAULT_LIMIT": "100",
        "QUERY_TABLE_BLACKLIST": "sensitive_table,internal_audit_log"
      }
    }
  }
}

args must contain the absolute path to server.py.
On Windows, use "command": "C:\\path\\to\\MySQL_MCP\\.venv\\Scripts\\python.exe" (escape backslashes in JSON).

Replace the env values with your actual MySQL credentials.

Option B — HTTP/SSE transport

Start the server as a standalone HTTP service first. MySQL settings come from the process environment (or your shell / systemd / Docker environment block).

export MYSQL_HOST=127.0.0.1
export MYSQL_PORT=3306
export MYSQL_USER=your_mysql_user
export MYSQL_PASSWORD=your_mysql_password
export MYSQL_DATABASE=your_database_name

# Default bind is 127.0.0.1 (safer). Use 0.0.0.0 only on trusted networks or
# behind a reverse proxy; set MCP_BEARER_TOKEN so clients must send
# Authorization: Bearer <token> on SSE and message requests.
export MCP_BEARER_TOKEN=your-long-random-secret   # optional but recommended if exposed

# Use the venv interpreter (from repo root after install):
# Unix/macOS:  .venv/bin/python server.py --transport sse --port 8000
# Windows:     .venv\Scripts\python.exe server.py --transport sse --port 8000
.venv/bin/python server.py --transport sse --port 8000

Then point your MCP client at the SSE endpoint (and configure the client to send the Bearer token if MCP_BEARER_TOKEN is set):

{
  "mcpServers": {
    "mysql-readonly": {
      "url": "http://localhost:8000/sse"
    }
  }
}

---

Environment Variables Reference

Variable	Required	Default	Description
MYSQL_HOST	No	127.0.0.1	MySQL hostname or IP
MYSQL_PORT	No	3306	MySQL port
MYSQL_USER	Yes	—	MySQL username
MYSQL_PASSWORD	No	""	MySQL password
MYSQL_DATABASE	Yes	—	Target database name
MYSQL_CONNECT_TIMEOUT	No	10	TCP connect timeout (seconds)
MYSQL_READ_TIMEOUT	No	30	Socket read timeout (seconds)
MYSQL_WRITE_TIMEOUT	No	30	Socket write timeout (seconds)
MYSQL_MAX_EXECUTION_TIME	No	30000	Per-query server limit (milliseconds); SET SESSION MAX_EXECUTION_TIME
MYSQL_SSL	No	false	Enable TLS to MySQL: "true" / "false"
MYSQL_SSL_CA	No	""	Path to CA certificate (when using TLS)
MYSQL_SSL_CERT	No	""	Path to client certificate
MYSQL_SSL_KEY	No	""	Path to client private key
MYSQL_SSL_VERIFY_CERT	No	true	Set "false" to skip server cert verification (not recommended)
QUERY_DEFAULT_LIMIT	No	100	Upper bound on rows for SELECT; explicit LIMIT is also capped to this (after applying the limit tool argument)
QUERY_TABLE_BLACKLIST	No	""	Comma-separated table names. The query tool rejects SQL that references them (including JOIN); describe_table still returns schema for those tables. Not a substitute for DB grants.
MCP_HOST	No	127.0.0.1	Bind address for SSE transport (CLI --host overrides at runtime)
MCP_PORT	No	8000	Bind port for SSE (CLI --port overrides)
MCP_BEARER_TOKEN	No	""	If non-empty, SSE HTTP requests require Authorization: Bearer <token>

---

Available Tools

query

Execute a readonly SQL statement and return results as structured JSON.

Parameter	Type	Default	Description
sql	str	—	SQL statement (SELECT / SHOW / DESCRIBE / EXPLAIN)
limit	int	QUERY_DEFAULT_LIMIT	Capped at QUERY_DEFAULT_LIMIT; combined with server rewrite so SELECT never returns more rows than this effective cap (even if SQL contains a larger LIMIT)

Returns:

{
  "columns": ["id", "name", "email"],
  "rows": [
    {"id": 1, "name": "Alice", "email": "[email protected]"}
  ],
  "row_count": 1
}

list_tables

List all tables in the configured database.

Returns:

{
  "database": "mydb",
  "tables": ["users", "orders", "products"],
  "count": 3
}

describe_table

Get the column schema of a specific table.

For tables in QUERY_TABLE_BLACKLIST, use this tool for schema — the query tool rejects any SQL (including DESCRIBE) that references those tables.

Parameter	Type	Description
table_name	str	Table name (letters, digits, underscores only)

Returns:

{
  "table": "users",
  "columns": [
    {"Field": "id",    "Type": "int",          "Null": "NO",  "Key": "PRI", "Default": null, "Extra": "auto_increment"},
    {"Field": "name",  "Type": "varchar(255)", "Null": "YES", "Key": "",    "Default": null, "Extra": ""},
    {"Field": "email", "Type": "varchar(255)", "Null": "YES", "Key": "UNI", "Default": null, "Extra": ""}
  ]
}

---

Security — Readonly Enforcement

The server enforces readonly access at the application layer with a two-stage guard:

1. Whitelist — the first keyword must be one of SELECT, SHOW, DESCRIBE, DESC, EXPLAIN.
2. Blacklist — the full statement is scanned for forbidden patterns: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, REPLACE, GRANT, REVOKE, COMMIT, ROLLBACK, LOAD DATA, INTO OUTFILE, SLEEP, BENCHMARK, and more.
3. Multi-statement rejection — any SQL containing ; (after stripping a single trailing semicolon) is rejected.
4. Identifier validation — table names passed to describe_table are validated to contain only [A-Za-z0-9_] characters before being interpolated into the query.
5. Table blacklist — tables listed in QUERY_TABLE_BLACKLIST cannot be used for data access through the query tool (including subqueries / JOINs that reference them). The describe_table tool is still allowed for those names so agents can inspect schema. Errors use a bilingual JSON payload (message_en / message_zh).

For production use, also configure the MySQL user with SELECT-only privileges at the database level as an additional layer of defense. Treat the app-level blacklist as a convenience, not the primary authorization boundary.

---

Verification

After configuration, test the server with these queries:

-- Should succeed
SHOW TABLES
SELECT * FROM your_table LIMIT 5
DESCRIBE your_table
EXPLAIN SELECT id FROM your_table

-- Should be rejected with an error
DELETE FROM your_table WHERE id = 1
INSERT INTO your_table (name) VALUES ('x')
SELECT 1; DROP TABLE your_table
SELECT SLEEP(5)

---

Running Locally (without an MCP client)

You can test the server directly from the command line using the MCP CLI:

# Install dev dependency
pip install "mcp[cli]"

# stdio mode — interactive inspector
MYSQL_HOST=127.0.0.1 MYSQL_USER=root MYSQL_PASSWORD=secret MYSQL_DATABASE=mydb \
  mcp dev server.py

# SSE mode — start server, then open http://localhost:8000/sse in a browser or curl
MYSQL_HOST=127.0.0.1 MYSQL_USER=root MYSQL_PASSWORD=secret MYSQL_DATABASE=mydb \
  .venv/bin/python server.py --transport sse

---

Project Structure

MySQL_MCP/
├── server.py            # MCP server: tools, SQL guard, MySQL connector
├── install.py           # First-time setup: venv + pip install (all platforms)
├── install.bat          # Windows launcher for install.py
├── mcp.json             # MCP configuration template (stdio + SSE examples)
├── requirements.txt     # Python dependencies
├── pyproject.toml       # Package metadata
├── Dockerfile           # Container image definition
├── docker-compose.yml   # Compose file (MCP server + optional local MySQL)
└── README.md            # This file

---

Docker Deployment

Build and run with Docker Compose

Create a .env file in the project root with your MySQL credentials:

MYSQL_HOST=host.docker.internal   # use host.docker.internal to reach the host machine
MYSQL_PORT=3306
MYSQL_USER=your_mysql_user
MYSQL_PASSWORD=your_mysql_password
MYSQL_DATABASE=your_database_name

Uncomment the ports block in docker-compose.yml (see the 127.0.0.1:${MCP_PORT:-8000}:8000 example) so the host can reach the container; it is commented out by default for safety.

Then start the container:

docker compose up -d

Set MCP_BEARER_TOKEN in .env when exposing SSE. The MCP SSE endpoint is then http://localhost:8000/sse (or the mapped host/port you chose).

Point your MCP client at it:

{
  "mcpServers": {
    "mysql-readonly": {
      "url": "http://localhost:8000/sse"
    }
  }
}

Build and run manually

docker build -t mysql-mcp-server .

docker run -d \
  --name mysql-mcp-server \
  -p 8000:8000 \
  -e MYSQL_HOST=host.docker.internal \
  -e MYSQL_USER=your_user \
  -e MYSQL_PASSWORD=your_password \
  -e MYSQL_DATABASE=your_db \
  mysql-mcp-server

Using with a local MySQL container

Uncomment the mysql service block in docker-compose.yml to spin up a local MySQL alongside the MCP server. The service uses a healthcheck so the MCP server only starts after MySQL is ready.

---

Extending

The following improvements are recommended before production use:

• Connection pooling — replace per-request connections with DBUtils or SQLAlchemy pool
• SQL AST validation — use sqlglot or sqlparse for structural analysis instead of regex
• Audit logging — log every executed query with timestamp, client identity, and row count
• Row-level rate limiting — enforce per-client query frequency limits
• TLS / mTLS at the edge — terminate HTTPS and optional client certificates in a reverse proxy in front of SSE; combine with MCP_BEARER_TOKEN for defense in depth

---

License

This project is licensed under the MIT License. See LICENSE.