MySQL MCP Server

A comprehensive Model Context Protocol (MCP) server for MySQL database operations with advanced features for database analysis, health monitoring, and AI-assisted database management.

🚀 Features

MySQL MCP Server goes beyond basic CRUD operations, providing powerful database analysis capabilities and an extensible framework for custom tools.

🔌 Transport Modes

• STDIO Mode: Direct integration with MCP clients like Cursor, Cline

• SSE Mode: HTTP-based Server-Sent Events for web applications

🗄️ Database Operations

• Cross-Database Access: Connect once, access all databases without switching connections

• Multi-Statement Execution: Execute multiple SQL statements separated by ";"

• Smart Table Discovery: Find tables by comments and descriptions

• SQL Execution Plan Analysis: Performance optimization insights

🛡️ Security & Permissions

• Role-Based Access Control: Three permission levels (readonly, writer, admin)

• SQL Comment Support: Handles SQL statements with -- and /* */ comments

🔍 Advanced Analysis

• Health Monitoring: Connection, transaction, and lock status analysis

• Performance Insights: Index usage analysis and optimization recommendations

• Lock Detection: Row-level and table-level lock monitoring

• Chinese Text Processing: Pinyin conversion for Chinese field names

🤖 AI Integration

• Prompt Templates: Pre-built AI prompts for database analysis and querying

• Context-Aware Assistance: Intelligent database operation guidance

📋 Permission Levels

🛠️ Available Tools

Database Operations

Schema Discovery

Performance & Health

Utilities

🤖 AI Prompt Templates

🚀 Quick Start

Prerequisites

• Python 3.10+

• UV package manager

• MySQL server

Installation

📦 Installing UV Package Manager

Windows:

# Using PowerShell (Recommended)
powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"

# Or using pip
pip install uv

# Or using Chocolatey
choco install uv

# Or using Scoop
scoop install uv

Linux/macOS:

# Using curl
curl -LsSf https://astral.sh/uv/install.sh | sh

# Or using pip
pip install uv

🔽 Project Setup

1. Clone and install dependencies:

   Windows (PowerShell):

   git clone <repository-url>
   cd mcp_mysql
   uv sync

   Linux/macOS:

   git clone <repository-url>
   cd mcp_mysql
   uv sync

2. Configure database connection:

   Create a .env file in the project root directory:

   Windows (PowerShell):

   # Create .env file using PowerShell
   @"
   # MySQL Database Configuration
   MYSQL_HOST=localhost
   MYSQL_PORT=3306
   MYSQL_USER=your_username
   MYSQL_PASSWORD=your_password
   # MYSQL_DATABASE=specific_db  # Optional: leave empty for cross-database access
   MYSQL_ROLE=admin  # Options: readonly, writer, admin
   "@ | Out-File -FilePath ".env" -Encoding utf8

   Or manually create .env file with content:

   # MySQL Database Configuration
   MYSQL_HOST=localhost
   MYSQL_PORT=3306
   MYSQL_USER=your_username
   MYSQL_PASSWORD=your_password
   # MYSQL_DATABASE=specific_db  # Optional: leave empty for cross-database access
   MYSQL_ROLE=admin  # Options: readonly, writer, admin

   For remote MySQL server (example):

   MYSQL_HOST=192.168.1.100
   MYSQL_PORT=3306
   MYSQL_USER=root
   MYSQL_PASSWORD=your_secure_password
   MYSQL_ROLE=admin

Running the Server

SSE Mode (Web-based)

Windows (PowerShell):

# Start SSE server on http://localhost:9000
uv run server.py

Linux/macOS:

# Start SSE server on http://localhost:9000
uv run server.py

MCP Client Configuration (SSE):

{
  "mcpServers": {
    "mysql": {
      "name": "mysql",
      "description": "MySQL database operations",
      "isActive": true,
      "baseUrl": "http://localhost:9000/sse"
    }
  }
}

STDIO Mode (Direct integration)

Windows (PowerShell):

# Start STDIO server
uv run server.py --stdio

Linux/macOS:

# Start STDIO server
uv run server.py --stdio

MCP Client Configuration (STDIO):

Windows:

{
  "mcpServers": {
    "mysql": {
      "name": "mysql",
      "command": "uv",
      "args": [
        "--directory", "C:\\path\\to\\mcp_mysql\\src",
        "run", "server.py", "--stdio"
      ],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_ROLE": "admin"
      }
    }
  }
}

Linux/macOS:

{
  "mcpServers": {
    "mysql": {
      "name": "mysql",
      "command": "uv",
      "args": [
        "--directory", "/path/to/mcp_mysql/src",
        "run", "server.py", "--stdio"
      ],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_ROLE": "admin"
      }
    }
  }
}

🛠️ Windows-Specific Configuration Notes

Common Issues and Solutions

PowerShell Execution Policy: If you encounter execution policy errors, run:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Path Separators:

• Use double backslashes \\ or forward slashes / in JSON configuration

• Example: "C:\\Users\\YourName\\mcp_mysql" or "C:/Users/YourName/mcp_mysql"

Environment Variables in Windows:

# Set environment variables temporarily (current session)
$env:MYSQL_HOST = "localhost"
$env:MYSQL_USER = "your_username"
$env:MYSQL_PASSWORD = "your_password"

# Run the server with environment variables
uv run server.py --stdio

Windows Firewall: Ensure MySQL port (default 3306) is allowed through Windows Firewall if connecting to remote MySQL server.

🔧 Extending with Custom Tools

1. Create a new tool class:

   # src/handles/my_custom_tool.py
   from .base import BaseHandler
   from mcp.types import Tool, TextContent
   
   class MyCustomTool(BaseHandler):
       name = "my_custom_tool"
       description = "Description of what this tool does"
       
       def get_tool_description(self) -> Tool:
           # Define tool schema
           pass
           
       async def run_tool(self, arguments: dict) -> list[TextContent]:
           # Implement tool logic
           pass

2. Register the tool:

   # src/handles/__init__.py
   from .my_custom_tool import MyCustomTool
   
   __all__ = [..., "MyCustomTool"]

💡 Usage Examples

Cross-Database Operations

List all databases:

Use get_databases tool

Cross-database queries:

SELECT u.name, o.total 
FROM users_db.users u
JOIN orders_db.orders o ON u.id = o.user_id;

Table structure analysis:

get_table_desc: "users_db.users,orders_db.orders"

AI-Assisted Operations

Performance Analysis:

Analyze this slow query and suggest optimizations:
SELECT * FROM large_table lt 
LEFT JOIN another_table at ON lt.id = at.foreign_id 
WHERE lt.created_date > '2024-01-01'

Health Monitoring:

Check MySQL health status and identify any performance issues

Smart Table Discovery:

Find all tables related to user management across all databases

Deadlock Analysis:

Analyze why this UPDATE statement is stuck:
UPDATE users SET status = 'active' WHERE id = 123

📖 Documentation

• Development Guide - For developers working with this codebase

• API Reference - Detailed tool and prompt documentation

🤝 Contributing

1. Fork the repository

2. Create a feature branch

3. Add your custom tools or improvements

4. Submit a pull request

📄 License

This project is open source. Please check the license file for details.

Star this repository if you find it helpful! ⭐