mcp_mysql_server_pro

If you find it useful, please give it a thumbs up and support it. Thank you all, esteemed experts!

Introduction

mcp_mysql_server_pro goes beyond the basic CRUD (Create, Read, Update, Delete) functionalities of MySQL, also incorporating database anomaly analysis capabilities, and is designed to be easily extendable for personalized tool development by developers.

• Supports all transmission modes of the Model Context Protocol (MCP) (STDIO, SSE, Streamable Http)

• Supports execution of multiple SQL statements, separated by ";"

• Supports querying the corresponding database table name and fields based on table comments

• Supports SQL execution plan analysis

• Supports conversion of Chinese field names to Pinyin

• Supports lock table analysis

• Supports running health status analysis

• Supports permission control: read-only (readonly), read-write (writer), and administrator (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

Tool Name	Description
execute_sql	SQL execution tool, capable of executing commands such as ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE", "CREATE", "ALTER", "DROP", "TRUNCATE"] based on permission configuration
get_chinese_initials	Converts Chinese field names into their pinyin initials
get_db_health_running	Analyzes the health status of MySQL (connection status, transaction status, running status, lock detection)
get_table_desc	Searches for the structure of the corresponding tables in the database based on table names, supports querying multiple tables
get_table_index	Searches for the indexes of the corresponding tables in the database based on table names, supports querying multiple tables
get_table_lock	Checks if there are any row-level or table-level locks present on the current MySQL server
get_table_name	Searches for the corresponding table names in the database based on table comments or descriptions
get_db_health_index_usage	Obtains the index usage situation of the currently connected MySQL database, including redundant index situations, poorly performing indexes, and the top 5 queries with no index usage and execution time over 30 seconds
use_prompt_queryTableData	Uses built-in prompts to let the model construct a chain call to tools within MCP (not a commonly used fixed tool, requires code modification to enable, see the class for details)

Prompt List

| Prompt Name | Description ||----------------------------|------------------------------------------------------------------------------------------------------------------------------------|
| analyzing-mysql-prompt | This is a prompt for analyzing MySQL-related issues. |
| query-table-data-prompt | This is a prompt for querying table data by calling a tool. The description can be empty, and if it is, it will be initialized as a MySQL database data query assistant. |

Usage Instructions

pip Installation and Configuration

1. Install the package
   bash
   pip install mysql_mcp_server_pro

Parameter Explanation:
--mode: Transmission mode ("stdio", "sse", "streamablehttp")
--envfile: Path to the environment variable file

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

MySQL Database Configuration

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database

Optional values: readonly, writer, admin; default is readonly

MYSQL_ROLE=readonly

1. Run the Service
   bash

SSE Mode

mysql_mcp_server_pro --mode sse --envfile /path/to/.env

Streamable Http Mode (Default)

mysql_mcp_server_pro --envfile /path/to/.env

1. Configure on the MCP Client. For more details, see the SSE startup below.

Note:

• The .env file should be placed in the directory where the command is run or specify a custom path using the --envfile parameter.
• You can also set these variables directly in the environment.
• Ensure that the database configuration is correct and the connection is possible.

Running with uvx, Client Configuration

• This method can be configured directly on the MCP client without downloading the source code. For example, Qwen plugin, trae editor, etc.
  json
  {
  "mcpServers": {
  "mysql": {
  "command": "uvx",
  "args": [
  "--from",
  "mysql_mcp_server_pro",
  "mysql_mcp_server_pro",
  "--mode",
  "stdio"
  ],
  "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 - Streamable Http Method

• Start the service using uv

Add the following content to your MCP client tool, such as cursor, cline, etc.

MCP JSON as follows:
json
{
"mcpServers": {
"mysql_mcp_server_pro": {
"name": "mysql_mcp_server_pro",
"type": "streamableHttp",
"description": "",
"isActive": true,
"url": "http://localhost:3000/mcp/"
}
}
}

Modify the .env file content, changing the database connection information to your own.
bash

MySQL Database Configuration

MYSQL_HOST=192.168.xxx.xxx
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=root
MYSQL_DATABASE=a_llm
MYSQL_ROLE=admin

Start Command
bash

Download dependencies

uv sync

Start

uv run -m mysql_mcp_server_pro.server

Custom env file location

uv run -m mysql_mcp_server_pro.server --envfile /path/to/.env

Local Development - SSE Method

• Start the service using uv

Add the following content to your MCP client tool, such as cursor, cline, etc.

MCP JSON as follows:
json
{
"mcpServers": {
"mysql_mcp_server_pro": {
"name": "mysql_mcp_server_pro",
"description": "",
"isActive": true,
"url": "http://localhost:9000/sse"
}
}
}

Modify the .env file content, changing the database connection information to your own.
bash

MySQL Database Configuration

MYSQL_HOST=192.168.xxx.xxx
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=root
MYSQL_DATABASE=a_llm
MYSQL_ROLE=admin

Start Command
bash

Download dependencies

uv sync

Start

uv run -m mysql_mcp_server_pro.server --mode sse

Custom env file location

uv run -m mysql_mcp_server_pro.server --mode sse --envfile /path/to/.env

Local Development - STDIO Method

Add the following content to your MCP client tool, such as cursor, cline, etc.

MCP JSON as follows:
json
{
"mcpServers": {
"operateMysql": {
"isActive": true,
"name": "operateMysql",
"command": "uv",
"args": [
"--directory",
"/Volumes/mysql_mcp_server_pro/src/mysql_mcp_server_pro", # Replace this with your project path
"run",
"-m",
"mysql_mcp_server_pro.server",
"--mode",
"stdio"
],
"env": {
"MYSQL_HOST": "localhost",
"MYSQL_PORT": "3306",
"MYSQL_USER": "root",
"MYSQL_PASSWORD": "123456",
"MYSQL_DATABASE": "a_llm",
"MYSQL_ROLE": "admin"
}
}
}
}## Custom Extension Tools

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

2. Introduce the new tool in __init__.py to be callable within the server.

Tool Invocation Examples

1. Creating a new table and inserting data, the prompt format is as follows:

Task

Create an organizational structure table with the following structure: Department Name, Department Number, Parent Department, Is Valid.

Requirements

• Use t_admin_rms_zzjg as the table name,
• Field requirements: use 'varchar(255)' for string types, 'int' for integer types, 'float' for floating-point numbers, 'datetime' for date and time, 'boolean' for boolean, 'text' for text, 'longtext' for large text, 'bigint' for large integers, and 'double' for large floating-point numbers.
• The table header must include a primary key field, Serial No. XH varchar(255)
• At the end of the table, add fixed fields: Creator-CJR varchar(50), Creation Time-CJSJ datetime, Modifier-XGR varchar(50), Modification Time-XGSJ datetime.
• Use the tool's returned content for field naming
• Commonly used fields should have indexes added
• Each field needs to be annotated, and the table itself also requires a comment
• After creation, generate 5 real data entries

1. Querying data based on table comments, the prompt is as follows:

Query the data of Zhang San from the user information table

1. Analyzing slow SQL, the prompt is 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 the current index situation, check the execution plan and propose optimization suggestions. Output in markdown format, including the indexing status, execution status, and optimization suggestions related to the SQL.

1. Analyzing SQL deadlock issues, the prompt is as follows:

The update statement update t_admin_rms_zzjg set sfyx = '0' where xh = '1' has deadlocked, please analyze the cause

1. Analyzing health status, the prompt is as follows:

Check the current health status of MySQL

Prompt Invocation Examples

1. Example of invoking a MySQL analysis prompt

   • Step 1: Select "analyzing-mysql-prompt"

   

   • Step 2: Automatically generate the corresponding prompt

   

   • Step 3: Start the Q&A

   

2. Example of invoking a table data query prompt

   • Step 1: Select "query-table-data-prompt"

   

   • Step 2: Enter the problem description (optional). If not entered, it will default to the MySQL data query assistant.