How to Build a Read-Only MySQL MCP Server for AI Coding Tools
MCP Development and Usage Guide
This article addresses two questions: what MCP is, and how to develop a MCP Server from scratch that connects to MySQL and integrates with WorkBuddy.
Target audience: developers who want to extend tool capabilities in AI coding tools like WorkBuddy.
After reading this, you will be able to independently develop, debug, and register a working MCP Server, and develop a read-only exploration Server that connects to a database.
1. What is MCP?
1.1 One-line definition
MCP (Model Context Protocol) is an open-source protocol released by Anthropic in November 2024. It standardizes communication between AI applications and external tools and data sources.
1.2 Problem it solves
Without MCP, every AI application needs custom adapter code for every data source. Connecting M applications to N data sources requires M×N adapters.
With MCP, each data source only needs to implement a MCP Server once, and all MCP-compliant AI applications can use it directly. The adapter workload drops from M×N to M+N. It is like a USB interface: peripherals are built to a uniform interface, and hosts read from the same interface.
1.3 Three roles
| Role | Responsibility | Example |
|---|---|---|
| Host | Runs the AI application, manages connections, enforces security policies | WorkBuddy, Claude Desktop, Cursor |
| Client | Connector inside the Host, maintains a 1:1 session with one Server | Configure the MCP client on WorkBuddy; no separate installation needed |
| Server | Independent process exposing tools, resources, and prompt templates | File system Server, database Server, custom business Server |
1.4 Transport methods
| Transport | Communication medium | Use case |
|---|---|---|
| stdio | Standard input/output | Local processes, most common, simplest configuration |
| Streamable HTTP | HTTP POST + optional SSE stream | Remote deployment, shared by multiple clients |
The example in this guide uses stdio transport.
1.5 Three capabilities a Server provides
| Capability | Control | Function | Example |
|---|---|---|---|
| Tools | Model decides to call | Let the model execute actions and change state | Query orders, run data quality checks, send messages |
| Resources | Application decides to read | Inject read-only context into the model | Table schema docs, config files, metric definitions |
| Prompts | User actively selects | Prebuilt reusable prompt templates | "Generate inspection report", "Code review" templates |
Design principle for the three capabilities: operations with side effects or that require computation become Tools; read-only data becomes Resources; fixed question patterns become Prompts.
2. Developing a MCP Server
This chapter uses MySQL as an example to build a working MCP Server from scratch. The Server is named mysql-explorer.
It is positioned as read-only exploration: the AI can view tables, view schemas, and run queries, but cannot modify data.
It exposes 3 Tools, 2 Resources, and 1 Prompt, with each capability in its place: Tools execute actions (list tables, view structure, run queries), Resources provide read-only context (table list, column structure), and Prompt presets a fixed exploration report pattern.
2.1 Environment preparation
You need Python 3.10 or higher, and a connectable MySQL 5.7 / 8.0 instance. Run:
pip install "mcp[cli]" pymysql
Expected result: installs mcp and pymysql and their dependencies, exits with no errors.
2.2 Complete code
Create a file mysql_server.py with the following content. The purpose of each code block is written in the comments; reading the comments lets you understand all the code:
import json
import os
import re
import pymysql
from mcp.server.fastmcp import FastMCP
# Create Server instance; name is shown to the client
mcp = FastMCP("mysql-explorer")
# Connection config: read five connection essentials from environment variables; secrets are not written in plaintext in code or args
def _db_conf() -> dict:
return {
"host": os.environ.get("MYSQL_HOST", "127.0.0.1"),
"port": int(os.environ.get("MYSQL_PORT", "3306")),
"user": os.environ.get("MYSQL_USER", "root"),
"password": os.environ.get("MYSQL_PASSWORD", ""),
"database": os.environ.get("MYSQL_DB", ""),
"charset": "utf8mb4",
"cursorclass": pymysql.cursors.DictCursor,
"connect_timeout": 5,
}
# Open a new connection per call and close it after; DictCursor makes results directly dicts for better model readability
def _fetch_all(sql: str, params: tuple | None = None) -> list[dict]:
"""Execute a SQL statement and return all result rows."""
conn = pymysql.connect(**_db_conf())
try:
with conn.cursor() as cur:
cur.execute(sql, params)
return cur.fetchall()
finally:
conn.close()
# Read-only defense
# _assert_read_only strips comments then takes the first keyword and compares against the whitelist;
# stripping comments prevents bypass attempts like `-- DELETE`
_ALLOWED_HEAD = {"select", "show", "describe", "desc", "explain", "with"}
_COMMENT = re.compile(r"--.*?$|/\*.*?\*/", re.MULTILINE | re.DOTALL)
_TABLE_NAME = re.compile(r"^[A-Za-z0-9_$]+$")
def _assert_read_only(sql: str) -> str:
"""Remove comments, validate the statement type is read-only, and return the executable statement. Throw an error for non-read-only statements."""
cleaned = _COMMENT.sub("", sql).strip().rstrip(";")
head = cleaned.split(None, 1)[0].lower() if cleaned else ""
if head not in _ALLOWED_HEAD:
# Explicitly raise an exception: MCP converts exceptions into error results returned to the model, so the model can adjust its next action
raise ValueError(
f"This Server only allows read-only queries (SELECT / SHOW / DESC / EXPLAIN / WITH), received: {head or 'empty statement'}"
)
return cleaned
# Table name whitelist: validate before concatenating SHOW FULL COLUMNS to prevent injection
# Internal implementation: shared between Tool and Resource
def _list_tables_data() -> dict:
rows = _fetch_all("SHOW TABLE STATUS")
return {
"database": _db_conf()["database"],
"tables": [
{"table": r.get("Name"), "rows": r.get("Rows"), "comment": r.get("Comment") or ""}
for r in rows
],
}
def _describe_table_data(table: str) -> dict:
if not _TABLE_NAME.fullmatch(table):
raise ValueError("Table name only allows letters, digits, underscores and $, to prevent injection")
rows = _fetch_all(f"SHOW FULL COLUMNS FROM `{table}`")
columns = [
{
"column": r.get("Field"),
"type": r.get("Type"),
"nullable": r.get("Null"),
"key": r.get("Key"),
"default": r.get("Default"),
"comment": r.get("Comment") or "",
}
for r in rows
]
return {"table": table, "columns": columns}
# Tool: model decides to call
# @mcp.tool() registers the function as a Tool; type annotations automatically generate parameter schema;
# the docstring is what the model uses to decide when to call, so it must be clear
@mcp.tool()
def list_tables() -> dict:
"""List all tables in the currently connected database, including estimated row counts and table comments. Use this to understand available tables before exploring."""
return _list_tables_data()
@mcp.tool()
def describe_table(table: str) -> dict:
"""View the column structure of a specified table: column name, type, nullable, key, comment.
Args:
table: Table name, e.g. dwd_order
"""
return _describe_table_data(table)
@mcp.tool()
def run_query(sql: str, max_rows: int = 50) -> dict:
"""Execute a read-only SQL and return the result set. Only SELECT / SHOW / DESC / EXPLAIN / WITH are allowed;
a SELECT without LIMIT is automatically wrapped with a LIMIT to prevent large result sets from overwhelming memory.
Args:
sql: Read-only SQL statement, e.g. SELECT COUNT(*) FROM dwd_order
max_rows: Maximum number of rows to return, default 50
"""
cleaned = _assert_read_only(sql)
# For SELECT or WITH without LIMIT, wrap with a subquery and append LIMIT to protect memory
if cleaned.lower().startswith(("select", "with")) and not re.search(
r"\blimit\s+\d+", cleaned, re.IGNORECASE
):
cleaned = f"SELECT * FROM ({cleaned}) AS _sub LIMIT {int(max_rows)}"
rows = _fetch_all(cleaned)
return {"row_count": len(rows), "rows": rows[: int(max_rows)]}
# Resource: application decides to read, read-only context
# In URI templates, braces are path parameters; the client pulls read-only content by URI
@mcp.resource("mysql://tables")
def resource_tables() -> str:
"""Table list of the current database"""
return json.dumps(_list_tables_data(), ensure_ascii=False, indent=2)
@mcp.resource("mysql://tables/{table}/schema")
def resource_schema(table: str) -> str:
"""Column structure of a specified table"""
return json.dumps(_describe_table_data(table), ensure_ascii=False, indent=2)
# Prompt: user actively selects, return value is injected into the conversation as a prompt
@mcp.prompt()
def probe_report(table: str) -> str:
"""Generate a data exploration report prompt for a specified table"""
return (
f"Please complete data exploration for table {table}: first use describe_table to view column structure, "
f"then use run_query to count total rows and null values in key columns, "
f"finally output a conclusion-oriented exploration report."
)
if __name__ == "__main__":
# Start the stdio main loop; the Host communicates with this process via standard input/output
mcp.run()
2.3 Security design: four layers of defense
| Layer | Implementation | What it blocks |
|---|---|---|
| SQL whitelist | _assert_read_only | Write statements like INSERT / UPDATE / DELETE / DROP / ALTER |
| Table name validation | _describe_table_data | Injection characters like quotes, spaces in table names |
| Automatic LIMIT | run_query | Large result sets without LIMIT overwhelming memory |
| Secret isolation | env field in mcp.json | Secrets appearing in args and code in plaintext |
Comments are stripped before taking the first keyword, to prevent bypassing the whitelist with comment-masked statements like -- DELETE. Complementary database-side defense: create a dedicated read-only account for MCP. Run:
CREATE USER 'mcp_ro'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON your_database.* TO 'mcp_ro'@'%';
FLUSH PRIVILEGES;
The dual protection means: the code whitelist blocks AI misuse, and the database permissions catch any write operation that slips through. Both the account permission and the code validation are indispensable.
2.4 Registering with the client
WorkBuddy has two ways to register a MCP Server: interface configuration and configuration file. Both have the same effect; the interface configuration essentially writes to a config file. Choose either.
Method one: configure the file. Edit ~/.workbuddy/mcp.json and add the mcpServers configuration:
{
"mcpServers": {
"mysql-explorer": {
"command": "python3",
"args": ["/Users/wenhuadong/temp/mysql_server.py"],
"env": {
"MYSQL_HOST": "127.0.0.1",
"MYSQL_PORT": "3306",
"MYSQL_USER": "mcp_ro",
"MYSQL_PASSWORD": "your_password",
"MYSQL_DB": "your_database"
}
}
}
}
Method two: interface. In WorkBuddy's "Expert - Skills - Connector" - top right "Connector" - Custom Connector - Add MCP
Field meanings:
| Field | Meaning |
|---|---|
| mcpServers | Root node for all Servers; the key is the custom name of the Server |
| command | Launch command; under stdio it's an executable program |
| args | Array of launch arguments |
| env | Injected environment variables; all five MySQL connection essentials are passed here |
⚠️ Note: args must use absolute paths. Relative paths resolve against WorkBuddy's working directory, making results unpredictable.
💡 Tip: Local stdio Servers are registered in these two ways. If the Server is deployed remotely over HTTP, use the url field to point to the service address instead of writing command and args.
💡 Tip: stdio Servers are started by WorkBuddy when the session launches. After changing the configuration, you must reopen the session; old sessions won't take effect.
2.5 Verification steps
① Reopen a WorkBuddy session, ask the AI to list the currently available MCP tools, and confirm the three tools of mysql-explorer appear.
② Tell the AI: list the tables in the current database. Expected result: the AI calls list_tables and returns table names, estimated row counts, and comments.
③ Tell the AI: show the schema of dwd_order. Expected result: the AI calls describe_table and returns the column list.
④ Tell the AI: count how many rows are in dwd_order. Expected result: the AI calls run_query and returns row_count and the COUNT(*) result.
⑤ Tell the AI: DELETE FROM dwd_order. Expected result: run_query throws an error saying only read-only queries are allowed, and data is not modified.
2.6 Common failures
Beyond the general failures in Chapter 3, this Server has three MySQL-specific failures:
| Symptom | Cause | Resolution |
|---|---|---|
| Connection fails: Access denied | Wrong username or password, or the account lacks permission to that database | Manually connect with the same account from the command line to confirm reachability |
| Result truncated | max_rows defaults to 50 | Explicitly pass a larger max_rows, or aggregate first in SQL |
| Chinese characters garbled | Connection charset not utf8mb4 | _db_conf already sets charset to utf8mb4; check the table's own charset |
2.7 Extending to other services
MCP is a generic protocol; any service callable via code can be exposed through a MCP interface, not just data sources.
To switch data sources: replace _db_conf and _fetch_all, keep the four-layer defense structure, and you get an exploration Server for PostgreSQL, Doris, etc.
To switch service types: wrap internal system capabilities as Tools, such as order queries, message sending, data quality checks, knowledge base retrieval; WorkBuddy includes built-in MCP for several services.
3. Testing and debugging
3.1 MCP Inspector
The official debugging tool is Inspector. Run:
mcp dev mysql_server.py
The browser automatically opens the debug interface. Features:
| Panel | Purpose |
|---|---|
| Tools | List all tools, fill in parameters to call directly, view returns and exceptions |
| Resources | List resources, click to read contents |
| Prompts | Render prompt templates |
Acceptance criteria: all three capabilities are listed and called successfully in Inspector before integrating with a client.
3.2 Common failures
| Symptom | Cause | Resolution |
|---|---|---|
| Client shows Server connection failure | Script path wrong or Python version too old | Run the script manually from the command line, confirm it exits without errors and that the exit mode is waiting for input |
| Tool list empty | Session not reopened | Reopen the client session |
| Model doesn't call tools | docstring missing or vague | Write docstrings clarifying purpose and parameter meanings |
| Parameter validation failed | Missing type annotations | Add type annotations to all parameters; FastMCP relies on them to generate schemas |
⚠️ Note: When debugging a stdio Server, never use print to output debug information in the code. stdout is the protocol channel; extra output corrupts protocol frames. Write debug information to stderr or a log file.
4. Release forms and security
4.1 Choosing between stdio and HTTP
| Criterion | Choice |
|---|---|
| Only one client on the same machine uses it | stdio |
| Team shared, multiple clients, needs centralized auth | Streamable HTTP |
For HTTP, change mcp.run() to mcp.run(transport="streamable-http"), host it with uvicorn or another service, and in the client configuration use the url field to point to the service address.
4.2 Security red lines
① Tools must not execute unvalidated SQL string concatenation, file deletion, or command execution. ② Secrets are injected via the env field, not written in plaintext in args. ③ Resources expose only read-only data, never as write entry points. ④ Host should enable manual confirmation for high-risk Tools (clients commonly support a tool-level confirmation switch).
5. Pre-delivery self-check list
- The three capabilities (Tool / Resource / Prompt) are placed per the principle in section 1.5, not mixed
- Every Tool's docstring describes its purpose and parameters
- All parameters have type annotations
- All three capabilities are called successfully in Inspector
- Absolute path used in client configuration
- Secrets injected via env, not present in args or code
- No debug output printed to stdout during troubleshooting
For database Servers (Chapter 2), add:
- SQL whitelist and table name validation work; write statements are rejected
- SELECT without LIMIT gets an automatic LIMIT appended
- Database-side read-only account created and only SELECT granted