MySQL MCP Server

#!/usr/bin/env node /** * MySQL MCP Server * * Model Context Protocol server for MySQL database access. * Provides tools for querying MySQL databases. */ import { Server } from '@modelcontextprotocol/sdk/server/index.js'; import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js'; import { CallToolRequestSchema, ListToolsRequestSchema, Tool, } from '@modelcontextprotocol/sdk/types.js'; // Extend Tool type to include input_examples for better Claude parameter accuracy // See: https://www.anthropic.com/engineering/advanced-tool-use type ToolWithExamples = Tool & { input_examples?: Record<string, unknown>[]; }; import mysql from 'mysql2/promise'; // MySQL connection configuration from environment variables const config = { host: process.env.MYSQL_HOST || 'localhost', port: parseInt(process.env.MYSQL_PORT || '3306'), user: process.env.MYSQL_USER || 'root', password: process.env.MYSQL_PASS || '', database: process.env.MYSQL_DB || '', }; // Create connection pool const pool = mysql.createPool({ ...config, waitForConnections: true, connectionLimit: 10, queueLimit: 0, }); // Create MCP server const server = new Server( { name: 'automatemysql', version: '1.0.0', }, { capabilities: { tools: {}, }, } ); // Define available tools with input_examples for improved Claude accuracy const tools: ToolWithExamples[] = [ { name: 'mysql_query', description: 'Execute a SQL query against the MySQL database. Supports SELECT, INSERT, UPDATE, DELETE, and other SQL statements. Returns query results as JSON.', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'The SQL query to execute' }, params: { type: 'array', description: 'Optional array of parameters for prepared statements', items: { type: ['string', 'number', 'boolean', 'null'] } } }, required: ['query'] }, input_examples: [ // Simple SELECT { query: 'SELECT * FROM users LIMIT 10' }, // SELECT with prepared statement parameters { query: 'SELECT * FROM users WHERE status = ? AND created_at > ?', params: ['active', '2024-01-01'] }, // JOIN query { query: 'SELECT u.name, o.order_date FROM users u INNER JOIN orders o ON u.id = o.user_id' }, // Aggregation { query: 'SELECT status, COUNT(*) as count FROM tickets GROUP BY status' }, // INSERT with parameters { query: 'INSERT INTO audit_log (action, timestamp) VALUES (?, NOW())', params: ['Data Import'] }, // UPDATE with parameters { query: 'UPDATE users SET status = ? WHERE id = ?', params: ['active', 12345] }, ] }, { name: 'mysql_show_tables', description: 'List all tables in the current database', inputSchema: { type: 'object', properties: {} } }, { name: 'mysql_describe_table', description: 'Get the schema/structure of a specific table', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Name of the table to describe' } }, required: ['tableName'] }, input_examples: [ { tableName: 'users' }, { tableName: 'orders' }, { tableName: 'audit_log' }, ] }, { name: 'mysql_table_info', description: 'Get detailed information about tables including row counts and sizes', inputSchema: { type: 'object', properties: { limit: { type: 'number', description: 'Maximum number of tables to return (default: 50)', default: 50 } } }, input_examples: [ {}, { limit: 10 }, { limit: 100 }, ] } ]; // Handle list tools request server.setRequestHandler(ListToolsRequestSchema, async () => { return { tools }; }); // Handle tool execution server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params; if (!args) { return { content: [{ type: 'text', text: 'Missing arguments' }], isError: true }; } try { switch (name) { case 'mysql_query': { const query = args.query as string; const params = (args.params as any[]) || []; const [rows] = await pool.execute(query, params); return { content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }] }; } case 'mysql_show_tables': { const [rows] = await pool.query('SHOW TABLES'); return { content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }] }; } case 'mysql_describe_table': { const tableName = args.tableName as string; // Use ?? placeholder for table name in prepared statement const [rows] = await pool.query('DESCRIBE ??', [tableName]); return { content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }] }; } case 'mysql_table_info': { const limit = parseInt(String(args.limit || 50)); const query = ` SELECT TABLE_NAME, TABLE_ROWS, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Size_MB, ENGINE, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_ROWS DESC LIMIT ${limit} `; const [rows] = await pool.query(query); return { content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }] }; } default: return { content: [{ type: 'text', text: `Unknown tool: ${name}` }], isError: true }; } } catch (error: any) { return { content: [{ type: 'text', text: `Error executing ${name}: ${error.message}\n${error.stack || ''}` }], isError: true }; } }); // Start the server async function main() { // Test database connection try { const connection = await pool.getConnection(); console.error('MySQL MCP Server: Database connection successful'); console.error(`Connected to: ${config.host}:${config.port}/${config.database}`); connection.release(); } catch (error: any) { console.error('MySQL MCP Server: Database connection failed:', error.message); process.exit(1); } const transport = new StdioServerTransport(); await server.connect(transport); console.error('MySQL MCP Server running on stdio'); } main().catch((error) => { console.error('Fatal error in main():', error); process.exit(1); });