Skip to content
Back to search

mysql_mcp_server_pro

Model Context Protocol (MCP) server that supports secure interaction with MySQL databases and has anomaly analysis capabilities.更加牛逼!更加好用!不仅止于mysql的增删改查功能; 还包含了数据库异常分析能力;且便于开发者们进行个性化的工具扩展

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today
Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/wenb1n-dev--mysql-mcp-server-pro/badge.svg)](/listings/wenb1n-dev--mysql-mcp-server-pro)

HTML

<a href="/listings/wenb1n-dev--mysql-mcp-server-pro"><img src="/api/listings/wenb1n-dev--mysql-mcp-server-pro/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

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_pro --mode sse --envfile /path/to/.env

## Streamable Http mode (default)
mysql_mcp_server_pro --envfile /path/to/.env

# Streamable Http  oauth Authentication
mysql_mcp_server_pro --oauth true

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 or use --envfile parameter to specify the path - You can also set these variables directly in your environment - Make sure the database configuration is correct and can connect

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 321 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

  1. [![简体中文](https://img.shields.io/badge/简体中文-点击查看-orange)](README-zh.md)
  2. [![English](https://img.shields.io/badge/English-Click-yellow)](README.md)
  3. [![MseeP.ai Security Assessment Badge](https://mseep.net/mseep-audited.png)](https://mseep.ai/app/wenb1n-dev-mysql-mcp-server-pro)
  4. [![MCPHub](https://img.shields.io/badge/mcphub-audited-blue)](https://mcphub.com/mcp-servers/wenb1n-dev/mysql_mcp_server_pro)
  5. # mcp_mysql_server_pro
  6. ## Introduction
  7. 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.
  8. - Supports all Model Context Protocol (MCP) transfer modes (STDIO, SSE, Streamable Http)
  9. - Supports OAuth2.0
  10. - Supports multiple SQL execution, separated by ";"
  11. - Supports querying database table names and fields based on table comments
  12. - Supports SQL execution plan analysis
  13. - Supports Chinese field to pinyin conversion
  14. - Supports table lock analysis
  15. - Supports database health status analysis
  16. - Supports permission control with three roles: readonly, writer, and admin
  17. ```
  18. "readonly": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN"], # Read-only permissions
  19. "writer": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE"], # Read-write permissions
  20. "admin": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE",
  21. "CREATE", "ALTER", "DROP", "TRUNCATE"] # Administrator permissions
  22. ```
  23. - Supports prompt template invocation
  24. ## Tool List
  25. | Tool Name | Description |
  26. |----------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  27. | execute_sql | SQL execution tool that can execute ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE", "CREATE", "ALTER", "DROP", "TRUNCATE"] commands based on permission configuration |
  28. | get_chinese_initials | Convert Chinese field names to pinyin initials |
  29. | get_db_health_running | Analyze MySQL health status (connection status, transaction status, running status, lock status detection) |
  30. | get_table_desc | Search for table structures in the database based on table names, supporting multi-table queries |
  31. | get_table_index | Search for table indexes in the database based on table names, supporting multi-table queries |
  32. | get_table_lock | Check if there are row-level locks or table-level locks in the current MySQL server |
  33. | get_table_name | Search for table names in the database based on table comments and descriptions |
  34. | 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 |
  35. | 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. |
  36. | 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) |
  37. ## Prompt List
  38. | Prompt Name | Description |
  39. |---------------------------|---------------------------------------------------------------------------------------------------------------------------------------|
  40. | analyzing-mysql-prompt | This is a prompt for analyzing MySQL-related issues |
  41. | 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 |
  42. ## Usage Instructions
  43. ### Installation and Configuration
  44. 1. Install Package
  45. ```bash
  46. pip install mysql_mcp_server_pro
  47. ```
  48. 2. Configure Environment Variables
  49. Create a `.env` file with the following content:
  50. ```bash
  51. # MySQL Database Configuration
  52. MYSQL_HOST=localhost
  53. MYSQL_PORT=3306
  54. MYSQL_USER=your_username
  55. MYSQL_PASSWORD=your_password
  56. MYSQL_DATABASE=your_database
  57. # Optional, default is 'readonly'. Available values: readonly, writer, admin
  58. MYSQL_ROLE=readonly
  59. ```
  60. 3. Run Service
  61. ```bash
  62. # SSE mode
  63. mysql_mcp_server_pro --mode sse --envfile /path/to/.env
  64. ## Streamable Http mode (default)
  65. mysql_mcp_server_pro --envfile /path/to/.env
  66. # Streamable Http oauth Authentication
  67. mysql_mcp_server_pro --oauth true
  68. ```
  69. 4. mcp client
  70. go to see see "Use uv to start the service"
  71. ^_^
  72. Note:
  73. - The `.env` file should be placed in the directory where you run the command or use --envfile parameter to specify the path
  74. - You can also set these variables directly in your environment
  75. - Make sure the database configuration is correct and can connect
  76. ### Run with uvx, Client Configuration
  77. - 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.
  78. ```json
  79. {
  80. "mcpServers": {
  81. "mysql": {
  82. "command": "uvx",
  83. "args": [
  84. "--from",
  85. "mysql_mcp_server_pro",
  86. "mysql_mcp_server_pro",
  87. "--mode",
  88. "stdio"
  89. ],
  90. "env": {
  91. "MYSQL_HOST": "192.168.x.xxx",
  92. "MYSQL_PORT": "3306",
  93. "MYSQL_USER": "root",
  94. "MYSQL_PASSWORD": "root",
  95. "MYSQL_DATABASE": "a_llm",
  96. "MYSQL_ROLE": "admin"
  97. }
  98. }
  99. }
  100. }
  101. ```
  102. ### Local Development with Streamable Http mode
  103. - Use uv to start the service
  104. Add the following content to your mcp client tools, such as cursor, cline, etc.
  105. mcp json as follows:
  106. ```
  107. {
  108. "mcpServers": {
  109. "mysql_mcp_server_pro": {
  110. "name": "mysql_mcp_server_pro",
  111. "type": "streamableHttp",
  112. "description": "",
  113. "isActive": true,
  114. "url": "http://localhost:3000/mcp/"
  115. }
  116. }
  117. }
  118. ```
  119. Modify the .env file content to update the database connection information with your database details:
  120. ```
  121. # MySQL Database Configuration
  122. MYSQL_HOST=192.168.xxx.xxx
  123. MYSQL_PORT=3306
  124. MYSQL_USER=root
  125. MYSQL_PASSWORD=root
  126. MYSQL_DATABASE=a_llm
  127. MYSQL_ROLE=admin
  128. ```
  129. Start commands:
  130. ```
  131. # Download dependencies
  132. uv sync
  133. # Start
  134. uv run -m mysql_mcp_server_pro.server
  135. # Custom env file location
  136. uv run -m mysql_mcp_server_pro.server --envfile /path/to/.env
  137. # oauth Authentication
  138. uv run -m mysql_mcp_server_pro.server --oauth true
  139. ```
  140. ### Local Development with SSE Mode
  141. - Use uv to start the service
  142. Add the following content to your mcp client tools, such as cursor, cline, etc.
  143. mcp json as follows:
  144. ```
  145. {
  146. "mcpServers": {
  147. "mysql_mcp_server_pro": {
  148. "name": "mysql_mcp_server_pro",
  149. "description": "",
  150. "isActive": true,
  151. "url": "http://localhost:9000/sse"
  152. }
  153. }
  154. }
  155. ```
  156. Modify the .env file content to update the database connection information with your database details:
  157. ```
  158. # MySQL Database Configuration
  159. MYSQL_HOST=192.168.xxx.xxx
  160. MYSQL_PORT=3306
  161. MYSQL_USER=root
  162. MYSQL_PASSWORD=root
  163. MYSQL_DATABASE=a_llm
  164. MYSQL_ROLE=admin
  165. ```
  166. Start commands:
  167. ```
  168. # Download dependencies
  169. uv sync
  170. # Start
  171. uv run -m mysql_mcp_server_pro.server --mode sse
  172. # Custom env file location
  173. uv run -m mysql_mcp_server_pro.server --mode sse --envfile /path/to/.env
  174. ```
  175. ### Local Development with STDIO Mode
  176. Add the following content to your mcp client tools, such as cursor, cline, etc.
  177. mcp json as follows:
  178. ```
  179. {
  180. "mcpServers": {
  181. "operateMysql": {
  182. "isActive": true,
  183. "name": "operateMysql",
  184. "command": "uv",
  185. "args": [
  186. "--directory",
  187. "/Volumes/mysql_mcp_server_pro/src/mysql_mcp_server_pro", # Replace this with your project path
  188. "run",
  189. "-m",
  190. "mysql_mcp_server_pro.server",
  191. "--mode",
  192. "stdio"
  193. ],
  194. "env": {
  195. "MYSQL_HOST": "localhost",
  196. "MYSQL_PORT": "3306",
  197. "MYSQL_USER": "root",
  198. "MYSQL_PASSWORD": "123456",
  199. "MYSQL_DATABASE": "a_llm",
  200. "MYSQL_ROLE": "admin"
  201. }
  202. }
  203. }
  204. }
  205. ```
  206. ## Custom Tool Extensions
  207. 1. Add a new tool class in the handles package, inherit from BaseHandler, and implement get_tool_description and run_tool methods
  208. 2. Import the new tool in __init__.py to make it available in the server
  209. ## OAuth2.0 Authentication
  210. 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.
  211. ```aiignore
  212. uv run -m mysql_mcp_server_pro.server --oauth true
  213. ```
  214. 2. Visit the authentication service at http://localhost:3000/login. Default username and password are configured in the env file.
  215. ![image](https://github.com/user-attachments/assets/ec8a629e-62f9-4b93-b3cc-442b3d2dc46f)
  216. 3. Copy the token and add it to the request headers, for example:
  217. ![image](https://github.com/user-attachments/assets/a5451e35-bddd-4e49-8aa9-a4178d30ec88)
  218. ```json
  219. {
  220. "mcpServers": {
  221. "mysql_mcp_server_pro": {
  222. "name": "mysql_mcp_server_pro",
  223. "type": "streamableHttp",
  224. "description": "",
  225. "isActive": true,
  226. "url": "http://localhost:3000/mcp/",
  227. "headers": {
  228. "authorization": "bearer TOKEN_VALUE"
  229. }
  230. }
  231. }
  232. }
  233. ```
  234. ## Examples
  235. 1. Create a new table and insert data, prompt format as follows:
  236. ```
  237. # Task
  238. Create an organizational structure table with the following structure: department name, department number, parent department, is valid.
  239. # Requirements
  240. - Table name: department
  241. - Common fields need indexes
  242. - Each field needs comments, table needs comment
  243. - Generate 5 real data records after creation
  244. ```
  245. ![image](https://github.com/user-attachments/assets/34118993-2a4c-4804-92f8-7fba9df89190)
  246. ![image](https://github.com/user-attachments/assets/f8299f01-c321-4dbf-b5de-13ba06885cc1)
  247. 2. Query data based on table comments, prompt as follows:
  248. ```
  249. Search for data with Department name 'Executive Office' in Department organizational structure table
  250. ```
  251. ![image](https://github.com/user-attachments/assets/dcf96603-548e-42d9-9217-78e569be7a8d)
  252. 3. Analyze slow SQL, prompt as follows:
  253. ```
  254. select * from t_jcsjzx_hjkq_cd_xsz_sk xsz
  255. left join t_jcsjzx_hjkq_jcd jcd on jcd.cddm = xsz.cddm
  256. Based on current index situation, review execution plan and provide optimization suggestions in markdown format, including table index status, execution details, and optimization recommendations
  257. ```
  258. 4. Analyze SQL deadlock issues, prompt as follows:
  259. ```
  260. update t_admin_rms_zzjg set sfyx = '0' where xh = '1' is stuck, please analyze the cause
  261. ```
  262. ![image](https://github.com/user-attachments/assets/25bca1cd-854c-4591-ac6e-32d464b12066)
  263. 5. Analyze the health status prompt as follows
  264. ```
  265. Check the current health status of MySQL
  266. ```
  267. ![image](https://github.com/user-attachments/assets/1f221ab8-59bf-402c-a15a-ec3eba1eea59)

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.