README.md•9.82 kB
[](README-zh.md)
[](README.md)
# 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
| Role | Permissions | Use Case |
|------|-------------|----------|
| **readonly** | `SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN`, `USE` | Read-only access for reporting and analysis |
| **writer** | readonly + `INSERT`, `UPDATE`, `DELETE` | Application development and data manipulation |
| **admin** | Full database administration | Complete database management including DDL, user management, backup/recovery |
## 🛠️ Available Tools
### Database Operations
| Tool | Description |
|------|-------------|
| `execute_sql` | Execute SQL statements with role-based permission control |
| `get_databases` | List all available databases (excluding system databases) |
### Schema Discovery
| Tool | Description |
|------|-------------|
| `get_table_desc` | Get table structures across databases (supports `database.table` format) |
| `get_table_index` | Retrieve table indexes with cross-database support |
| `get_table_name` | Find tables by comments and descriptions |
### Performance & Health
| Tool | Description |
|------|-------------|
| `get_db_health_running` | Analyze MySQL health (connections, transactions, locks) |
| `get_db_health_index_usage` | Index usage analysis with performance recommendations |
| `get_table_lock` | Detect row-level and table-level locks |
### Utilities
| Tool | Description |
|------|-------------|
| `get_chinese_initials` | Convert Chinese field names to pinyin initials |
## 🤖 AI Prompt Templates
| Prompt Template | Purpose |
|----------------|----------|
| `analyzing-mysql-prompt` | Comprehensive MySQL issue analysis and troubleshooting |
| `query-table-data-prompt` | Intelligent table data querying with AI assistance |
## 🚀 Quick Start
### Prerequisites
- Python 3.10+
- UV package manager
- MySQL server
### Installation
#### 📦 Installing UV Package Manager
**Windows:**
```powershell
# 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:**
```bash
# 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):**
```powershell
git clone <repository-url>
cd mcp_mysql
uv sync
```
**Linux/macOS:**
```bash
git clone <repository-url>
cd mcp_mysql
uv sync
```
2. **Configure database connection:**
Create a `.env` file in the project root directory:
**Windows (PowerShell):**
```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:**
```env
# 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):**
```env
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):**
```powershell
# Start SSE server on http://localhost:9000
uv run server.py
```
**Linux/macOS:**
```bash
# Start SSE server on http://localhost:9000
uv run server.py
```
**MCP Client Configuration (SSE):**
```json
{
"mcpServers": {
"mysql": {
"name": "mysql",
"description": "MySQL database operations",
"isActive": true,
"baseUrl": "http://localhost:9000/sse"
}
}
}
```
#### STDIO Mode (Direct integration)
**Windows (PowerShell):**
```powershell
# Start STDIO server
uv run server.py --stdio
```
**Linux/macOS:**
```bash
# Start STDIO server
uv run server.py --stdio
```
**MCP Client Configuration (STDIO):**
**Windows:**
```json
{
"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:**
```json
{
"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:
```powershell
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:**
```powershell
# 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:**
```python
# 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:**
```python
# 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:**
```sql
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](CLAUDE.md) - For developers working with this codebase
- [API Reference](src/) - 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! ⭐**