mysql_mcp_server_pro

mcp_mysql_server

Introduction

mcp_mysql_server_pro is not just about MySQL CRUD operations, but also includes database anomaly analysis capabilities and makes it easy for developers to extend with custom tools.

• Supports both STDIO and SSE modes
• Supports multiple SQL execution, separated by ";"
• Supports querying database table names and fields based on table comments
• Supports SQL execution plan analysis
• Supports Chinese field to pinyin conversion
• Supports table lock analysis
• Supports database health status analysis
• Supports permission control with three roles: readonly, writer, and admin

  "readonly": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN"],  # Read-only permissions
  "writer": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE"],  # Read-write permissions
  "admin": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE", 
           "CREATE", "ALTER", "DROP", "TRUNCATE"]  # Administrator permissions
  

• Supports prompt template invocation

Tool List

Prompt List

Usage Instructions

Installation and Configuration

1. Install Package

pip install mysql_mcp_server_pro

2. Configure Environment Variables Create a .env file with the following content:

# MySQL Database Configuration
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database
# Optional, default is 'readonly'. Available values: readonly, writer, admin
MYSQL_ROLE=readonly

3. Run Service

# SSE mode
mysql_mcp_server_sse

4. mcp client

go to see see “Use uv to start the service” ^_^

Note:

• The .env file should be placed in the directory where you run the command
• You can also set these variables directly in your environment
• Make sure the database configuration is correct and can connect

Run with uvx, Client Configuration

{
    "mcpServers": {
        "mysql": {
            "command": "uvx",
            "args": [
                "--from",
                "mysql_mcp_server_pro",
                "mysql_mcp_server_pro"
            ],
            "env": {
                "MYSQL_HOST": "192.168.x.xxx",
                "MYSQL_PORT": "3306",
                "MYSQL_USER": "root",
                "MYSQL_PASSWORD": "root",
                "MYSQL_DATABASE": "a_llm",
                "MYSQL_ROLE": "admin"
            }
        }
    }
}

Local Development with SSE Mode

• Use uv to start the service

Add the following content to your mcp client tools, such as cursor, cline, etc.

mcp json as follows:

{
  "mcpServers": {
    "operateMysql": {
      "name": "operateMysql",
      "description": "",
      "isActive": true,
      "baseUrl": "http://localhost:9000/sse"
    }
  }
}

Modify the .env file content to update the database connection information with your database details:

# MySQL Database Configuration
MYSQL_HOST=192.168.xxx.xxx
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=root
MYSQL_DATABASE=a_llm
MYSQL_ROLE=readonly  # Optional, default is 'readonly'. Available values: readonly, writer, admin

Start commands:

# Download dependencies
uv sync

# Start
uv run -m mysql_mcp_server_pro.server 

Local Development with STDIO Mode

Add the following content to your mcp client tools, such as cursor, cline, etc.

mcp json as follows:

{
  "mcpServers": {
      "operateMysql": {
        "isActive": true,
        "name": "operateMysql",
        "command": "uv",
        "args": [
          "--directory",
          "G:\\python\\mysql_mcp\\src",  # Replace this with your project path
          "run",
          "server.py",
          "--stdio"
        ],
        "env": {
          "MYSQL_HOST": "192.168.xxx.xxx",
          "MYSQL_PORT": "3306",
          "MYSQL_USER": "root",
          "MYSQL_PASSWORD": "root",
          "MYSQL_DATABASE": "a_llm",
          "MYSQL_ROLE": "readonly"  # Optional, default is 'readonly'. Available values: readonly, writer, admin
       }
    }
  }
}    

Custom Tool Extensions

1. Add a new tool class in the handles package, inherit from BaseHandler, and implement get_tool_description and run_tool methods

2. Import the new tool in init.py to make it available in the server

Examples

1. Create a new table and insert data, prompt format as follows:

# Task
   Create an organizational structure table with the following structure: department name, department number, parent department, is valid.
# Requirements
 - Table name: department
 - Common fields need indexes
 - Each field needs comments, table needs comment
 - Generate 5 real data records after creation

2. Query data based on table comments, prompt as follows:

Search for data with Department name 'Executive Office' in Department organizational structure table

3. Analyze slow SQL, prompt as follows:

select * from t_jcsjzx_hjkq_cd_xsz_sk xsz
left join t_jcsjzx_hjkq_jcd jcd on jcd.cddm = xsz.cddm 
Based on current index situation, review execution plan and provide optimization suggestions in markdown format, including table index status, execution details, and optimization recommendations

4. Analyze SQL deadlock issues, prompt as follows:

update t_admin_rms_zzjg set sfyx = '0' where xh = '1' is stuck, please analyze the cause

5. Analyze the health status prompt as follows

Check the current health status of MySQL