MCP Server: MySQL Python Integration, Schema & ACLs

Overview

This MCP server implements a Model Context Protocol (MCP) adapter that connects language models or other agents to a MySQL database using a Python backend. It exposes a small set of tools for schema inspection and controlled query execution, and it enforces configurable access control rules (ACLs) so downstream clients can safely explore and query your database.

The server is designed for local development and containerized environments. It supports a stdio mode that reads/writes MCP JSON messages over standard input/output, making it easy to launch from a subprocess (for local LLM integrations) or as a Docker container. Use it to provide model agents with an up-to-date view of your schema, to run parameterized queries under policy constraints, and to keep execution auditable.

GitHub: https://github.com/tonycai/mcp-mysql-server

Features

• MySQL integration via Python connector (connects to remote or local MySQL instances)
• Schema inspection endpoints: list databases, tables, columns, and sample rows
• Configurable ACLs to allow/deny access at table- and column-level
• Safe query execution: parameterized execution and optional read-only enforcement
• stdio mode for simple local or containerized MCP message exchange
• Docker-friendly: run in a container and map configs or environment variables
• Logging and request/response tracing for auditing

Installation / Configuration

Clone and install (typical Python workflow):

git clone https://github.com/tonycai/mcp-mysql-server.git
cd mcp-mysql-server
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Minimal YAML configuration example (config.yaml):

mysql:
  host: localhost
  port: 3306
  user: myuser
  password: secret
  database: mydb
server:
  readonly: true           # optional: enforce read-only mode
  stdio: true              # run in stdio mode
acl:
  - name: reporting
    allow_tables:
      - name: sales
        allow_columns: ["id","region","amount","date"]
      - name: customers
        allow_columns: ["id","name","email"]
  - name: admin
    allow_tables: ["*"]    # wildcard: full access

Start the server in stdio mode:

python -m mcp_mysql_server --config config.yaml --stdio

Docker example:

docker build -t mcp-mysql-server .
docker run --rm -i \
  -v $(pwd)/config.yaml:/app/config.yaml \
  mcp-mysql-server --config /app/config.yaml --stdio

Environment variables (alternative to YAML keys):

• MYSQL_HOST
• MYSQL_PORT
• MYSQL_USER
• MYSQL_PASSWORD
• MYSQL_DATABASE
• MCP_STDIO (true/false)
• MCP_READONLY (true/false)

Available Tools / Resources

The server exposes several MCP-style tools that agents can call. Typical tool names and behaviors:

• inspect_schema
  • Returns list of schemas/databases and their tables.
• list_tables
  • Returns table names and row counts (if allowed).
• describe_table
  • Returns columns, types, and sample values for a specific table.
• execute_query
  • Runs a parameterized SQL query, subject to ACLs and read-only rules.
• explain_query
  • Returns the SQL EXPLAIN plan (read-only) to help model choose better queries.

Example ACL fields (table):

Message transport

• The server speaks MCP over newline-delimited JSON on stdin/stdout in stdio mode. Each request is a JSON object describing the tool and arguments; responses are JSON objects with status, data, and optional logs.

Use Cases

1. LLM-enabled data assistant

   • Give a chat model limited, auditable access to a production or staging MySQL instance. The model can ask for table schemas, and the server returns column definitions and sample rows. All query execution is filtered by ACLs to prevent data leakage.

2. Developer tooling and exploration

   • Run the server locally to let engineers explore unfamiliar schemas without giving full DB credentials to an interactive tool. Use read-only mode to avoid accidental mutations.

3. Automated testing and CI

   • Integrate the server into CI jobs that need to validate SQL generation from model outputs. The server can execute generated queries against a transient test database and return results or errors.

4. Query safety layer

   • Place the MCP server between an application that dynamically generates SQL (for example, via a model) and the database to enforce structural and column-level restrictions and to log queries for review.

Example: describe_table request (stdin/stdout)

Request (JSON line):

{"tool": "describe_table", "args": {"table": "sales"}, "context": {"acl_group": "reporting"}}

Possible response:

{"status":"ok","data":{"table":"sales","columns":[{"name":"id","type":"INT"},{"name":"region","type":"VARCHAR"},{"name":"amount","type":"DECIMAL"},{"name":"date","type":"DATE"}]}}

This pattern makes it straightforward to drive the server from a subprocess in your application, or to wire it into an LLM orchestration framework that speaks MCP.

For full details on options and message schema, refer to the repository: https://github.com/tonycai/mcp-mysql-server