# Directory Structure
```
├── .gitignore
├── Dockerfile
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── smithery.yaml
├── src
│ └── index.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2022",
4 | "module": "Node16",
5 | "moduleResolution": "Node16",
6 | "outDir": "./build",
7 | "rootDir": "./src",
8 | "strict": true,
9 | "esModuleInterop": true,
10 | "skipLibCheck": true,
11 | "forceConsistentCasingInFileNames": true
12 | },
13 | "include": ["src/**/*"],
14 | "exclude": ["node_modules"]
15 | }
16 |
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | # Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
2 | FROM node:lts-alpine
3 |
4 | WORKDIR /app
5 |
6 | # Copy package.json and package-lock.json
7 | COPY package*.json ./
8 |
9 | # Install dependencies without running prepare scripts
10 | RUN npm install --ignore-scripts
11 |
12 | # Copy the rest of the repository
13 | COPY . .
14 |
15 | # Build the project using TypeScript (tsconfig.json is now available)
16 | RUN npm run build
17 |
18 | # Expose any necessary ports (if applicable)
19 | # EXPOSE 3000
20 |
21 | # Set the entrypoint command to run the MCP server
22 | CMD ["node", "build/index.js"]
23 |
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "@malove86/mcp-mysql-server",
3 | "version": "0.2.4",
4 | "description": "A Model Context Protocol server for MySQL database operations",
5 | "type": "module",
6 | "bin": {
7 | "mcp-mysql": "./build/index.js"
8 | },
9 | "files": [
10 | "build"
11 | ],
12 | "scripts": {
13 | "build": "tsc && node -e \"require('fs').chmodSync('build/index.js', '755')\"",
14 | "prepare": "npm run build",
15 | "watch": "tsc --watch",
16 | "inspector": "npx @modelcontextprotocol/inspector build/index.js"
17 | },
18 | "keywords": [
19 | "mcp",
20 | "model-context-protocol",
21 | "mysql",
22 | "database",
23 | "claude",
24 | "anthropic"
25 | ],
26 | "author": "malove86",
27 | "license": "MIT",
28 | "dependencies": {
29 | "@modelcontextprotocol/sdk": "0.6.0",
30 | "dotenv": "^16.4.7",
31 | "mysql2": "^3.11.5"
32 | },
33 | "devDependencies": {
34 | "@types/node": "^20.11.24",
35 | "typescript": "^5.3.3"
36 | },
37 | "repository": {
38 | "type": "git",
39 | "url": "https://github.com/Malove86/mcp-mysql-server.git"
40 | },
41 | "publishConfig": {
42 | "access": "public"
43 | }
44 | }
45 |
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
1 | # Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
2 |
3 | startCommand:
4 | type: stdio
5 | configSchema:
6 | # JSON Schema defining the configuration options for the MCP.
7 | type: object
8 | required:
9 | - mysqlHost
10 | - mysqlUser
11 | - mysqlPassword
12 | - mysqlDatabase
13 | properties:
14 | mysqlHost:
15 | type: string
16 | description: Hostname for the MySQL server
17 | mysqlUser:
18 | type: string
19 | description: MySQL user name
20 | mysqlPassword:
21 | type: string
22 | description: MySQL user's password
23 | mysqlDatabase:
24 | type: string
25 | description: MySQL database name
26 | mysqlPort:
27 | type: integer
28 | description: Port for the MySQL server (default 3306)
29 | default: 3306
30 | commandFunction:
31 | # A JS function that produces the CLI command based on the given config to start the MCP on stdio.
32 | |-
33 | (config) => ({
34 | command: 'node',
35 | args: ['build/index.js'],
36 | env: {
37 | MYSQL_HOST: config.mysqlHost,
38 | MYSQL_USER: config.mysqlUser,
39 | MYSQL_PASSWORD: config.mysqlPassword,
40 | MYSQL_DATABASE: config.mysqlDatabase,
41 | MYSQL_PORT: config.mysqlPort?.toString() || '3306'
42 | }
43 | })
44 | exampleConfig:
45 | mysqlHost: localhost
46 | mysqlUser: example_user
47 | mysqlPassword: example_password
48 | mysqlDatabase: example_db
49 | mysqlPort: 3306
50 |
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | #!/usr/bin/env node
2 | import { Server } from '@modelcontextprotocol/sdk/server/index.js';
3 | import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
4 | import {
5 | CallToolRequestSchema,
6 | ErrorCode,
7 | ListToolsRequestSchema,
8 | McpError,
9 | } from '@modelcontextprotocol/sdk/types.js';
10 | import * as mysql from 'mysql2/promise';
11 | import { config } from 'dotenv';
12 | import { Pool } from 'mysql2/promise';
13 |
14 | // Load environment variables
15 | config();
16 |
17 | interface DatabaseConfig {
18 | host: string;
19 | user: string;
20 | password: string;
21 | database: string;
22 | port?: number; // Add optional port parameter
23 | }
24 |
25 | // Type guard for error objects
26 | function isErrorWithMessage(error: unknown): error is { message: string } {
27 | return (
28 | typeof error === 'object' &&
29 | error !== null &&
30 | 'message' in error &&
31 | typeof (error as Record<string, unknown>).message === 'string'
32 | );
33 | }
34 |
35 | // Helper to get error message
36 | function getErrorMessage(error: unknown): string {
37 | if (isErrorWithMessage(error)) {
38 | return error.message;
39 | }
40 | return String(error);
41 | }
42 |
43 | // Helper to validate SQL query for basic security
44 | function validateSqlQuery(sql: string): boolean {
45 | // 检查是否存在常见SQL注入模式
46 | const dangerousPatterns = [
47 | /;\s*DROP\s+/i,
48 | /;\s*DELETE\s+/i,
49 | /;\s*UPDATE\s+/i,
50 | /;\s*INSERT\s+/i,
51 | /UNION\s+SELECT/i,
52 | /--/,
53 | /\/\*/,
54 | /xp_cmdshell/i
55 | ];
56 |
57 | return !dangerousPatterns.some(pattern => pattern.test(sql));
58 | }
59 |
60 | class MySQLServer {
61 | private server: Server;
62 | private config: DatabaseConfig | null = null;
63 | private pool: Pool | null = null;
64 | private isConnected: boolean = false;
65 | private connectionPromise: Promise<void> | null = null;
66 | private activeConnections: number = 0;
67 | private maxConnections: number = 50; // 最大同时处理的连接数
68 |
69 | constructor() {
70 | this.server = new Server(
71 | {
72 | name: 'mysql-server',
73 | version: '1.0.0',
74 | },
75 | {
76 | capabilities: {
77 | tools: {},
78 | },
79 | }
80 | );
81 |
82 | if (process.env.MYSQL_HOST
83 | && process.env.MYSQL_USER
84 | && process.env.MYSQL_PASSWORD !== undefined
85 | && process.env.MYSQL_PASSWORD !== null
86 | && process.env.MYSQL_DATABASE) {
87 | this.config = {
88 | host: process.env.MYSQL_HOST as string,
89 | user: process.env.MYSQL_USER as string,
90 | password: process.env.MYSQL_PASSWORD as string,
91 | database: process.env.MYSQL_DATABASE as string,
92 | port: Number(process.env.MYSQL_PORT ?? 3306),
93 | };
94 |
95 | // 打印连接信息
96 | console.error(`[Init] Found database configuration in environment variables: ${this.config.host}:${this.config.port}/${this.config.database}`);
97 | }
98 |
99 | this.setupToolHandlers();
100 |
101 | // Error handling
102 | this.server.onerror = (error) => console.error('[MCP Error]', error);
103 | process.on('SIGINT', async () => {
104 | await this.cleanup();
105 | process.exit(0);
106 | });
107 | }
108 |
109 | private async cleanup() {
110 | if (this.pool) {
111 | try {
112 | console.error(`[Cleanup] Closing connection pool, active connections: ${this.activeConnections}`);
113 | await this.pool.end();
114 | console.error('[Cleanup] Connection pool closed successfully');
115 | } catch (error) {
116 | console.error(`[Cleanup] Error closing pool: ${getErrorMessage(error)}`);
117 | }
118 | this.pool = null;
119 | }
120 | await this.server.close();
121 | }
122 |
123 | private async ensureConnection() {
124 | // 如果已经有一个正在进行的连接操作，直接返回那个Promise
125 | if (this.connectionPromise) {
126 | return this.connectionPromise;
127 | }
128 |
129 | // 如果没有配置，抛出错误
130 | if (!this.config) {
131 | throw new McpError(
132 | ErrorCode.InvalidRequest,
133 | 'Database configuration not set. Use connect_db tool first or set environment variables.'
134 | );
135 | }
136 |
137 | // 创建连接池
138 | if (!this.pool) {
139 | const connectionPromise = (async () => {
140 | try {
141 | // 创建连接池而不是单一连接，提高性能和稳定性
142 | this.pool = mysql.createPool({
143 | ...this.config,
144 | waitForConnections: true,
145 | connectionLimit: this.maxConnections, // 增加连接池大小以支持更多同时连接
146 | queueLimit: 0
147 | });
148 |
149 | // 测试连接池是否正常工作
150 | const conn = await this.pool.getConnection();
151 | await conn.ping();
152 | conn.release();
153 |
154 | // 这里一定有config，因为前面已经检查过
155 | const config = this.config!;
156 | console.error(`Successfully connected to MySQL database: ${config.host}:${config.port || 3306}/${config.database}`);
157 | this.isConnected = true;
158 |
159 | // 连接成功后清空connectionPromise，允许将来的连接检查创建新的Promise
160 | this.connectionPromise = null;
161 | } catch (error) {
162 | this.pool = null; // 重置连接池对象以便下次重试
163 | this.isConnected = false;
164 | this.connectionPromise = null; // 重置连接Promise
165 |
166 | const errorMsg = getErrorMessage(error);
167 | console.error(`Database connection failed: ${errorMsg}`);
168 | throw new McpError(
169 | ErrorCode.InternalError,
170 | `Failed to connect to database: ${errorMsg}`
171 | );
172 | }
173 | })();
174 |
175 | this.connectionPromise = connectionPromise;
176 | return connectionPromise;
177 | }
178 |
179 | return Promise.resolve();
180 | }
181 |
182 | private setupToolHandlers() {
183 | this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
184 | tools: [
185 | {
186 | name: 'connect_db',
187 | description: 'Connect to MySQL database (optional if environment variables are set)',
188 | inputSchema: {
189 | type: 'object',
190 | properties: {
191 | host: {
192 | type: 'string',
193 | description: 'Database host',
194 | },
195 | user: {
196 | type: 'string',
197 | description: 'Database user',
198 | },
199 | password: {
200 | type: 'string',
201 | description: 'Database password',
202 | },
203 | database: {
204 | type: 'string',
205 | description: 'Database name',
206 | },
207 | port: {
208 | type: 'number',
209 | description: 'Database port (optional)',
210 | },
211 | },
212 | required: ['host', 'user', 'password', 'database'],
213 | },
214 | },
215 | {
216 | name: 'query',
217 | description: 'Execute a SELECT query',
218 | inputSchema: {
219 | type: 'object',
220 | properties: {
221 | sql: {
222 | type: 'string',
223 | description: 'SQL SELECT query',
224 | },
225 | params: {
226 | type: 'array',
227 | items: {
228 | type: ['string', 'number', 'boolean', 'null'],
229 | },
230 | description: 'Query parameters (optional)',
231 | },
232 | },
233 | required: ['sql'],
234 | },
235 | },
236 | {
237 | name: 'list_tables',
238 | description: 'List all tables in the database',
239 | inputSchema: {
240 | type: 'object',
241 | properties: {
242 | random_string: {
243 | type: 'string',
244 | description: 'Dummy parameter for no-parameter tools',
245 | }
246 | },
247 | required: [], // 修改为可选参数
248 | },
249 | },
250 | {
251 | name: 'describe_table',
252 | description: 'Get table structure',
253 | inputSchema: {
254 | type: 'object',
255 | properties: {
256 | table: {
257 | type: 'string',
258 | description: 'Table name',
259 | },
260 | },
261 | required: ['table'],
262 | },
263 | },
264 | ],
265 | }));
266 |
267 | this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
268 | // 获取请求ID用于日志
269 | const requestId = `req-${Date.now()}-${Math.floor(Math.random() * 1000)}`;
270 | console.error(`[${requestId}] Received tool call: ${request.params.name}`);
271 |
272 | // 增加活跃连接计数
273 | this.activeConnections++;
274 | console.error(`[${requestId}] Active connections: ${this.activeConnections}`);
275 |
276 | try {
277 | // 如果有环境变量配置并且不是connect_db命令，先确保连接存在
278 | if (this.config && !this.isConnected && request.params.name !== 'connect_db') {
279 | try {
280 | console.error(`[${requestId}] Auto-connecting to database`);
281 | await this.ensureConnection();
282 | } catch (error) {
283 | console.error(`[${requestId}] Auto-connection failed: ${getErrorMessage(error)}`);
284 | // 不抛出错误，让后续操作根据实际情况处理
285 | }
286 | }
287 |
288 | let result;
289 | switch (request.params.name) {
290 | case 'connect_db':
291 | result = await this.handleConnectDb(requestId, request.params.arguments);
292 | break;
293 | case 'query':
294 | result = await this.handleQuery(requestId, request.params.arguments);
295 | break;
296 | case 'list_tables':
297 | result = await this.handleListTables(requestId);
298 | break;
299 | case 'describe_table':
300 | result = await this.handleDescribeTable(requestId, request.params.arguments);
301 | break;
302 | default:
303 | throw new McpError(
304 | ErrorCode.MethodNotFound,
305 | `Unknown tool: ${request.params.name}`
306 | );
307 | }
308 |
309 | return result;
310 | } finally {
311 | // 减少活跃连接计数
312 | this.activeConnections--;
313 | console.error(`[${requestId}] Request completed, active connections: ${this.activeConnections}`);
314 | }
315 | });
316 | }
317 |
318 | private async handleConnectDb(requestId: string, args: any) {
319 | // 验证参数
320 | if (!args.host || !args.user || args.password === undefined || args.password === null || !args.database) {
321 | throw new McpError(
322 | ErrorCode.InvalidParams,
323 | 'Missing required database configuration parameters'
324 | );
325 | }
326 |
327 | // 关闭现有连接池
328 | if (this.pool) {
329 | try {
330 | console.error(`[${requestId}] Closing existing connection pool`);
331 | await this.pool.end();
332 | } catch (error) {
333 | console.error(`[${requestId}] Error closing pool: ${getErrorMessage(error)}`);
334 | }
335 | this.pool = null;
336 | }
337 |
338 | this.config = {
339 | host: args.host,
340 | user: args.user,
341 | password: args.password,
342 | database: args.database,
343 | port: args.port || 3306, // 确保有默认端口
344 | };
345 |
346 | try {
347 | console.error(`[${requestId}] Connecting to database: ${this.config.host}:${this.config.port}/${this.config.database}`);
348 | await this.ensureConnection();
349 | return {
350 | content: [
351 | {
352 | type: 'text',
353 | text: 'Successfully connected to database',
354 | },
355 | ],
356 | };
357 | } catch (error) {
358 | throw new McpError(
359 | ErrorCode.InternalError,
360 | `Failed to connect to database: ${getErrorMessage(error)}`
361 | );
362 | }
363 | }
364 |
365 | private async handleQuery(requestId: string, args: any) {
366 | await this.ensureConnection();
367 |
368 | if (!args.sql) {
369 | throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
370 | }
371 |
372 | if (!args.sql.trim().toUpperCase().startsWith('SELECT')) {
373 | throw new McpError(
374 | ErrorCode.InvalidParams,
375 | 'Only SELECT queries are allowed with query tool'
376 | );
377 | }
378 |
379 | const sql = args.sql.trim();
380 |
381 | // 验证SQL安全性
382 | if (!validateSqlQuery(sql)) {
383 | throw new McpError(
384 | ErrorCode.InvalidParams,
385 | 'SQL query contains potentially dangerous patterns'
386 | );
387 | }
388 |
389 | try {
390 | console.error(`[${requestId}] Executing query: ${sql.substring(0, 100)}${sql.length > 100 ? '...' : ''}`);
391 | const [rows] = await this.pool!.query(args.sql, args.params || []);
392 |
393 | // 计算结果集大小
394 | const resultSize = JSON.stringify(rows).length;
395 | console.error(`[${requestId}] Query executed successfully, result size: ${resultSize} bytes`);
396 |
397 | return {
398 | content: [
399 | {
400 | type: 'text',
401 | text: JSON.stringify(rows, null, 2),
402 | },
403 | ],
404 | };
405 | } catch (error) {
406 | const errorMsg = getErrorMessage(error);
407 | console.error(`[${requestId}] Query execution failed: ${errorMsg}`);
408 | throw new McpError(
409 | ErrorCode.InternalError,
410 | `Query execution failed: ${errorMsg}`
411 | );
412 | }
413 | }
414 |
415 | private async handleListTables(requestId: string) {
416 | await this.ensureConnection();
417 |
418 | try {
419 | console.error(`[${requestId}] Executing SHOW TABLES`);
420 | const [rows] = await this.pool!.query('SHOW TABLES');
421 | console.error(`[${requestId}] SHOW TABLES completed, found ${Array.isArray(rows) ? rows.length : 0} tables`);
422 |
423 | return {
424 | content: [
425 | {
426 | type: 'text',
427 | text: JSON.stringify(rows, null, 2),
428 | },
429 | ],
430 | };
431 | } catch (error) {
432 | const errorMsg = getErrorMessage(error);
433 | console.error(`[${requestId}] Failed to list tables: ${errorMsg}`);
434 | throw new McpError(
435 | ErrorCode.InternalError,
436 | `Failed to list tables: ${errorMsg}`
437 | );
438 | }
439 | }
440 |
441 | private async handleDescribeTable(requestId: string, args: any) {
442 | await this.ensureConnection();
443 |
444 | if (!args.table) {
445 | throw new McpError(ErrorCode.InvalidParams, 'Table name is required');
446 | }
447 |
448 | try {
449 | console.error(`[${requestId}] Executing DESCRIBE ${args.table}`);
450 | const [rows] = await this.pool!.query('DESCRIBE ??', [args.table]);
451 | console.error(`[${requestId}] DESCRIBE completed, found ${Array.isArray(rows) ? rows.length : 0} columns`);
452 |
453 | return {
454 | content: [
455 | {
456 | type: 'text',
457 | text: JSON.stringify(rows, null, 2),
458 | },
459 | ],
460 | };
461 | } catch (error) {
462 | const errorMsg = getErrorMessage(error);
463 | console.error(`[${requestId}] Failed to describe table: ${errorMsg}`);
464 | throw new McpError(
465 | ErrorCode.InternalError,
466 | `Failed to describe table: ${errorMsg}`
467 | );
468 | }
469 | }
470 |
471 | async run() {
472 | const transport = new StdioServerTransport();
473 | await this.server.connect(transport);
474 | console.error('MySQL MCP server running on stdio');
475 | console.error(`Max concurrent connections: ${this.maxConnections}`);
476 |
477 | // 如果配置了环境变量，尝试初始连接
478 | if (this.config && !this.isConnected) {
479 | try {
480 | console.error('[Init] Auto-connecting to database with environment variables');
481 | await this.ensureConnection();
482 | console.error('[Init] Auto-connection succeeded');
483 | } catch (error) {
484 | console.error(`[Init] Auto-connection failed: ${getErrorMessage(error)}`);
485 | // 不抛出错误，让后续操作根据实际情况处理
486 | }
487 | }
488 | }
489 | }
490 |
491 | const server = new MySQLServer();
492 | server.run().catch(console.error);
493 |
```