跪拜 Guibai
← Back to the summary

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

Insert image description here

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.

Insert image description here

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

For database Servers (Chapter 2), add: