#!/usr/bin/env node
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListResourcesRequestSchema,
ListToolsRequestSchema,
ReadResourceRequestSchema,
ListPromptsRequestSchema,
GetPromptRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import mysql, { MysqlError, Pool, PoolConnection } from "mysql";
import * as fs from 'fs';
import * as path from 'path';
import * as os from 'os';
// ============================================================================
// Type Definitions
// ============================================================================
type MySQLErrorType = MysqlError | null;
type AccessMode = 'readonly' | 'readwrite' | 'full';
type StatementType = 'SELECT' | 'INSERT' | 'UPDATE' | 'DELETE' | 'DDL' | 'UNKNOWN';
interface TableRow {
table_name: string;
TABLE_NAME?: string;
}
interface ColumnRow {
column_name: string;
data_type: string;
COLUMN_NAME?: string;
DATA_TYPE?: string;
}
interface DatabaseConfig {
name: string;
host: string;
port: number;
user: string;
password: string;
database: string;
accessMode?: AccessMode; // Default: 'readonly'
allowedTables?: string[]; // Whitelist patterns
deniedTables?: string[]; // Blacklist patterns
connectionLimit?: number; // Connection pool size
}
interface DatabasesFileConfig {
databases: DatabaseConfig[];
}
// ============================================================================
// Configuration
// ============================================================================
const serverConfig = {
name: "mcp-server-mysql",
version: "2.0.0",
};
/**
* Parse database configurations from config file and/or environment variables.
*/
function parseDatabaseConfigs(): Map<string, DatabaseConfig> {
const databases = new Map<string, DatabaseConfig>();
// Check for config file
const configPath = process.env.MYSQL_CONFIG_PATH
|| path.join(os.homedir(), '.mcp_mysql', 'databases.json');
if (fs.existsSync(configPath)) {
try {
const fileContent = fs.readFileSync(configPath, 'utf-8');
const fileConfig: DatabasesFileConfig = JSON.parse(fileContent);
for (const db of fileConfig.databases) {
// Set default accessMode to 'readonly' if not specified
if (!db.accessMode) {
db.accessMode = 'readonly';
}
databases.set(db.name, db);
}
console.error(`Loaded ${fileConfig.databases.length} database(s) from ${configPath}`);
} catch (err) {
console.error(`Failed to parse config file ${configPath}:`, err);
}
}
// Legacy mode: single database via environment variables (backward compatible)
if (process.env.MYSQL_HOST || process.env.MYSQL_USER) {
const defaultDb: DatabaseConfig = {
name: "default",
host: process.env.MYSQL_HOST || "127.0.0.1",
port: Number(process.env.MYSQL_PORT || "3306"),
user: process.env.MYSQL_USER || "root",
password: process.env.MYSQL_PASS || "",
database: process.env.MYSQL_DB || "",
accessMode: 'readonly', // Default to readonly for safety
connectionLimit: 10,
};
databases.set("default", defaultDb);
console.error(`Loaded "default" database from environment variables`);
}
if (databases.size === 0) {
console.error("Warning: No database configurations found.");
}
return databases;
}
const databases = parseDatabaseConfigs();
// ============================================================================
// Connection Pool Management
// ============================================================================
const connectionPools = new Map<string, Pool>();
/**
* Get or create a connection pool for a database.
*/
function getConnectionPool(dbConfig: DatabaseConfig): Pool {
let pool = connectionPools.get(dbConfig.name);
if (!pool) {
pool = mysql.createPool({
host: dbConfig.host,
port: dbConfig.port,
user: dbConfig.user,
password: dbConfig.password,
database: dbConfig.database,
connectionLimit: dbConfig.connectionLimit || 10,
});
connectionPools.set(dbConfig.name, pool);
}
return pool;
}
// ============================================================================
// SQL Validation
// ============================================================================
/**
* Detect the type of SQL statement.
*/
function getStatementType(sql: string): StatementType {
const trimmed = sql.trim().toUpperCase();
if (trimmed.startsWith('SELECT') || trimmed.startsWith('WITH')) return 'SELECT';
if (trimmed.startsWith('INSERT')) return 'INSERT';
if (trimmed.startsWith('UPDATE')) return 'UPDATE';
if (trimmed.startsWith('DELETE')) return 'DELETE';
if (/^(CREATE|ALTER|DROP|TRUNCATE|GRANT|REVOKE)/.test(trimmed)) return 'DDL';
return 'UNKNOWN';
}
/**
* Extract table names from SQL (simplified extraction).
*/
function extractTableNames(sql: string): string[] {
const tables: string[] = [];
// Match patterns like: FROM table, INTO table, UPDATE table, JOIN table
const patterns = [
/\bFROM\s+`?([A-Z_][A-Z0-9_]*)`?/gi,
/\bINTO\s+`?([A-Z_][A-Z0-9_]*)`?/gi,
/\bUPDATE\s+`?([A-Z_][A-Z0-9_]*)`?/gi,
/\bJOIN\s+`?([A-Z_][A-Z0-9_]*)`?/gi,
/\bTABLE\s+`?([A-Z_][A-Z0-9_]*)`?/gi,
/\bTRUNCATE\s+(?:TABLE\s+)?`?([A-Z_][A-Z0-9_]*)`?/gi,
];
for (const pattern of patterns) {
let match;
const regex = new RegExp(pattern.source, pattern.flags);
while ((match = regex.exec(sql)) !== null) {
const tableName = match[1].toLowerCase();
if (!tables.includes(tableName)) {
tables.push(tableName);
}
}
}
return tables;
}
/**
* Check if a table name matches a pattern (exact, wildcard, or regex).
*/
function matchesPattern(tableName: string, pattern: string): boolean {
const tableLower = tableName.toLowerCase();
// Regex pattern (starts and ends with /)
if (pattern.startsWith('/') && pattern.endsWith('/')) {
try {
const regex = new RegExp(pattern.slice(1, -1), 'i');
return regex.test(tableLower);
} catch {
return false;
}
}
// Wildcard pattern (contains *)
if (pattern.includes('*')) {
const regexPattern = pattern.toLowerCase().replace(/\*/g, '.*');
return new RegExp(`^${regexPattern}$`).test(tableLower);
}
// Exact match
return tableLower === pattern.toLowerCase();
}
/**
* Check if a table is allowed based on whitelist/blacklist patterns.
*/
function isTableAllowed(tableName: string, dbConfig: DatabaseConfig): boolean {
// Check blacklist first (deniedTables)
if (dbConfig.deniedTables && dbConfig.deniedTables.length > 0) {
for (const pattern of dbConfig.deniedTables) {
if (matchesPattern(tableName, pattern)) {
return false;
}
}
}
// Check whitelist (allowedTables)
if (dbConfig.allowedTables && dbConfig.allowedTables.length > 0) {
for (const pattern of dbConfig.allowedTables) {
if (matchesPattern(tableName, pattern)) {
return true;
}
}
return false; // Not in whitelist
}
return true; // No restrictions
}
/**
* Validate SQL access based on database configuration.
*/
function validateAccess(sql: string, dbConfig: DatabaseConfig): void {
const stmtType = getStatementType(sql);
const mode = dbConfig.accessMode || 'readonly';
// Check statement type against access mode
if (mode === 'readonly' && stmtType !== 'SELECT') {
throw new Error(`Database "${dbConfig.name}" is read-only. Only SELECT queries are allowed.`);
}
if (mode === 'readwrite' && stmtType === 'DDL') {
throw new Error(`Database "${dbConfig.name}" does not allow DDL operations.`);
}
// Check table-level permissions for write operations
if (stmtType !== 'SELECT') {
const tables = extractTableNames(sql);
for (const table of tables) {
if (!isTableAllowed(table, dbConfig)) {
throw new Error(`Access denied: Table "${table}" is not allowed for write operations in database "${dbConfig.name}".`);
}
}
}
}
// ============================================================================
// Database Operations
// ============================================================================
const mysqlQuery = <T>(
connection: PoolConnection,
sql: string,
params: any[] = [],
): Promise<T> => {
return new Promise((resolve, reject) => {
connection.query(sql, params, (error: MySQLErrorType, results: any) => {
if (error) reject(error);
else resolve(results);
});
});
};
/**
* Get database configuration by name.
*/
function getDatabase(dbName?: string): DatabaseConfig {
let targetDb: DatabaseConfig | undefined;
if (dbName) {
targetDb = databases.get(dbName);
if (!targetDb) {
throw new Error(`Database "${dbName}" not found. Available: ${Array.from(databases.keys()).join(', ')}`);
}
} else if (databases.size === 1) {
targetDb = databases.values().next().value;
} else if (databases.size > 1) {
throw new Error(`Multiple databases configured. Please specify a database name. Available: ${Array.from(databases.keys()).join(', ')}`);
} else {
throw new Error("No database configurations found.");
}
if (!targetDb) {
throw new Error("No database configuration available.");
}
return targetDb;
}
/**
* Get a database connection by name.
*/
const mysqlGetConnection = (dbName?: string): Promise<{ connection: PoolConnection; dbConfig: DatabaseConfig }> => {
const dbConfig = getDatabase(dbName);
const pool = getConnectionPool(dbConfig);
return new Promise((resolve, reject) => {
pool.getConnection((error: MySQLErrorType, connection: PoolConnection) => {
if (error) reject(new Error(`Error connecting to MySQL (${dbConfig.name}): ${error.message}`));
else resolve({ connection, dbConfig });
});
});
};
const mysqlBeginTransaction = (connection: PoolConnection): Promise<void> => {
return new Promise((resolve, reject) => {
connection.beginTransaction((error: MySQLErrorType) => {
if (error) reject(error);
else resolve();
});
});
};
const mysqlRollback = (connection: PoolConnection): Promise<void> => {
return new Promise((resolve, _) => {
connection.rollback(() => resolve());
});
};
const mysqlCommit = (connection: PoolConnection): Promise<void> => {
return new Promise((resolve, reject) => {
connection.commit((error: MySQLErrorType) => {
if (error) reject(error);
else resolve();
});
});
};
// ============================================================================
// Query Execution Functions
// ============================================================================
async function executeQuery<T>(sql: string, params: any[] = [], dbName?: string): Promise<T> {
const { connection, dbConfig } = await mysqlGetConnection(dbName);
try {
const results = await mysqlQuery<T>(connection, sql, params);
return results;
} finally {
connection.release();
}
}
/**
* Execute a read-only query with validation and read-only transaction.
*/
async function executeReadOnlyQuery<T>(sql: string, dbName?: string): Promise<T> {
const dbConfig = getDatabase(dbName);
// Validate: only SELECT allowed
const stmtType = getStatementType(sql);
if (stmtType !== 'SELECT') {
throw new Error('Only SELECT queries are allowed with mysql_query. Use mysql_execute for write operations.');
}
const { connection } = await mysqlGetConnection(dbName);
try {
// Set read-only mode
await mysqlQuery(connection, "SET SESSION TRANSACTION READ ONLY");
// Begin transaction
await mysqlBeginTransaction(connection);
// Execute query
const results = await mysqlQuery(connection, sql);
// Rollback transaction (since it's read-only)
await mysqlRollback(connection);
// Reset to read-write mode
await mysqlQuery(connection, "SET SESSION TRANSACTION READ WRITE");
return <T>{
content: [
{
type: "text",
text: JSON.stringify(results, null, 2),
},
],
isError: false,
};
} catch (error) {
await mysqlRollback(connection);
throw error;
} finally {
connection.release();
}
}
/**
* Execute a write operation with validation and confirmation.
*/
async function executeWriteQuery<T>(sql: string, dbName: string, confirm: boolean): Promise<T> {
if (!confirm) {
throw new Error('Write operations require confirmation. Set confirm=true to proceed.');
}
const dbConfig = getDatabase(dbName);
// Validate access based on configuration
validateAccess(sql, dbConfig);
const { connection } = await mysqlGetConnection(dbName);
try {
await mysqlBeginTransaction(connection);
const results = await mysqlQuery(connection, sql);
await mysqlCommit(connection);
return <T>{
content: [
{
type: "text",
text: JSON.stringify({
success: true,
message: "Operation completed successfully",
results: results,
}, null, 2),
},
],
isError: false,
};
} catch (error) {
await mysqlRollback(connection);
throw error;
} finally {
connection.release();
}
}
// ============================================================================
// MCP Server Setup
// ============================================================================
const server = new Server(serverConfig, {
capabilities: {
resources: {},
tools: {},
prompts: {},
},
});
// ============================================================================
// Helper: Check if any database is writable
// ============================================================================
function hasWritableDatabase(): boolean {
return Array.from(databases.values()).some(
db => db.accessMode === 'readwrite' || db.accessMode === 'full'
);
}
function getWritableDatabases(): string[] {
return Array.from(databases.entries())
.filter(([, db]) => db.accessMode === 'readwrite' || db.accessMode === 'full')
.map(([name]) => name);
}
// ============================================================================
// Resource Handlers
// ============================================================================
server.setRequestHandler(ListResourcesRequestSchema, async () => {
const resources: Array<{
uri: string;
mimeType: string;
name: string;
description?: string;
}> = [];
// Add connections resource
resources.push({
uri: "mysql://connections",
mimeType: "application/json",
name: "MySQL Database Connections",
description: "List of available MySQL database connections with access modes",
});
// Add table schema resources for each database
for (const [dbName, dbConfig] of databases) {
try {
const { connection } = await mysqlGetConnection(dbName);
const tables = await mysqlQuery<TableRow[]>(
connection,
"SELECT table_name FROM information_schema.tables WHERE table_schema = ?",
[dbConfig.database]
);
connection.release();
for (const table of tables) {
const tableName = table.table_name || table.TABLE_NAME;
resources.push({
uri: `mysql://${dbName}/tables/${tableName}/schema`,
mimeType: "application/json",
name: `[${dbName}] "${tableName}" schema`,
description: `Schema for table ${tableName} in database ${dbName}`,
});
}
} catch (err) {
console.error(`Failed to list tables for ${dbName}:`, err);
}
}
return { resources };
});
server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
const uri = request.params.uri;
// Handle connections resource
if (uri === "mysql://connections") {
const connectionList = Array.from(databases.entries()).map(([name, config]) => ({
name,
host: config.host,
port: config.port,
database: config.database,
user: config.user,
accessMode: config.accessMode || 'readonly',
allowedTables: config.allowedTables,
deniedTables: config.deniedTables,
}));
return {
contents: [{
uri,
mimeType: "application/json",
text: JSON.stringify(connectionList, null, 2),
}],
};
}
// Handle table schema resource
const match = uri.match(/^mysql:\/\/([^\/]+)\/tables\/([^\/]+)\/schema$/);
if (match) {
const [, dbName, tableName] = match;
const dbConfig = getDatabase(dbName);
const { connection } = await mysqlGetConnection(dbName);
try {
const results = await mysqlQuery<ColumnRow[]>(
connection,
"SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = ? AND table_name = ?",
[dbConfig.database, tableName]
);
return {
contents: [{
uri,
mimeType: "application/json",
text: JSON.stringify(results, null, 2),
}],
};
} finally {
connection.release();
}
}
throw new Error(`Invalid resource URI: ${uri}`);
});
// ============================================================================
// Prompt Handlers
// ============================================================================
server.setRequestHandler(ListPromptsRequestSchema, async () => ({
prompts: [
{
name: "mysql_usage_guide",
description: "Guide for querying MySQL databases",
},
],
}));
server.setRequestHandler(GetPromptRequestSchema, async (request) => {
if (request.params.name !== "mysql_usage_guide") {
throw new Error(`Unknown prompt: ${request.params.name}`);
}
const dbList = Array.from(databases.entries()).map(([name, config]) =>
`${name} (${config.accessMode || 'readonly'})`
).join(', ');
const writableList = getWritableDatabases();
const writeSection = writableList.length > 0
? `\n\nFor write operations (INSERT/UPDATE/DELETE), use the mysql_execute tool with:\n- database: Must be one of [${writableList.join(', ')}]\n- sql: The SQL statement\n- confirm: Must be true to execute`
: '';
return {
description: "MySQL Database Query Guide",
messages: [
{
role: "user",
content: {
type: "text",
text: `You have access to the following MySQL databases: [${dbList}].
For read-only queries (SELECT), use the mysql_query tool with:
- database: The database name (optional if only one database is configured)
- sql: The SQL query to execute${writeSection}
Examples:
- "查询 dev 库中的用户表" -> Use mysql_query with database="dev"
- "查看 test 环境的订单数据" -> Use mysql_query with database="test"
Use mysql://connections resource to see all database connections and their access modes.`,
},
},
],
};
});
// ============================================================================
// Tool Handlers
// ============================================================================
server.setRequestHandler(ListToolsRequestSchema, async () => {
const tools: Array<{
name: string;
description: string;
inputSchema: object;
}> = [
{
name: "mysql_query",
description: "Run a read-only SELECT query on a MySQL database",
inputSchema: {
type: "object",
properties: {
database: {
type: "string",
description: `Database connection name. Available: ${Array.from(databases.keys()).join(', ')}`,
},
sql: {
type: "string",
description: "SELECT query to execute (read-only)",
},
},
required: ["sql"],
},
},
];
// Dynamically expose mysql_execute only if writable databases exist
if (hasWritableDatabase()) {
const writableList = getWritableDatabases();
tools.push({
name: "mysql_execute",
description: "Execute a write operation (INSERT/UPDATE/DELETE/DDL) on a MySQL database. Requires confirmation.",
inputSchema: {
type: "object",
properties: {
database: {
type: "string",
description: `Database connection name. Writable databases: ${writableList.join(', ')}`,
},
sql: {
type: "string",
description: "SQL statement to execute (INSERT/UPDATE/DELETE/DDL)",
},
confirm: {
type: "boolean",
description: "Must be true to confirm and execute the operation",
},
},
required: ["database", "sql", "confirm"],
},
});
}
return { tools };
});
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const toolName = request.params.name;
if (toolName === "mysql_query") {
const sql = request.params.arguments?.sql as string;
const dbName = request.params.arguments?.database as string | undefined;
return executeReadOnlyQuery(sql, dbName);
}
if (toolName === "mysql_execute") {
const sql = request.params.arguments?.sql as string;
const dbName = request.params.arguments?.database as string;
const confirm = request.params.arguments?.confirm as boolean;
if (!dbName) {
throw new Error("Database name is required for write operations.");
}
return executeWriteQuery(sql, dbName, confirm);
}
throw new Error(`Unknown tool: ${toolName}`);
});
// ============================================================================
// Server Startup and Shutdown
// ============================================================================
async function runServer() {
const transport = new StdioServerTransport();
await server.connect(transport);
console.error("MySQL MCP Server started");
console.error(`Databases: ${Array.from(databases.entries()).map(([n, c]) => `${n}(${c.accessMode})`).join(', ')}`);
}
const shutdown = async (signal: string) => {
console.error(`Received ${signal}. Shutting down...`);
const closePromises: Promise<void>[] = [];
for (const [name, pool] of connectionPools) {
closePromises.push(new Promise<void>((resolve, reject) => {
pool.end((err: MySQLErrorType) => {
if (err) {
console.error(`Error closing pool ${name}:`, err);
reject(err);
} else {
resolve();
}
});
}));
}
await Promise.all(closePromises);
};
process.on("SIGINT", async () => {
try {
await shutdown("SIGINT");
process.exit(0);
} catch (err) {
process.exit(1);
}
});
process.on("SIGTERM", async () => {
try {
await shutdown("SIGTERM");
process.exit(0);
} catch (err) {
process.exit(1);
}
});
runServer().catch((error: unknown) => {
console.error("Server error:", error);
process.exit(1);
});
export { executeQuery, executeReadOnlyQuery, executeWriteQuery, databases };
