How gs76lee/mysql-mcp-server works
Project Type and Purpose
• This is a backend service (“mysql-mcp-server”) that implements the Model Context Protocol (MCP) for AI models to perform MySQL database operations.
• It exposes a FastMCP server interface, registers a set of “tools” (executors) for CRUD, schema inspection, query planning, reporting, and visualization recommendation, then relays results back to the AI model.
File Structure Analysis
• Top-level:
• Dockerfile + docker-compose.yml for containerized deployment
• .env.example, requirements.txt, README.md
• mysql_mcp_server/ (main application)
• assets/ (static images)
• test/ (unit tests)
• Inside mysql_mcp_server/:
• main.py: entry point, starts MCP server
• executors/: each file defines one tool (execute_* functions)
• helper/: utility modules (db_conn_helper, logger_helper, tool_decorator)
Key README Insights
• Layers: Interface (MCP Server), Business Logic (executors), Data Access (DatabaseManager via PyMySQL)
• Tech stack: Python, FastMCP, PyMySQL, pandas, python-dotenv, fire
• Deployment: Docker (+ Compose), direct Python run
a. Main Components
• AI Model (external client)
• MCP Server (FastMCP)
• Tool Registry / Controller Layer (tool_decorator, TOOLS_DEFINITION)
• Executors (create_table, desc_table, explain, insert_query, insight_starter, invoke_viz_pro, select_query, show_tables)
• DatabaseManager (singleton managing PyMySQL connections)
• MySQL Database (8.0)
• Docker / Compose Orchestrator
b. Interactions
1. AI Model → MCP Server: request list of tools
2. MCP Server → Controller: serves tool list
3. AI Model → MCP Server: invoke specific tool
4. MCP Server → Executor: call execute_* function
5. Executor → DatabaseManager: issue SQL via PyMySQL
6. DatabaseManager ↔ MySQL: run query, return results
7. Executor → MCP Server: wrap and send results
8. MCP Server → AI Model: return execution results
c. Architectural Patterns
• Layered architecture:
• Interface Layer (MCP Server)
• Business Logic Layer (executors + decorator registry)
• Data Access Layer (DatabaseManager)
• Singleton pattern for DatabaseManager
• Plugin/Registration pattern for tools via decorator and __init__.py imports
d. Technologies / Frameworks
• FastMCP (MCP Server)
• PyMySQL (DB connectivity)
• pandas (data processing inside insight_starter)
• python-dotenv (.env support)
• fire (CLI entry points)
• Docker + Docker Compose
Essential Architectural Elements to Capture
• Container orchestration: separate containers for MySQL and MCP server
• Tool registration mechanism (decorator + __all__)
• Execution flow (AI Model ↔ MCP Server ↔ Executor ↔ DatabaseManager ↔ MySQL)
• Layer boundaries and the technologies that implement them