MCP MySQL Server

A MCP MySQL server with DDL support, permission control and operation logs.

Version History

v2.0.2 (Latest)

• ✅ Configuration File Support: Added JSON configuration file support with --config parameter
• ✅ Command Line Arguments: Added --help and --config command line options
• ✅ Backward Compatibility: Maintained environment variable support as fallback
• ✅ Enhanced Documentation: Updated README with configuration examples and usage instructions

v2.0.1

• ✅ DDL SQL Logging: Added dedicated DDL SQL operation logging to ddl.sql file
• ✅ Success-Only Logging: Only successful DDL operations are recorded to the SQL file
• ✅ Timestamped Entries: Each DDL operation includes precise timestamp comments
• ✅ Auto-Formatting: SQL statements are automatically formatted with semicolon endings
• ✅ New Tool: Added get_ddl_sql_logs tool for querying DDL operation history
• ✅ Enhanced Logging: Improved logging configuration with separate DDL log file support

v2.0.0

• ✅ Initial release with DDL support
• ✅ Permission control system
• ✅ Operation logging
• ✅ Connection pool management

Features

• ✅ SQL query execution (DDL and DML)
• ✅ Database information retrieval
• ✅ Operation logging
• ✅ Connection pool management
• ✅ Auto-reconnection mechanism
• ✅ Health checks
• ✅ Error handling and recovery

Installation

Global Installation (Recommended)

npm install -g @bobliu1996/mcp-server-mysql

Local Installation

npm install @bobliu1996/mcp-server-mysql

From Source

git clone https://github.com/liliangshan/mcp-server-mysql.git
cd mcp-server-mysql
npm install

Configuration

Configuration File

The server now uses JSON configuration files and supports the --config parameter to specify the configuration file path.

Configuration File Structure

{
  "mysql": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "",
    "database": ""
  },
  "permissions": {
    "allowDDL": false,
    "allowDrop": false,
    "allowDelete": false
  },
  "logging": {
    "logDir": "./logs",
    "logFile": "mcp-mysql.log",
    "ddlLogFile": "ddl.sql"
  }
}

Configuration Parameters

Parameter	Description	Default
mysql.host	MySQL server address	localhost
mysql.port	MySQL server port	3306
mysql.user	MySQL username	root
mysql.password	MySQL password	``
mysql.database	Default database	``
permissions.allowDDL	Allow DDL operations	false
permissions.allowDrop	Allow DROP operations	false
permissions.allowDelete	Allow DELETE operations	false
logging.logDir	Log directory	./logs
logging.logFile	Log filename	mcp-mysql.log
logging.ddlLogFile	DDL log filename	ddl.sql

Configuration Examples

Basic Configuration (config.json):

{
  "mysql": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "database": "test_db"
  },
  "permissions": {
    "allowDDL": true,
    "allowDrop": false,
    "allowDelete": true
  },
  "logging": {
    "logDir": "./logs",
    "logFile": "mcp-mysql.log",
    "ddlLogFile": "ddl.sql"
  }
}

Usage

1. Using Default Configuration File

# Direct run with default config.json
mcp-server-mysql

# Using npx (Recommended)
npx @bobliu1996/mcp-server-mysql

2. Specify Configuration File

# Use custom configuration file
mcp-server-mysql --config /path/to/your-config.json

# Using npx
npx @bobliu1996/mcp-server-mysql --config ./my-config.json

3. Show Help

mcp-server-mysql --help

4. Direct Start (Source Installation)

# Use default configuration
npm start

# Use custom configuration
npm start -- --config ./my-config.json

5. Managed Start (Recommended for Production)

# Use default configuration
npm run start-managed

# Use custom configuration
npm run start-managed -- --config ./prod-config.json

Managed start provides:

• Auto-restart (up to 10 times)
• Error recovery
• Process management
• Logging

6. Development Mode

npm run dev

Editor Integration

Cursor Editor Configuration

1. Create .cursor/mcp.json file in your project root:

Using Configuration File (Recommended):

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql", "--config", "./config.json"]
    }
  }
}

Multiple Instances Configuration (Support running multiple servers simultaneously):

{
  "mcpServers": {
    "mysql-dev": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql", "--config", "./config-dev.json"]
    },
    "mysql-prod": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql", "--config", "./config-prod.json"]
    }
  }
}

Tool Name Isolation: Each server instance automatically adds an instance prefix to tool names:

• Development Instance: dev_sql_query, dev_get_database_info, etc.
• Production Instance: prod_sql_query, prod_get_database_info, etc.

Using Environment Variables (Backward Compatible):

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "your_host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

VS Code Configuration

1. Install the MCP extension for VS Code
2. Create .vscode/settings.json file:

Using Configuration File (Recommended):

{
  "mcp.servers": {
    "mysql": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql", "--config", "./config.json"]
    }
  }
}

Using Environment Variables (Backward Compatible):

{
  "mcp.servers": {
    "mysql": {
      "command": "npx",
      "args": ["@bobliu1996/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "your_host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

As MCP Server

The server communicates with MCP clients via stdin/stdout after startup:

{"jsonrpc": "2.0", "id": 1, "method": "initialize", "params": {"protocolVersion": "2025-06-18"}}

Available Tools

1. sql_query: Execute SQL queries

   {
     "jsonrpc": "2.0",
     "id": 2,
     "method": "tools/call",
     "params": {
       "name": "sql_query",
       "arguments": {
         "sql": "SELECT * FROM users LIMIT 10"
       }
     }
   }
   

2. get_database_info: Get database information

   {
     "jsonrpc": "2.0",
     "id": 3,
     "method": "tools/call",
     "params": {
       "name": "get_database_info",
       "arguments": {}
     }
   }
   

3. get_operation_logs: Get operation logs

   {
     "jsonrpc": "2.0",
     "id": 4,
     "method": "tools/call",
     "params": {
       "name": "get_operation_logs",
       "arguments": {
         "limit": 50,
         "offset": 0
       }
     }
   }
   

4. get_ddl_sql_logs: Get DDL SQL operation logs (v2.0.1+)

   {
     "jsonrpc": "2.0",
     "id": 5,
     "method": "tools/call",
     "params": {
       "name": "get_ddl_sql_logs",
       "arguments": {
         "limit": 50,
         "offset": 0
       }
     }
   }
   

5. check_permissions: Check database permissions

   {
     "jsonrpc": "2.0",
     "id": 6,
     "method": "tools/call",
     "params": {
       "name": "check_permissions",
       "arguments": {}
     }
   }
   

Connection Pool Features

• Auto-creation: Automatically creates connection pool on notifications/initialized
• Health checks: Checks connection pool status every 5 minutes
• Auto-reconnection: Automatically recreates connection pool when it fails
• Connection reuse: Uses connection pool for better performance
• Graceful shutdown: Properly releases connections when server shuts down

Logging

General Logs

Log file location: ./logs/mcp-mysql.log

Logged content:

• All requests and responses
• SQL operation records
• Error messages
• Connection pool status changes

DDL SQL Logs (v2.0.1+)

DDL log file location: ./logs/ddl.sql

Features:

• Success-Only Recording: Only successful DDL operations are recorded
• Timestamped Entries: Each operation includes precise timestamp comments
• Auto-Formatting: SQL statements are automatically formatted with semicolon endings
• Executable Format: Can be directly executed to recreate database structure

Example DDL log format:

# 2024-01-15 14:23:45
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
# 2024-01-15 14:24:12
ALTER TABLE users ADD COLUMN email VARCHAR(255);
# 2024-01-15 14:25:33
CREATE INDEX idx_email ON users(email);

DDL Logging Benefits

🔄 Database Synchronization

• Production Sync: Easily synchronize database schema changes from development to production environments
• Multi-Environment Deployment: Apply the same DDL changes across staging, testing, and production databases
• Rollback Support: Maintain a complete history of schema changes for easy rollback operations

📋 Development Workflow

• Schema Versioning: Track database evolution with timestamped change history
• Team Collaboration: Share database structure changes with team members through executable SQL files
• Code Review: Review database changes alongside application code changes

🛡️ Operational Excellence

• Audit Trail: Maintain comprehensive audit logs of all database structure modifications
• Compliance: Meet regulatory requirements for database change tracking
• Disaster Recovery: Quickly rebuild database structure from DDL logs in case of data loss

⚡ Performance & Reliability

• Clean Execution: Only successful operations are recorded, ensuring reliable script execution
• Error Prevention: Failed operations are excluded, preventing script execution errors
• Automated Formatting: Consistent SQL formatting reduces manual errors and improves readability

Error Handling

• Individual request errors don't affect the entire server
• Connection pool errors are automatically recovered
• Process exceptions are automatically restarted (managed mode)

Environment Variables

Variable	Default	Description
MYSQL_HOST	localhost	MySQL host address
MYSQL_PORT	3306	MySQL port
MYSQL_USER	root	MySQL username
MYSQL_PASSWORD		MySQL password
MYSQL_DATABASE		Database name
ALLOW_DDL	false	Whether to allow DDL operations (CREATE, ALTER, TRUNCATE, RENAME, COMMENT). Set to 'true' to enable
ALLOW_DROP	false	Whether to allow DROP operations. Set to 'true' to enable
ALLOW_DELETE	false	Whether to allow DELETE operations. Set to 'true' to enable
MCP_LOG_DIR	./logs	Log directory
MCP_LOG_FILE	mcp-mysql.log	Log filename
MCP_DDL_LOG_FILE	ddl.sql	DDL SQL log filename (v2.0.1+)

Development

Project Structure

mcpmysql/
├── src/
│   └── server-final.js    # Main server file
├── start-server.js        # Managed startup script
├── package.json
└── README.md

Testing

npm test

Quick Start

1. Install Package

npm install -g @bobliu1996/mcp-server-mysql

2. Configure Environment Variables

export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=root
export MYSQL_PASSWORD=your_password
export MYSQL_DATABASE=your_database
export ALLOW_DDL=false
export ALLOW_DROP=false
export ALLOW_DELETE=false

Permission Control Examples:

# Default: Disable all destructive operations (safe mode)
export ALLOW_DDL=false
export ALLOW_DROP=false
export ALLOW_DELETE=false

# Allow DDL but disable DROP and DELETE
export ALLOW_DDL=true
export ALLOW_DROP=false
export ALLOW_DELETE=false

# Allow everything except DELETE
export ALLOW_DDL=true
export ALLOW_DROP=true
export ALLOW_DELETE=false

# Enable all operations (use with caution)
export ALLOW_DDL=true
export ALLOW_DROP=true
export ALLOW_DELETE=true

3. Run Server

mcp-server-mysql

License

MIT