How to Build a Read-Only MySQL MCP Server for AI Coding Tools
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.
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.
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.