Content
# MySQL MCP Server
An AI-native interaction layer for MySQL databases, built on top of FastMCP. Dynamically connects to any MySQL instance at runtime, condenses database operations into 18 structured tools covering **metadata search**, **performance analysis**, **large table insights**, **blocking diagnosis**, **slow query analysis**, and more. Enables LLM agents to complete complex tasks autonomously like seasoned engineers, without human intervention.
**Core Features:**
- **Context-aware** — Connects and collects 17 server metadata items (version, isolation level, sql_mode, buffer_pool) to provide accurate runtime environment context for AI, avoiding misjudgments.
- **Production-safe** — Read-only interception + query timeout + parameterized injection prevention + mis-kill protection + result set truncation + request authentication, six layers of protection ensure production databases can be connected and used immediately.
- **Tool-based constraints** — Dedicated tools replace free SQL execution, constraining AI behavior paths and reducing misoperation and injection risks.
- **Data exploration chain** — `search_schema → describe_schema → sample_data → count_estimate` progressive exploration, AI generates SQL after fully understanding data structures, significantly improving accuracy.
- **Fault self-handling** — `active_connections → lock_waits → kill_query` closed loop, AI autonomously completes blocking diagnosis and handling.
- **Concurrent connection to multiple databases** — Connects to multiple MySQL instances simultaneously through an alias connection pool, allowing one agent to query and diagnose across instances.
## Engineering Structure
```
mysql-mcp-server/
├── mysql_mcp/
│ ├── __init__.py # Package entry
│ ├── server.py # MCP Server entry (FastMCP + streamable-http)
│ ├── connection_manager.py # Connection manager, thread-safe
│ └── config_loader.py # Configuration file loader (YAML)
├── config.example.yaml # Configuration template
├── requirements.txt # Dependencies
├── Dockerfile # Container build
├── mysql-mcp.service # systemd service file
└── README.md
```
## Quick Start
### 1. Install Dependencies
```bash
pip install -r requirements.txt
```
### 2. Configure
```bash
cp config.example.yaml config.yaml
```
```yaml
# config.yaml
credentials:
user: "root"
password: "your password"
auth:
key: "your-secret-key" # Authentication key, client requests must carry in the X-Auth-Key header
mysql:
query_timeout_ms: 60000 # Single query timeout (milliseconds), default 60 seconds, prevent long queries from pulling down the database
```
### 3. Start
Execute in the project root directory:
```bash
python -m mysql_mcp.server
```
Server listens on `http://0.0.0.0:8200/mcp`, transport is streamable-http.
### 4. Docker Start
```bash
docker build -t mysql-mcp-server .
docker run -d -p 8200:8200 -v $(pwd)/config.yaml:/app/config.yaml mysql-mcp-server
```
## Connect to Agent
Client configuration requires the `X-Auth-Key` header:
```python
from langchain_mcp_adapters.client import MultiServerMCPClient
client = MultiServerMCPClient(
{
"mysql": {
"transport": "streamable-http",
"url": "http://localhost:8200/mcp",
"headers": {"X-Auth-Key": "your-secret-key"},
},
},
tool_name_prefix=True, # Tool name with server name prefix to avoid collisions across multiple servers
)
tools = await client.get_tools()
```
When sharing across teams, each team only needs to know the URL and `X-Auth-Key` to connect.
> The tools of this server are registered with bare names (e.g., `connect`, `kill_query`), and the consumption side enables `tool_name_prefix=True` to actually call names like `mysql_connect`, `mysql_kill_query`, etc. — uniformly responsible for namespace management by the adapter.
## Automatic Loading of Server Metadata
After a successful `mysql_connect`, 17 server metadata items are automatically collected as context for subsequent tool calls, avoiding AI guessing version characteristics:
| Group | Field | Purpose |
|------|------|------|
| Version | `version`, `innodb_version` | Determine if features are supported (CTE, window functions, JSON, etc.) |
| Identity | `server_id`, `read_only`, `super_read_only` | Distinguish primary and secondary, determine executable operation range |
| Replication | `log_bin`, `gtid_mode` | Determine if binlog is enabled, replication method |
| Capacity | `max_connections`, `innodb_buffer_pool_size` | Connection and memory baseline |
| Transaction | `transaction_isolation` | Default isolation level (RR / RC), affects lock behavior |
| Syntax | `sql_mode` | Strict mode, ONLY_FULL_GROUP_BY, etc., determine SQL writing |
| Timezone | `time_zone` | Time function and storage behavior |
| Slow Query | `long_query_time` | Slow query threshold, auxiliary judgment |
| Charset | `character_set_server` | Default character set |
| Double 1 | `innodb_flush_log_at_trx_commit`, `sync_binlog` | Persistence and binlog synchronization strategy, affecting data security level |
These information returns with the connection, AI does not need to call extra to choose the correct SQL syntax and diagnosis direction.
## Tool List
> The following tool names are actual call names after enabling `tool_name_prefix=True`; server-side registration with bare names (e.g., `connect`, `kill_query`), prefix added uniformly by langchain-mcp-adapters.
| Tool | Parameters | Description |
|------|------|------|
| `mysql_connect` | `host` (required), `port`, `user`, `password`, `database`, `alias` | Connect to instance, user/password read from config.yaml if missing; automatically returns server metadata (version, sql_mode, timezone, double 1 configuration, etc., 17 items) upon successful connection |
| `mysql_disconnect` | `alias` | Disconnect |
| `mysql_list_connections` | — | List all active connections |
| `mysql_search_schema` | `keyword`, `alias` | Search table/column/table comment by keyword, pure information_schema query does not scan business tables; up to 100 tables, 200 columns |
| `mysql_describe_schema` | `table`, `alias` | Return table/view DDL (SHOW CREATE TABLE), including columns, types, primary keys, indexes, foreign keys, engine, charset, comments; three-layer positioning library name: direct use with prefix → current connection library → cross-library search for same-name objects |
| `mysql_sample_data` | `database` (required), `table`, `limit`, `alias` | Sample table data (SELECT * LIMIT N), for AI to understand field semantics; database returned by describe_schema; limit default 10, up to 50 |
| `mysql_list_large_tables` | `min_size_gb`, `limit`, `alias` | Check tables exceeding threshold by ibd file size, in descending order of size; min_size_gb default 10, limit default 10, up to 50; return total and data |
| `mysql_count_estimate` | `database` (required), `table`, `alias` | Return table row count estimate and last update time (information_schema.TABLES, do not scan business tables); database returned by describe_schema |
| `mysql_query` | `query`, `alias` | Execute arbitrary read-only SQL, automatically truncated if exceeding 2000 rows |
| `mysql_explain` | `query`, `alias` | View SQL execution plan, return JSON format |
| `mysql_show_status` | `alias` | SHOW GLOBAL STATUS, return status variable dictionary |
| `mysql_show_variables` | `alias` | SHOW GLOBAL VARIABLES, return configuration variable dictionary |
| `mysql_show_engine_status` | `alias` | SHOW ENGINE INNODB STATUS, return deadlock, transaction, buffer pool, lock, and other diagnostic information |
| `mysql_show_statement_digest` | `limit`, `within_hours`, `alias` | View SQL fingerprint aggregation statistics, ordered by average single-time consumption to locate really slow SQL; default returns recent 24 hours of active SQL |
| `mysql_show_active_connections` | `alias` | View active connections, ordered by consumption time, find long queries and lock waits |
| `mysql_show_lock_waits` | `alias` | Analyze InnoDB lock blocking relationship (who is blocked by whom, wait time, lock object, last SQL of lock holder, kill suggestion) |
| `mysql_kill_query` | `process_id`, `alias` | Terminate specified connection executing SQL (KILL QUERY), retain connection itself; used to urgently kill slow queries, long transactions, blocking DDL |
| `mysql_list_long_running_transactions` | `min_duration_sec`, `alias` | Check long uncommitted InnoDB transactions, ordered by duration; min_duration_sec default 60, return thread_id can cooperate with kill_query to terminate |
## Usage Examples
### Example 1: Data Exploration
```
User: I just took over 172.10.20.11:3306, help me understand the table structure related to orders, and write a SQL to count yesterday's GMV
Agent automatically executes:
1. mysql_connect(host="172.10.20.11", port=3306)
→ Automatically obtain 17 metadata items such as version, sql_mode, isolation level
2. mysql_search_schema(keyword="order")
→ Discover related tables like orders, order_detail, order_refund
3. mysql_describe_schema(table="orders")
→ Get DDL: fields, types, primary keys, indexes, comments
4. mysql_sample_data(database="shop", table="orders", limit=5)
→ Sample real data, confirm field semantics and unit
5. Synthesize structure and samples → generate accurate SQL without manual correction
```
### Example 2: Performance Diagnosis — Slow Query Location and Optimization
```
User: 172.10.20.11:3306 recently responds slowly, help me troubleshoot
Agent automatically executes:
1. mysql_connect(host="172.10.20.11", port=3306)
2. mysql_show_statement_digest(limit=10, within_hours=6)
→ Order by average time consumption, lock the slowest SQL fingerprint
3. mysql_explain(query="SELECT * FROM orders WHERE ...")
→ See execution plan, find full table scan, no index
4. Comprehensive analysis → provide index suggestions and optimized SQL
```
### Example 3: Fault Self-Handling — Lock Blocking Diagnosis and Handling
```
User: Business feedback on 172.10.20.11:3306 database is stuck, help me handle
Agent automatically executes:
1. mysql_connect(host="172.10.20.11", port=3306)
2. mysql_show_active_connections()
→ Find multiple connections in Locked state, long wait time
3. mysql_show_lock_waits()
→ Lock blocking relationship: connection 12345 holds lock, blocking 30+ sessions
4. mysql_list_long_running_transactions(min_duration_sec=300)
→ Confirm the connection corresponds to an 8-minute uncommitted long transaction
5. mysql_kill_query(process_id=12345)
→ Terminate problematic SQL, blocking release
6. Report disposal result → no manual intervention, fully automated
```
### Example 4: Large Table Insights
```
User: 172.10.20.11:3306 disk is almost full, help me find the largest table
Agent automatically executes:
1. mysql_connect(host="172.10.20.11", port=3306)
2. mysql_list_large_tables(min_size_gb=5, limit=20)
→ Return list of tables exceeding 5GB, with total count
3. mysql_count_estimate(database="shop", table="order_log_2024")
→ View row count estimate and last update time of the largest table
4. Comprehensive analysis → provide archiving and cleaning suggestions
```
Connection Info
You Might Also Like
No recommendations available