📌Overview

Purpose: The MCP MySQL Server is designed to facilitate AI interactions with MySQL databases through a collection of tools, enhancing data management and operation efficiency.

Overview: The MCP MySQL Server operates based on the Model Context Protocol (MCP), providing a robust framework for executing various database operations. It integrates seamlessly with AI models, allowing them to perform tasks such as data querying and manipulation effectively. The server is built using Python and operates in a containerized environment, simplifying deployment and scalability.

Key Features:

• MCP Server Communication: The server acts as an intermediary, enabling AI models to request and execute database operations via a set of defined tools, thus ensuring efficient interaction with the MySQL database.

• Tool Executors: A collection of dedicated executors perform specific tasks like creating tables, querying data, or generating execution plans, which can be easily extended to accommodate new functionalities as needed.

---

mysql-mcp-server

한국어 README.md

0. Execution

Running with Docker

Change the database connection information as needed.

docker run -d --name mcp-mysql \
  -e MYSQL_HOST=localhost \
  -e MYSQL_PORT=3306 \
  -e MYSQL_USER=root \
  -e MYSQL_PASSWORD=mcpTest1234!!! \
  -e MYSQL_DATABASE=mcp_test \
  -e MCP_PORT=8081 \
  -p 3306:3306 mineru/mcp-mysql:1.0.0

Running with Docker Compose

This will proceed with a pre-configured setup.

docker-compose up -d

Running directly with Python

pip install -r requirements.txt
python mysql_mcp_server/main.py run

Cursor Configuration

MCP functionality is available from Cursor version 0.46 and above.
Additionally, the MCP feature is only accessible to Cursor Pro account users.

Tool Addition Tips

• Adding a Tool
  • execute functions implement the actual logic execution (Service Layer).
  • The @tool decorator helps register the tool with MCP (Controller Layer).
• Explanation
  • Each file under mysql_mcp_server/executors represents a single tool.
  • If a new tool is added, it must be imported in mysql_mcp_server/executors/__init__.py and included in the __all__ array.
  • This ensures the module is automatically registered in the TOOLS_DEFINITION variable.

flowchart LR;
    A[AI Model] -->|Request tool list| B[MCP Server]
    B -->|Return available tools| A

    A -->|Request specific tool execution| B
    B -->|Call the corresponding executor| C[Executors]
    
    subgraph Executors
        C1[execute_create_table] -->|Create table| D
        C2[execute_desc_table] -->|View table schema| D
        C3[execute_explain] -->|Query execution plan| D
        C4[execute_insert_query] -->|Execute INSERT query| D
        C5[execute_insight_starter] -->|Checking the schema for building reports| D
        C6[execute_invoke_viz_pro] -->|Visualization chart recommendations| D
        C7[execute_select_query] -->|Execute SELECT query| D
        C8[execute_show_tables] -->|Retrieve table list| D
    end

    D[DatabaseManager] -->|Connect to MySQL| E[MySQL 8.0]

    E -->|Return results| D
    D -->|Send results| C
    C -->|Return results| B
    B -->|Return execution results| A

Development Roadmap

• Parameter Options

  • Enable/Disable Switch for Each Tool: Provide a function to reduce Input Context costs
  • Query Security Level Setting: Optional control over potentially dangerous functions (DROP, DELETE, UPDATE)

• Features

  • Data Analysis Report Generation: Generates reports optimized to select appropriate charts based on user requests
    • Reporting capabilities for prescribed forms
    • Diversify report templates (planned)
  • Extended Text2SQL Support (planned)
  • SSH Connection Support: Enable secure remote access via SSH (planned)
  • File Extraction Function: CSV, JSON, Excel (planned)

1. Overview

MCP MySQL Server is a server application for MySQL database operations based on MCP (Model Context Protocol). This server provides tools that allow AI models to interact with the MySQL database.

2. System Configuration

2.1 Key Components

• MCP Server: A FastMCP server communicating with AI models
• MySQL Database: Data storage and management
• Tools: Executors performing database operations

2.2 Tech Stack

• Language: Python
• Database: MySQL 8.0
• Key Libraries:
  • mcp: Implements Model Context Protocol for AI communication
  • PyMySQL: Connects to MySQL and executes queries
  • pandas: Data processing and analysis
  • python-dotenv: Environment variable management
  • fire: Command-line interface implementation

2.3 Deployment Environment

• Containerized deployment via Docker and Docker Compose
• Ports: 8081 (MCP Server), 3306 (MySQL)

3. Directory Structure

MCPBoilerPlate/
├── mysql_mcp_server/           
│   ├── executors/              
│   │   ├── create_table.py     
│   │   ├── desc_table.py       
│   │   ├── explain.py          
│   │   ├── insert_query.py     
│   │   ├── insight_starter.py  
│   │   ├── invoke_viz_pro.py   
│   │   ├── select_query.py     
│   │   └── show_tables.py      
│   ├── helper/                 
│   │   ├── db_conn_helper.py   
│   │   ├── logger_helper.py    
│   │   └── tool_decorator.py   
│   └── main.py                 
├── docker-compose.yml          
├── Dockerfile                  
├── requirements.txt            
└── .env.example                

4. Architecture Design

4.1 Layered Structure

1. Interface Layer: MCP Server (FastMCP)
2. Business Logic Layer: Handlers and Executors
3. Data Access Layer: Database connection and query execution

4.2 Key Classes and Modules

• MySQLMCPServer: Main server class to initialize and run MCP server
• DatabaseManager: Singleton pattern database connection manager
• Executors: Tools for database operations
  • execute_create_table: Creates tables
  • execute_desc_table: Checks table schema
  • execute_explain: Provides query execution plans
  • execute_insert_query: Executes INSERT queries
  • execute_select_query: Executes SELECT queries
  • execute_show_tables: Retrieves table lists

4.3 Communication Flow

1. AI model requests available tools list from MCP server.
2. MCP server returns available tools.
3. AI model requests specific tool execution.
4. MCP server calls corresponding executor.
5. Execution results returned to AI model.

5. Scalability and Maintenance

• Adding Tools: Implement new tools in the executors directory and register in __init__.py.
• Environment Configuration: Use .env file for environment variables.
• Logging: Use logger_helper for consistent logging.

6. Deployment and Execution

6.1 Local Execution

# Setup environment
cp .env.example .env
# Modify .env as needed

# Install dependencies
pip install -r requirements.txt

# Run the server
python mysql_mcp_server/main.py run

6.2 Docker Deployment

# Start database using Docker Compose
docker-compose up -d db

# Build and run mysql-mcp-server with Docker Compose
docker-compose up -d --build mysql-mcp-server

7. Security Considerations

• Manage database credentials via environment variables.
• Use strong passwords in production.
• Consider SSL/TLS encryption for database connections if needed.