跪拜 Guibai
← All articles
Artificial Intelligence

How to Build a Read-Only MySQL MCP Server for AI Coding Tools

By 数据治理自习室 ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

A single MCP Server plugs into any MCP-aware AI tool, eliminating per-app adapters. This guide shows a secure, read-only MySQL implementation that protects memory, blocks injection, and keeps secrets out of config files.

Summary

MCP, released by Anthropic in November 2024, defines a common protocol between AI applications and external tools. Without it, connecting M AI apps to N data sources requires M×N custom adapters; with it, each source implements a Server once and any MCP-host can talk to it, dropping adapters to M+N. The protocol separates three roles: Host, Client, and Server, and a Server can expose three capability types: Tools (model-invoked actions), Resources (read-only context), and Prompts (user-selected templates). Transport is either stdio for local processes or Streamable HTTP for remote deployment.

This guide walks through building a MySQL exploration server named mysql-explorer. It exposes list_tables, describe_table, and run_query as tools, plus two resources and one prompt. The code enforces four security layers: an SQL whitelist that strips comments before checking the first keyword, a strict table-name regex, an automatic LIMIT on SELECT queries to protect memory, and secret injection via environment variables instead of plaintext. A complementary database-side read-only account is also created. The server registers in WorkBuddy via a JSON config or UI, and is validated through MCP Inspector before use. The same pattern extends to any code-callable service, not just databases.

Takeaways
— MCP is an Anthropic open-source protocol from November 2024 that reduces adapter count from M×N to M+N.
— stdio is for local processes; Streamable HTTP is for remote, multi-client deployment.
— A Server exposes three capability types: Tools (model-invoked actions), Resources (read-only context), and Prompts (user-selected templates).
— mysql-explorer exposes 3 tools, 2 resources, and 1 prompt.
— Four security layers: SQL whitelist that strips comments, table name regex, automatic LIMIT, and env-based secret injection.
— Database-side defense: create a read-only MySQL user with only SELECT granted.
— WorkBuddy configuration requires absolute paths in args and env variables for secrets.
— Debug with MCP Inspector via `mcp dev mysql_server.py`; verify all three capabilities there before client integration.
— Never use print to stdout in stdio servers; write debug output to stderr or a log file.
— For HTTP deployment, change transport to streamable-http and use the url field in client config.
Conclusions

Stripping comments before the SQL whitelist check is a pragmatic counter to comment-masked injection like `-- DELETE`.

The dual-layer defense (code whitelist plus database permissions) protects against both AI misbehavior and human error.

Docstrings in MCP Tools become executable metadata: they determine when the model chooses to call a tool.

MCP's generality means the same server pattern applies to any code-callable service, making it a flexible integration layer beyond just data sources.

Concepts & terms
Model Context Protocol (MCP)
An open protocol from Anthropic that standardizes how AI applications talk to external tools and data sources, replacing per-application adapters with a single interface.
stdio vs Streamable HTTP
stdio communicates over standard input/output and is used for local processes; Streamable HTTP uses HTTP POST plus optional SSE streams for remote, multi-client deployment.
Tools, Resources, and Prompts
The three capability types an MCP Server can expose: Tools are model-invoked actions; Resources are read-only context read by the application; Prompts are user-selected reusable templates.
Source: juejin.cn ↗ Google Translate ↗ Backup ↗