# Directory Structure
```
├── .gitignore
├── docker-compose.yml
├── Dockerfile
├── LICENSE
├── pyproject.toml
├── README-zh.md
├── README.md
├── requirements.txt
└── src
└── mysql_mcp_server_pro
├── __init__.py
├── cli.py
├── config
│ ├── __init__.py
│ ├── .env
│ ├── dbconfig.py
│ └── event_store.py
├── exception
│ ├── __init__.py
│ └── exceptions.py
├── handles
│ ├── __init__.py
│ ├── base.py
│ ├── execute_sql.py
│ ├── get_chinese_initials.py
│ ├── get_db_health_index_usage.py
│ ├── get_db_health_running.py
│ ├── get_table_desc.py
│ ├── get_table_index.py
│ ├── get_table_lock.py
│ ├── get_table_name.py
│ ├── optimize_sql.py
│ └── use_prompt_queryTableData.py
├── oauth
│ ├── __init__.py
│ ├── config.py
│ ├── middleware.py
│ ├── routes.py
│ └── token_handler.py
├── prompts
│ ├── __init__.py
│ ├── AnalysisMySqlIssues.py
│ ├── BasePrompt.py
│ └── QueryTableData.py
├── server.py
├── templates
│ └── login.html
└── utils
├── __init__.py
├── database_pool.py
└── execute_sql_util.py
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | .env
2 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | [](README-zh.md)
2 | [](README.md)
3 | [](https://mseep.ai/app/wenb1n-dev-mysql-mcp-server-pro)
4 | [](https://mcphub.com/mcp-servers/wenb1n-dev/mysql_mcp_server_pro)
5 |
6 |
7 | # mcp_mysql_server_pro
8 |
9 | ## Introduction
10 | 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.
11 |
12 | - Supports all Model Context Protocol (MCP) transfer modes (STDIO, SSE, Streamable Http)
13 | - Supports OAuth2.0
14 | - Supports multiple SQL execution, separated by ";"
15 | - Supports querying database table names and fields based on table comments
16 | - Supports SQL execution plan analysis
17 | - Supports Chinese field to pinyin conversion
18 | - Supports table lock analysis
19 | - Supports database health status analysis
20 | - Supports permission control with three roles: readonly, writer, and admin
21 | ```
22 | "readonly": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN"], # Read-only permissions
23 | "writer": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE"], # Read-write permissions
24 | "admin": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE",
25 | "CREATE", "ALTER", "DROP", "TRUNCATE"] # Administrator permissions
26 | ```
27 | - Supports prompt template invocation
28 |
29 |
30 | ## Tool List
31 | | Tool Name | Description |
32 | |----------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
33 | | execute_sql | SQL execution tool that can execute ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE", "CREATE", "ALTER", "DROP", "TRUNCATE"] commands based on permission configuration |
34 | | get_chinese_initials | Convert Chinese field names to pinyin initials |
35 | | get_db_health_running | Analyze MySQL health status (connection status, transaction status, running status, lock status detection) |
36 | | get_table_desc | Search for table structures in the database based on table names, supporting multi-table queries |
37 | | get_table_index | Search for table indexes in the database based on table names, supporting multi-table queries |
38 | | get_table_lock | Check if there are row-level locks or table-level locks in the current MySQL server |
39 | | get_table_name | Search for table names in the database based on table comments and descriptions |
40 | | get_db_health_index_usage | Get the index usage of the currently connected mysql database, including redundant index situations, poorly performing index situations, and the top 5 unused index situations with query times greater than 30 seconds |
41 | | optimize_sql | Professional SQL performance optimization tool, providing expert optimization suggestions based on MySQL execution plans, table structure information, table data volume, and table indexes. |
42 | | use_prompt_queryTableData | Use built-in prompts to let the model construct a chain call of tools in mcp (not a commonly used fixed tool, you need to modify the code to enable it, see this class for details) |
43 |
44 | ## Prompt List
45 | | Prompt Name | Description |
46 | |---------------------------|---------------------------------------------------------------------------------------------------------------------------------------|
47 | | analyzing-mysql-prompt | This is a prompt for analyzing MySQL-related issues |
48 | | query-table-data-prompt | This is a prompt for querying table data using tools. If description is empty, it will be initialized as a MySQL database query assistant |
49 |
50 | ## Usage Instructions
51 |
52 | ### Installation and Configuration
53 | 1. Install Package
54 | ```bash
55 | pip install mysql_mcp_server_pro
56 | ```
57 |
58 | 2. Configure Environment Variables
59 | Create a `.env` file with the following content:
60 | ```bash
61 | # MySQL Database Configuration
62 | MYSQL_HOST=localhost
63 | MYSQL_PORT=3306
64 | MYSQL_USER=your_username
65 | MYSQL_PASSWORD=your_password
66 | MYSQL_DATABASE=your_database
67 | # Optional, default is 'readonly'. Available values: readonly, writer, admin
68 | MYSQL_ROLE=readonly
69 | ```
70 |
71 | 3. Run Service
72 | ```bash
73 | # SSE mode
74 | mysql_mcp_server_pro --mode sse --envfile /path/to/.env
75 |
76 | ## Streamable Http mode (default)
77 | mysql_mcp_server_pro --envfile /path/to/.env
78 |
79 | # Streamable Http oauth Authentication
80 | mysql_mcp_server_pro --oauth true
81 |
82 | ```
83 |
84 | 4. mcp client
85 |
86 | go to see see "Use uv to start the service"
87 | ^_^
88 |
89 |
90 | Note:
91 | - The `.env` file should be placed in the directory where you run the command or use --envfile parameter to specify the path
92 | - You can also set these variables directly in your environment
93 | - Make sure the database configuration is correct and can connect
94 |
95 | ### Run with uvx, Client Configuration
96 | - This method can be used directly in MCP-supported clients, no need to download the source code. For example, Tongyi Qianwen plugin, trae editor, etc.
97 | ```json
98 | {
99 | "mcpServers": {
100 | "mysql": {
101 | "command": "uvx",
102 | "args": [
103 | "--from",
104 | "mysql_mcp_server_pro",
105 | "mysql_mcp_server_pro",
106 | "--mode",
107 | "stdio"
108 | ],
109 | "env": {
110 | "MYSQL_HOST": "192.168.x.xxx",
111 | "MYSQL_PORT": "3306",
112 | "MYSQL_USER": "root",
113 | "MYSQL_PASSWORD": "root",
114 | "MYSQL_DATABASE": "a_llm",
115 | "MYSQL_ROLE": "admin"
116 | }
117 | }
118 | }
119 | }
120 | ```
121 |
122 | ### Local Development with Streamable Http mode
123 |
124 | - Use uv to start the service
125 |
126 | Add the following content to your mcp client tools, such as cursor, cline, etc.
127 |
128 | mcp json as follows:
129 | ```
130 | {
131 | "mcpServers": {
132 | "mysql_mcp_server_pro": {
133 | "name": "mysql_mcp_server_pro",
134 | "type": "streamableHttp",
135 | "description": "",
136 | "isActive": true,
137 | "url": "http://localhost:3000/mcp/"
138 | }
139 | }
140 | }
141 | ```
142 |
143 | Modify the .env file content to update the database connection information with your database details:
144 | ```
145 | # MySQL Database Configuration
146 | MYSQL_HOST=192.168.xxx.xxx
147 | MYSQL_PORT=3306
148 | MYSQL_USER=root
149 | MYSQL_PASSWORD=root
150 | MYSQL_DATABASE=a_llm
151 | MYSQL_ROLE=admin
152 | ```
153 |
154 | Start commands:
155 | ```
156 | # Download dependencies
157 | uv sync
158 |
159 | # Start
160 | uv run -m mysql_mcp_server_pro.server
161 |
162 | # Custom env file location
163 | uv run -m mysql_mcp_server_pro.server --envfile /path/to/.env
164 |
165 | # oauth Authentication
166 | uv run -m mysql_mcp_server_pro.server --oauth true
167 | ```
168 |
169 | ### Local Development with SSE Mode
170 |
171 | - Use uv to start the service
172 |
173 | Add the following content to your mcp client tools, such as cursor, cline, etc.
174 |
175 | mcp json as follows:
176 | ```
177 | {
178 | "mcpServers": {
179 | "mysql_mcp_server_pro": {
180 | "name": "mysql_mcp_server_pro",
181 | "description": "",
182 | "isActive": true,
183 | "url": "http://localhost:9000/sse"
184 | }
185 | }
186 | }
187 | ```
188 |
189 | Modify the .env file content to update the database connection information with your database details:
190 | ```
191 | # MySQL Database Configuration
192 | MYSQL_HOST=192.168.xxx.xxx
193 | MYSQL_PORT=3306
194 | MYSQL_USER=root
195 | MYSQL_PASSWORD=root
196 | MYSQL_DATABASE=a_llm
197 | MYSQL_ROLE=admin
198 | ```
199 |
200 | Start commands:
201 | ```
202 | # Download dependencies
203 | uv sync
204 |
205 | # Start
206 | uv run -m mysql_mcp_server_pro.server --mode sse
207 |
208 | # Custom env file location
209 | uv run -m mysql_mcp_server_pro.server --mode sse --envfile /path/to/.env
210 | ```
211 |
212 | ### Local Development with STDIO Mode
213 |
214 | Add the following content to your mcp client tools, such as cursor, cline, etc.
215 |
216 | mcp json as follows:
217 | ```
218 | {
219 | "mcpServers": {
220 | "operateMysql": {
221 | "isActive": true,
222 | "name": "operateMysql",
223 | "command": "uv",
224 | "args": [
225 | "--directory",
226 | "/Volumes/mysql_mcp_server_pro/src/mysql_mcp_server_pro", # Replace this with your project path
227 | "run",
228 | "-m",
229 | "mysql_mcp_server_pro.server",
230 | "--mode",
231 | "stdio"
232 | ],
233 | "env": {
234 | "MYSQL_HOST": "localhost",
235 | "MYSQL_PORT": "3306",
236 | "MYSQL_USER": "root",
237 | "MYSQL_PASSWORD": "123456",
238 | "MYSQL_DATABASE": "a_llm",
239 | "MYSQL_ROLE": "admin"
240 | }
241 | }
242 | }
243 | }
244 | ```
245 |
246 | ## Custom Tool Extensions
247 | 1. Add a new tool class in the handles package, inherit from BaseHandler, and implement get_tool_description and run_tool methods
248 |
249 | 2. Import the new tool in __init__.py to make it available in the server
250 |
251 | ## OAuth2.0 Authentication
252 | 1. Start the authentication service. By default, it uses the built-in OAuth 2.0 password mode authentication. You can modify your own authentication service address in the env file.
253 | ```aiignore
254 | uv run -m mysql_mcp_server_pro.server --oauth true
255 | ```
256 |
257 | 2. Visit the authentication service at http://localhost:3000/login. Default username and password are configured in the env file.
258 | 
259 |
260 |
261 | 3. Copy the token and add it to the request headers, for example:
262 | 
263 |
264 | ```json
265 | {
266 | "mcpServers": {
267 | "mysql_mcp_server_pro": {
268 | "name": "mysql_mcp_server_pro",
269 | "type": "streamableHttp",
270 | "description": "",
271 | "isActive": true,
272 | "url": "http://localhost:3000/mcp/",
273 | "headers": {
274 | "authorization": "bearer TOKEN_VALUE"
275 | }
276 | }
277 | }
278 | }
279 | ```
280 |
281 | ## Examples
282 | 1. Create a new table and insert data, prompt format as follows:
283 | ```
284 | # Task
285 | Create an organizational structure table with the following structure: department name, department number, parent department, is valid.
286 | # Requirements
287 | - Table name: department
288 | - Common fields need indexes
289 | - Each field needs comments, table needs comment
290 | - Generate 5 real data records after creation
291 | ```
292 | 
293 | 
294 |
295 |
296 | 2. Query data based on table comments, prompt as follows:
297 | ```
298 | Search for data with Department name 'Executive Office' in Department organizational structure table
299 | ```
300 | 
301 |
302 |
303 | 3. Analyze slow SQL, prompt as follows:
304 | ```
305 | select * from t_jcsjzx_hjkq_cd_xsz_sk xsz
306 | left join t_jcsjzx_hjkq_jcd jcd on jcd.cddm = xsz.cddm
307 | Based on current index situation, review execution plan and provide optimization suggestions in markdown format, including table index status, execution details, and optimization recommendations
308 | ```
309 |
310 | 4. Analyze SQL deadlock issues, prompt as follows:
311 | ```
312 | update t_admin_rms_zzjg set sfyx = '0' where xh = '1' is stuck, please analyze the cause
313 | ```
314 | 
315 |
316 |
317 | 5. Analyze the health status prompt as follows
318 | ```
319 | Check the current health status of MySQL
320 | ```
321 | 
322 |
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/__init__.py:
--------------------------------------------------------------------------------
```python
1 |
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/exception/__init__.py:
--------------------------------------------------------------------------------
```python
1 |
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/cli.py:
--------------------------------------------------------------------------------
```python
1 | from mysql_mcp_server_pro.server import main
2 |
3 | def stdio_entry():
4 | main()
5 |
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/config/__init__.py:
--------------------------------------------------------------------------------
```python
1 | from .dbconfig import get_db_config,get_role_permissions
2 |
3 | __all__ = [
4 | "get_db_config",
5 | "get_role_permissions",
6 | ]
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/prompts/__init__.py:
--------------------------------------------------------------------------------
```python
1 | from .AnalysisMySqlIssues import AnalysisMySqlIssues
2 | from .QueryTableData import QueryTableData
3 |
4 | __all__ = [
5 | "AnalysisMySqlIssues",
6 | "QueryTableData",
7 | ]
```
--------------------------------------------------------------------------------
/requirements.txt:
--------------------------------------------------------------------------------
```
1 | mcp>=1.8.0
2 | mysql-connector-python>=9.2.0
3 | pymysql>=1.1.1
4 | pypinyin>=0.54.0
5 | python-dotenv>=1.1.0
6 | starlette>=0.46.1
7 | uvicorn>=0.34.0
8 | PyJWT>=2.8.0
9 | sqlalchemy>=2.0.0
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/oauth/__init__.py:
--------------------------------------------------------------------------------
```python
1 | from .config import oauth_config
2 | from .token_handler import TokenHandler
3 | from .middleware import OAuthMiddleware
4 | from .routes import login, login_page
5 |
6 | __all__ = [
7 | "oauth_config",
8 | "TokenHandler",
9 | "OAuthMiddleware",
10 | "login",
11 | "login_page"
12 | ]
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
1 | version: '3.8'
2 |
3 | services:
4 | mysql-mcp-server:
5 | build:
6 | context: .
7 | dockerfile: Dockerfile
8 | container_name: mysql-mcp-server
9 | ports:
10 | - "9000:9000" # 如果你的应用监听 9000 端口，请根据实际情况调整
11 | environment:
12 | MYSQL_HOST: ${MYSQL_HOST:-localhost}
13 | MYSQL_PORT: ${MYSQL_PORT:-3306}
14 | MYSQL_USER: ${MYSQL_USER:-root}
15 | MYSQL_PASSWORD: ${MYSQL_PASSWORD:-123456}
16 | MYSQL_DATABASE: ${MYSQL_DATABASE:-a_llm}
17 | MYSQL_ROLE: ${MYSQL_ROLE:-admin}
18 | PYTHONPATH: /app/src
19 | restart: unless-stopped
20 | command: ["python", "-m", "mysql_mcp_server_pro.server"]
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/handles/__init__.py:
--------------------------------------------------------------------------------
```python
1 | from .execute_sql import ExecuteSQL
2 | from .get_chinese_initials import GetChineseInitials
3 | from .get_table_desc import GetTableDesc
4 | from .get_table_index import GetTableIndex
5 | from .get_table_lock import GetTableLock
6 | from .get_table_name import GetTableName
7 | from .get_db_health_running import GetDBHealthRunning
8 | from .get_db_health_index_usage import GetDBHealthIndexUsage
9 | from .use_prompt_queryTableData import UsePromptQueryTableData
10 | from .optimize_sql import OptimizeSql
11 |
12 | __all__ = [
13 | "ExecuteSQL",
14 | "GetChineseInitials",
15 | "GetTableDesc",
16 | "GetTableIndex",
17 | "GetTableLock",
18 | "GetTableName",
19 | "GetDBHealthRunning",
20 | "GetDBHealthIndexUsage",
21 | "UsePromptQueryTableData",
22 | "OptimizeSql"
23 | ]
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | # Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
2 | # Use the official Python image from the Docker Hub
3 | FROM python:3.11-slim
4 |
5 | # Set the working directory in the container
6 | WORKDIR /app
7 |
8 | # Copy the requirements file into the container
9 | COPY requirements.txt .
10 |
11 | # Install the dependencies specified in the requirements file
12 | RUN pip install --no-cache-dir -r requirements.txt
13 |
14 | # Copy the current directory contents into the container at /app
15 | COPY src/ /app/src
16 |
17 | # Set environment variables for MySQL (these can be overwritten with `docker run -e`)
18 | ENV MYSQL_HOST=localhost
19 | ENV MYSQL_PORT=3306
20 | ENV MYSQL_USER=root
21 | ENV MYSQL_PASSWORD=123456
22 | ENV MYSQL_DATABASE=a_llm
23 | ENV MYSQL_ROLE=admin
24 | ENV PYTHONPATH=/app/src
25 |
26 | # Command to run the server
27 | CMD ["python", "-m", "mysql_mcp_server_pro.server"]
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/handles/get_db_health_running.py:
--------------------------------------------------------------------------------
```python
1 | from typing import Dict, Any, Sequence
2 |
3 | from mcp import Tool
4 | from mcp.types import TextContent
5 |
6 | from .base import BaseHandler
7 |
8 | from mysql_mcp_server_pro.handles import (
9 | ExecuteSQL
10 | )
11 |
12 | execute_sql = ExecuteSQL()
13 |
14 | class GetDBHealthRunning(BaseHandler):
15 | name = "get_db_health_running"
16 | description = (
17 | "获取当前mysql的健康状态(Analyze MySQL health status )"
18 | )
19 |
20 | def get_tool_description(self) -> Tool:
21 | return Tool(
22 | name=self.name,
23 | description=self.description,
24 | inputSchema={
25 | "type": "object",
26 | "properties": {
27 |
28 | }
29 | }
30 | )
31 |
32 | async def run_tool(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
33 | lock_result = await self.get_lock(arguments)
34 | processlist_result = await self.get_processlist(arguments)
35 | status_result = await self.get_status(arguments)
36 | trx_result = await self.get_trx(arguments)
37 |
38 |
39 | # 合并结果
40 | combined_result = []
41 | combined_result.extend(processlist_result)
42 | combined_result.extend(lock_result)
43 | combined_result.extend(trx_result)
44 | combined_result.extend(status_result)
45 |
46 | return combined_result
47 |
48 | """
49 | 获取连接情况
50 | """
51 | async def get_processlist(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
52 | try:
53 | sql = "SHOW FULL PROCESSLIST;SHOW VARIABLES LIKE 'max_connections';"
54 |
55 | return await execute_sql.run_tool({"query": sql})
56 | except Exception as e:
57 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
58 |
59 | """
60 | 获取运行情况
61 | """
62 | async def get_status(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
63 | try:
64 | sql = "SHOW ENGINE INNODB STATUS;"
65 |
66 | return await execute_sql.run_tool({"query": sql})
67 | except Exception as e:
68 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
69 |
70 | """
71 | 获取事务情况
72 | """
73 | async def get_trx(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
74 | try:
75 | sql = "SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;"
76 | return await execute_sql.run_tool({"query": sql})
77 | except Exception as e:
78 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
79 |
80 |
81 | """
82 | 获取锁情况
83 | """
84 | async def get_lock(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
85 | try:
86 | sql = "SHOW OPEN TABLES WHERE In_use > 0;select * from information_schema.innodb_locks;select * from information_schema.innodb_lock_waits;"
87 | sql += "select * from performance_schema.data_lock_waits;"
88 | sql += "select * from performance_schema.data_locks;"
89 |
90 |
91 | return await execute_sql.run_tool({"query": sql})
92 | except Exception as e:
93 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/handles/get_db_health_index_usage.py:
--------------------------------------------------------------------------------
```python
1 | from typing import Dict, Any, Sequence
2 |
3 | from mcp import Tool
4 | from mcp.types import TextContent
5 |
6 | from .base import BaseHandler
7 | from mysql_mcp_server_pro.config import get_db_config
8 | from mysql_mcp_server_pro.handles import (
9 | ExecuteSQL
10 | )
11 |
12 | execute_sql = ExecuteSQL()
13 |
14 | class GetDBHealthIndexUsage(BaseHandler):
15 | name = "get_db_health_index_usage"
16 | description = (
17 | "获取当前连接的mysql库的索引使用情况,包含冗余索引情况、性能较差的索引情况、未使用索引且查询时间大于30秒top5情况"
18 | + "(Get the index usage of the currently connected mysql database, including redundant index situations, "
19 | + "poorly performing index situations, and the top 5 unused index situations with query times greater than 30 seconds)"
20 | )
21 |
22 | def get_tool_description(self) -> Tool:
23 | return Tool(
24 | name=self.name,
25 | description=self.description,
26 | inputSchema={
27 | "type": "object",
28 | "properties": {
29 |
30 | }
31 | }
32 | )
33 |
34 | async def run_tool(self, arguments: Dict[str, Any]) -> Sequence[TextContent]:
35 | config = get_db_config()
36 |
37 | count_zero_result = await self.get_count_zero(arguments,config)
38 | max_time_result = await self.get_max_timer(arguments,config)
39 | not_used_index_result = await self.get_not_used_index(arguments,config)
40 |
41 |
42 | # 合并结果
43 | combined_result = []
44 | combined_result.extend(count_zero_result)
45 | combined_result.extend(max_time_result)
46 | combined_result.extend(not_used_index_result)
47 |
48 |
49 | return combined_result
50 |
51 | """
52 | 获取冗余索引情况
53 | """
54 | async def get_count_zero(self, arguments: Dict[str, Any], config) -> Sequence[TextContent]:
55 | try:
56 | sql = "SELECT object_name,index_name,count_star from performance_schema.table_io_waits_summary_by_index_usage "
57 | sql += f"WHERE object_schema = '{config['database']}' and count_star = 0 AND sum_timer_wait = 0 ;"
58 |
59 | return await execute_sql.run_tool({"query": sql})
60 | except Exception as e:
61 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
62 |
63 |
64 | """
65 | 获取性能较差的索引情况
66 | """
67 | async def get_max_timer(self, arguments: Dict[str, Any], config) -> Sequence[TextContent]:
68 | try:
69 | sql = "SELECT object_schema,object_name,index_name,(max_timer_wait / 1000000000000) max_timer_wait "
70 | sql += f"FROM performance_schema.table_io_waits_summary_by_index_usage where object_schema = '{config['database']}' "
71 | sql += "and index_name is not null ORDER BY max_timer_wait DESC;"
72 |
73 | return await execute_sql.run_tool({"query": sql})
74 | except Exception as e:
75 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
76 |
77 | """
78 | 获取未使用索引查询时间大于30秒的top5情况
79 | """
80 | async def get_not_used_index(self, arguments: Dict[str, Any], config) -> Sequence[TextContent]:
81 | try:
82 | sql = "SELECT object_schema,object_name, (max_timer_wait / 1000000000000) max_timer_wait "
83 | sql += f"FROM performance_schema.table_io_waits_summary_by_index_usage where object_schema = '{config['database']}' "
84 | sql += "and index_name IS null and max_timer_wait > 30000000000000 ORDER BY max_timer_wait DESC limit 5;"
85 |
86 | return await execute_sql.run_tool({"query": sql})
87 | except Exception as e:
88 | return [TextContent(type="text", text=f"执行查询时出错: {str(e)}")]
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/config/event_store.py:
--------------------------------------------------------------------------------
```python
1 | """
2 | In-memory event store for demonstrating resumability functionality.
3 |
4 | This is a simple implementation intended for examples and testing,
5 | not for production use where a persistent storage solution would be more appropriate.
6 | """
7 |
8 | import logging
9 | from collections import deque
10 | from dataclasses import dataclass
11 | from uuid import uuid4
12 |
13 | from mcp.server.streamable_http import (
14 | EventCallback,
15 | EventId,
16 | EventMessage,
17 | EventStore,
18 | StreamId,
19 | )
20 | from mcp.types import JSONRPCMessage
21 |
22 | logger = logging.getLogger(__name__)
23 |
24 |
25 | @dataclass
26 | class EventEntry:
27 | """
28 | Represents an event entry in the event store.
29 | """
30 |
31 | event_id: EventId
32 | stream_id: StreamId
33 | message: JSONRPCMessage
34 |
35 |
36 | class InMemoryEventStore(EventStore):
37 | """
38 | Simple in-memory implementation of the EventStore interface for resumability.
39 | This is primarily intended for examples and testing, not for production use
40 | where a persistent storage solution would be more appropriate.
41 |
42 | This implementation keeps only the last N events per stream for memory efficiency.
43 | """
44 |
45 | def __init__(self, max_events_per_stream: int = 100):
46 | """Initialize the event store.
47 |
48 | Args:
49 | max_events_per_stream: Maximum number of events to keep per stream
50 | """
51 | self.max_events_per_stream = max_events_per_stream
52 | # for maintaining last N events per stream
53 | self.streams: dict[StreamId, deque[EventEntry]] = {}
54 | # event_id -> EventEntry for quick lookup
55 | self.event_index: dict[EventId, EventEntry] = {}
56 |
57 | async def store_event(
58 | self, stream_id: StreamId, message: JSONRPCMessage
59 | ) -> EventId:
60 | """Stores an event with a generated event ID."""
61 | event_id = str(uuid4())
62 | event_entry = EventEntry(
63 | event_id=event_id, stream_id=stream_id, message=message
64 | )
65 |
66 | # Get or create deque for this stream
67 | if stream_id not in self.streams:
68 | self.streams[stream_id] = deque(maxlen=self.max_events_per_stream)
69 |
70 | # If deque is full, the oldest event will be automatically removed
71 | # We need to remove it from the event_index as well
72 | if len(self.streams[stream_id]) == self.max_events_per_stream:
73 | oldest_event = self.streams[stream_id][0]
74 | self.event_index.pop(oldest_event.event_id, None)
75 |
76 | # Add new event
77 | self.streams[stream_id].append(event_entry)
78 | self.event_index[event_id] = event_entry
79 |
80 | return event_id
81 |
82 | async def replay_events_after(
83 | self,
84 | last_event_id: EventId,
85 | send_callback: EventCallback,
86 | ) -> StreamId | None:
87 | """Replays events that occurred after the specified event ID."""
88 | if last_event_id not in self.event_index:
89 | logger.warning(f"Event ID {last_event_id} not found in store")
90 | return None
91 |
92 | # Get the stream and find events after the last one
93 | last_event = self.event_index[last_event_id]
94 | stream_id = last_event.stream_id
95 | stream_events = self.streams.get(last_event.stream_id, deque())
96 |
97 | # Events in deque are already in chronological order
98 | found_last = False
99 | for event in stream_events:
100 | if found_last:
101 | await send_callback(EventMessage(event.message, event.event_id))
102 | elif event.event_id == last_event_id:
103 | found_last = True
104 |
105 | return stream_id
106 |
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/oauth/routes.py:
--------------------------------------------------------------------------------
```python
1 | import os
2 | import hashlib
3 | import time
4 |
5 | from starlette.responses import JSONResponse, HTMLResponse
6 | from starlette.requests import Request
7 | from pathlib import Path
8 |
9 | from .token_handler import TokenHandler
10 | from .config import oauth_config
11 |
12 |
13 | async def login_page(request: Request) -> HTMLResponse:
14 | """返回登录页面"""
15 | try:
16 | templates_dir = Path(__file__).parent.parent / "templates"
17 | login_html = templates_dir / "login.html"
18 |
19 | with open(login_html, "r", encoding="utf-8") as f:
20 | content = f.read()
21 |
22 | return HTMLResponse(content)
23 | except Exception as e:
24 | return HTMLResponse(
25 | content=f"<h1>错误</h1><p>加载登录页面失败: {str(e)}</p>",
26 | status_code=500
27 | )
28 |
29 | async def login(request: Request) -> JSONResponse:
30 | """
31 | OAuth 2.0密码模式端点
32 |
33 | 请求格式：
34 | POST /mcp/auth/login
35 | Content-Type: application/json
36 | {
37 | "grant_type": "password",
38 | "username": "用户名",
39 | "password": "密码",
40 | "client_id": "客户端ID",
41 | "client_secret": "客户端密钥"
42 | }
43 | """
44 | # 检查 Accept 头部
45 | accept_header = request.headers.get("accept", "*/*")
46 | if accept_header != "*/*" and "application/json" not in accept_header:
47 | return JSONResponse(
48 | {"error": "not_acceptable", "error_description": "Client must accept application/json response"},
49 | status_code=406
50 | )
51 |
52 | try:
53 | data = await request.json()
54 |
55 | # 验证授权类型
56 | grant_type = data.get("grant_type")
57 | if grant_type not in oauth_config.GRANT_TYPES:
58 | return JSONResponse(
59 | {"error": "unsupported_grant_type"},
60 | status_code=400
61 | )
62 |
63 | # 验证客户端凭据
64 | client_id = data.get("client_id")
65 | client_secret = data.get("client_secret")
66 |
67 | if not client_id or not client_secret:
68 | return JSONResponse(
69 | {"error": "invalid_client"},
70 | status_code=401
71 | )
72 |
73 | if client_id != oauth_config.CLIENT_ID or client_secret != oauth_config.CLIENT_SECRET:
74 | return JSONResponse(
75 | {"error": "invalid_client"},
76 | status_code=401
77 | )
78 |
79 | if grant_type == "password":
80 | username = data.get("username")
81 | #password = data.get("password")
82 |
83 | encrypted_password = data.get("password") # 从前端接收的加密密码
84 |
85 | if not username or not encrypted_password:
86 | return JSONResponse(
87 | {"error": "invalid_request", "error_description": "Missing username or password"},
88 | status_code=400
89 | )
90 |
91 | # 获取时间戳和盐值
92 | timestamp = request.headers.get("X-Timestamp")
93 | salt = request.headers.get("X-Salt")
94 |
95 | if not timestamp or not salt:
96 | return JSONResponse(
97 | {"error": "invalid_request", "error_description": "Missing security parameters"},
98 | status_code=400
99 | )
100 |
101 | # 验证时间戳是否在有效期内（5分钟）
102 | try:
103 | ts = int(timestamp) / 1000 # 转换为秒
104 | current_time = time.time()
105 | if current_time - ts > 300: # 5分钟过期
106 | return JSONResponse(
107 | {"error": "invalid_request", "error_description": "Request expired"},
108 | status_code=400
109 | )
110 | except (ValueError, TypeError):
111 | return JSONResponse(
112 | {"error": "invalid_request", "error_description": "Invalid timestamp"},
113 | status_code=400
114 | )
115 |
116 | # 验证密码
117 | # 1. 使用与前端相同的加密方式验证
118 | # 第一次哈希：密码 + 盐
119 | first_hash = hashlib.sha256((os.getenv("OAUTH_USER_PASSWORD", "admin") + salt).encode()).hexdigest()
120 | # 第二次哈希：第一次哈希结果 + 时间戳
121 | expected_hash = hashlib.sha256((first_hash + timestamp).encode()).hexdigest()
122 |
123 |
124 | # 这里应该添加实际的用户验证逻辑
125 | if username == os.getenv("OAUTH_USER_NAME", "admin") and encrypted_password == expected_hash :
126 | # 生成令牌
127 | access_token, refresh_token, access_expires, refresh_expires = TokenHandler.create_tokens(
128 | user_id="1", # 这里应该是实际的用户ID
129 | username=username
130 | )
131 |
132 | # 返回标准的OAuth2.0响应
133 | return JSONResponse(
134 | TokenHandler.create_token_response(
135 | access_token,
136 | refresh_token,
137 | access_expires,
138 | refresh_expires
139 | )
140 | )
141 |
142 | return JSONResponse(
143 | {"error": "invalid_grant"},
144 | status_code=401
145 | )
146 |
147 | elif grant_type == "refresh_token":
148 | refresh_token = data.get("refresh_token")
149 | if not refresh_token:
150 | return JSONResponse(
151 | {"error": "invalid_request"},
152 | status_code=400
153 | )
154 |
155 | # 验证刷新令牌
156 | payload = TokenHandler.verify_token(refresh_token)
157 | if not payload or payload.get("type") != "refresh_token":
158 | return JSONResponse(
159 | {"error": "invalid_grant"},
160 | status_code=401
161 | )
162 |
163 | # 生成新的令牌
164 | access_token, new_refresh_token, access_expires, refresh_expires = TokenHandler.create_tokens(
165 | user_id=payload["sub"],
166 | username=payload["username"]
167 | )
168 |
169 | return JSONResponse(
170 | TokenHandler.create_token_response(
171 | access_token,
172 | new_refresh_token,
173 | access_expires,
174 | refresh_expires
175 | )
176 | )
177 |
178 | except Exception as e:
179 | return JSONResponse(
180 | {"error": "server_error", "error_description": str(e)},
181 | status_code=500
182 | )
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server_pro/templates/login.html:
--------------------------------------------------------------------------------
```html
1 | <!DOCTYPE html>
2 | <html lang="zh">
3 | <head>
4 | <meta charset="UTF-8">
5 | <meta name="viewport" content="width=device-width, initial-scale=1.0">
6 | <title>MySQL MCP Server Pro - 登录</title>
7 | <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600&display=swap" rel="stylesheet">
8 | <!-- 添加 crypto-js 库 -->
9 | <script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js"></script>
10 | <style>
11 | :root {
12 | --primary-color: #2196F3;
13 | --primary-dark: #1976D2;
14 | --text-primary: #212121;
15 | --text-secondary: #757575;
16 | --background: #f5f5f5;
17 | --surface: #FFFFFF;
18 | --error: #F44336;
19 | --success: #4CAF50;
20 | }
21 |
22 | * {
23 | margin: 0;
24 | padding: 0;
25 | box-sizing: border-box;
26 | }
27 |
28 | body {
29 | font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif;
30 | background: var(--background);
31 | color: var(--text-primary);
32 | line-height: 1.6;
33 | min-height: 100vh;
34 | display: flex;
35 | justify-content: center;
36 | align-items: center;
37 | }
38 |
39 | /* 登录表单样式 */
40 | .login-container {
41 | background: white;
42 | padding: 2rem;
43 | border-radius: 8px;
44 | box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
45 | width: 100%;
46 | max-width: 400px;
47 | }
48 |
49 | .header {
50 | text-align: center;
51 | margin-bottom: 2rem;
52 | }
53 |
54 | .title {
55 | font-size: 1.5rem;
56 | color: var(--text-primary);
57 | margin-bottom: 0.5rem;
58 | }
59 |
60 | .author {
61 | color: var(--text-secondary);
62 | font-size: 0.9rem;
63 | }
64 |
65 | .form-group {
66 | margin-bottom: 1.5rem;
67 | }
68 |
69 | label {
70 | display: block;
71 | margin-bottom: 0.5rem;
72 | color: var(--text-primary);
73 | font-weight: 500;
74 | }
75 |
76 | input {
77 | width: 100%;
78 | padding: 0.75rem;
79 | border: 1px solid #ddd;
80 | border-radius: 6px;
81 | font-size: 0.95rem;
82 | }
83 |
84 | input:focus {
85 | outline: none;
86 | border-color: var(--primary-color);
87 | }
88 |
89 | .submit-button {
90 | width: 100%;
91 | padding: 0.75rem;
92 | background: var(--primary-color);
93 | color: white;
94 | border: none;
95 | border-radius: 6px;
96 | font-size: 1rem;
97 | font-weight: 500;
98 | cursor: pointer;
99 | }
100 |
101 | .submit-button:hover {
102 | background: var(--primary-dark);
103 | }
104 |
105 | .error-message {
106 | color: var(--error);
107 | margin-top: 1rem;
108 | text-align: center;
109 | display: none;
110 | }
111 |
112 | /* Token展示界面样式 */
113 | .token-container {
114 | display: none;
115 | position: fixed;
116 | top: 0;
117 | left: 0;
118 | right: 0;
119 | bottom: 0;
120 | background: var(--background);
121 | padding: 1.5rem;
122 | }
123 |
124 | .token-content {
125 | max-width: 900px;
126 | margin: 0 auto;
127 | }
128 |
129 | .token-header {
130 | margin-bottom: 1rem;
131 | text-align: center;
132 | }
133 |
134 | .token-header h1 {
135 | font-size: 1.25rem;
136 | margin-bottom: 0.25rem;
137 | }
138 |
139 | .token-header .subtitle {
140 | font-size: 0.9rem;
141 | color: var(--text-secondary);
142 | }
143 |
144 | .token-section {
145 | background: white;
146 | padding: 1.25rem;
147 | border-radius: 8px;
148 | box-shadow: 0 2px 4px rgba(0,0,0,0.05);
149 | margin-bottom: 0.75rem;
150 | }
151 |
152 | .token-section h2 {
153 | font-size: 1rem;
154 | margin-bottom: 0.75rem;
155 | display: flex;
156 | align-items: center;
157 | gap: 0.5rem;
158 | }
159 |
160 | .token-value {
161 | font-family: 'Monaco', 'Consolas', monospace;
162 | word-break: break-all;
163 | background: var(--background);
164 | padding: 0.75rem;
165 | border-radius: 6px;
166 | border: 1px solid #ddd;
167 | font-size: 0.85rem;
168 | margin-bottom: 0.5rem;
169 | line-height: 1.3;
170 | }
171 |
172 | .token-info {
173 | margin: 0.5rem 0;
174 | color: var(--text-secondary);
175 | font-size: 0.85rem;
176 | }
177 |
178 | .token-info p {
179 | margin: 0.25rem 0;
180 | display: flex;
181 | align-items: center;
182 | gap: 0.5rem;
183 | }
184 |
185 | .copy-button {
186 | background: var(--success);
187 | color: white;
188 | border: none;
189 | padding: 0.5rem 1rem;
190 | border-radius: 4px;
191 | cursor: pointer;
192 | font-size: 0.85rem;
193 | display: inline-flex;
194 | align-items: center;
195 | gap: 0.5rem;
196 | }
197 |
198 | .copy-button:hover {
199 | opacity: 0.9;
200 | }
201 |
202 | .code-block {
203 | background: #1E1E1E;
204 | color: #E0E0E0;
205 | padding: 0.75rem;
206 | border-radius: 6px;
207 | overflow-x: auto;
208 | font-family: 'Monaco', 'Consolas', monospace;
209 | font-size: 0.8rem;
210 | line-height: 1.3;
211 | margin: 0.5rem 0;
212 | }
213 |
214 | .github-card {
215 | text-align: center;
216 | padding: 0.75rem;
217 | background: linear-gradient(135deg, #2196F3 0%, #1976D2 100%);
218 | color: white;
219 | border-radius: 6px;
220 | margin-top: 0.5rem;
221 | font-size: 0.85rem;
222 | }
223 |
224 | .github-card a {
225 | color: white;
226 | text-decoration: none;
227 | font-weight: 500;
228 | display: inline-flex;
229 | align-items: center;
230 | gap: 0.25rem;
231 | }
232 |
233 | .github-card a:hover {
234 | text-decoration: underline;
235 | }
236 | </style>
237 | </head>
238 | <body>
239 | <!-- 登录表单 -->
240 | <div class="login-container">
241 | <div class="header">
242 | <h1 class="title">MySQL MCP Server Pro</h1>
243 | <div class="author">by wenb1n</div>
244 | </div>
245 | <form id="loginForm">
246 | <div class="form-group">
247 | <label for="username">用户名</label>
248 | <input type="text" id="username" name="username" required>
249 | </div>
250 | <div class="form-group">
251 | <label for="password">密码</label>
252 | <input type="password" id="password" name="password" required>
253 | </div>
254 | <button type="submit" class="submit-button">登录</button>
255 | </form>
256 | <div id="errorMessage" class="error-message"></div>
257 | </div>
258 |
259 | <!-- Token展示界面 -->
260 | <div id="tokenContainer" class="token-container">
261 | <div class="token-content">
262 | <div class="token-header">
263 | <h1>MySQL MCP Server Pro</h1>
264 | <div class="subtitle">by wenb1n</div>
265 | </div>
266 |
267 | <div class="token-section">
268 | <h2>🔑 访问令牌 (Access Token)</h2>
269 | <div id="tokenValue" class="token-value"></div>
270 | <div class="token-info">
271 | <p>⏰ 过期时间：<span id="expireTime"></span></p>
272 | </div>
273 | <button id="copyButton" class="copy-button">📋 复制Token</button>
274 | </div>
275 |
276 | <div class="token-section">
277 | <h2>🔄 刷新令牌 (Refresh Token)</h2>
278 | <div id="refreshTokenValue" class="token-value"></div>
279 | <div class="token-info">
280 | <p>使用刷新令牌获取新的访问令牌:</p>
281 | </div>
282 | <div class="code-block">
283 | <pre id="refreshTokenExample">curl -X POST http://localhost:3000/mcp/auth/login \
284 | -H "Content-Type: application/json" \
285 | -d '{
286 | "grant_type": "refresh_token",
287 | "refresh_token": "your-refresh-token",
288 | "client_id": "mysql-mcp-client",
289 | "client_secret": "mysql-mcp-secret"
290 | }'</pre>
291 | </div>
292 | </div>
293 |
294 | <div class="github-card">
295 | <a href="https://github.com/wenb1n-dev/mysql_mcp_server_pro" target="_blank">
296 | ⭐️ 如果觉得好用，请帮忙点个 Star 支持一下！
297 | </a>
298 | </div>
299 | </div>
300 | </div>
301 |
302 | <script>
303 | // 生成随机盐值
304 | function generateSalt(length = 16) {
305 | const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
306 | let salt = '';
307 | for (let i = 0; i < length; i++) {
308 | salt += chars.charAt(Math.floor(Math.random() * chars.length));
309 | }
310 | return salt;
311 | }
312 |
313 | // 加密密码
314 | function encryptPassword(password, salt, timestamp) {
315 | // 第一次哈希：密码 + 盐
316 | const firstHash = CryptoJS.SHA256(password + salt).toString();
317 | // 第二次哈希：第一次哈希结果 + 时间戳
318 | const finalHash = CryptoJS.SHA256(firstHash + timestamp).toString();
319 | return finalHash;
320 | }
321 |
322 | document.getElementById('loginForm').addEventListener('submit', async (e) => {
323 | e.preventDefault();
324 | const username = document.getElementById('username').value;
325 | const password = document.getElementById('password').value;
326 |
327 | try {
328 | // 生成安全参数
329 | const salt = generateSalt();
330 | const timestamp = Date.now().toString();
331 | const encryptedPassword = encryptPassword(password, salt, timestamp);
332 |
333 | const response = await fetch('/mcp/auth/login', {
334 | method: 'POST',
335 | headers: {
336 | 'Content-Type': 'application/json',
337 | 'Accept': 'application/json',
338 | 'X-Timestamp': timestamp, // 添加时间戳头
339 | 'X-Salt': salt, // 添加盐值头
340 | },
341 | body: JSON.stringify({
342 | grant_type: 'password',
343 | username: username,
344 | password: encryptedPassword, // 发送加密后的密码
345 | client_id: 'mysql-mcp-client',
346 | client_secret: 'mysql-mcp-secret'
347 | })
348 | });
349 |
350 | const data = await response.json();
351 |
352 | if (response.ok) {
353 | // 显示token信息
354 | const tokenContainer = document.getElementById('tokenContainer');
355 | const tokenValue = document.getElementById('tokenValue');
356 | const expireTime = document.getElementById('expireTime');
357 | const refreshTokenValue = document.getElementById('refreshTokenValue');
358 |
359 | tokenValue.textContent = data.access_token;
360 | expireTime.innerHTML = `
361 | <div style="display: flex; flex-direction: column; gap: 0.25rem;">
362 | <div>🔑 访问令牌过期时间: ${data.expire_time}</div>
363 | <div>🔄 刷新令牌过期时间: ${data.refresh_token_expire_time}</div>
364 | </div>
365 | `;
366 | refreshTokenValue.textContent = data.refresh_token;
367 |
368 | // 更新示例代码中的刷新令牌
369 | const example = document.getElementById('refreshTokenExample');
370 | example.textContent = example.textContent.replace('your-refresh-token', data.refresh_token);
371 |
372 | tokenContainer.style.display = 'block';
373 | document.querySelector('.login-container').style.display = 'none';
374 | } else {
375 | const errorMessage = document.getElementById('errorMessage');
376 | errorMessage.textContent = data.error_description || data.error || '登录失败';
377 | errorMessage.style.display = 'block';
378 | }
379 | } catch (error) {
380 | const errorMessage = document.getElementById('errorMessage');
381 | errorMessage.textContent = '网络错误，请稍后重试';
382 | errorMessage.style.display = 'block';
383 | }
384 | });
385 |
386 | document.getElementById('copyButton').addEventListener('click', function() {
387 | const token = document.getElementById('tokenValue').textContent;
388 | if (token) {
389 | navigator.clipboard.writeText(token).then(() => {
390 | this.textContent = '✓ 已复制';
391 | setTimeout(() => {
392 | this.innerHTML = '📋 复制Token';
393 | }, 2000);
394 | }).catch(() => {
395 | alert('复制失败，请手动复制');
396 | });
397 | }
398 | });
399 | </script>
400 | </body>
401 | </html>
```