mcp-server-mysql by liliangshan - MCP Server

MCP MySQL Server

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

Version History

v3.0.0 (Latest)

✅ Readonly Mode: Added READONLY environment variable - when enabled, only SELECT and SHOW commands are allowed (highest priority check)
✅ Tool Prefix Support: Added TOOL_PREFIX environment variable for tool name isolation and config separation
✅ Project Branding: Added PROJECT_NAME environment variable for custom tool descriptions
✅ Enhanced Permission Check: Improved check_permissions tool with detailed messages and readonly mode warnings
✅ Default Log Path: Changed default log directory from ./logs to ./.setting (or ./.setting.<TOOL_PREFIX> if prefix is set)
✅ Multiple Instance Support: Full support for running multiple MySQL server instances with isolated configurations
✅ Improved CLI: Updated CLI to support all new environment variables and log path configuration

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 @liangshanli/mcp-server-mysql

Local Installation

npm install @liangshanli/mcp-server-mysql

From Source

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

Configuration

Set 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 READONLY=false
export ALLOW_DDL=true
export ALLOW_DROP=false
export ALLOW_DELETE=false

# Optional: Tool prefix for config isolation
export TOOL_PREFIX="projA"

# Optional: Project branding
export PROJECT_NAME="MyProject"

Usage

1. Direct Run (Global Installation)

mcp-server-mysql

2. Using npx (Recommended)

npx @liangshanli/mcp-server-mysql

3. Direct Start (Source Installation)

npm start

4. Managed Start (Recommended for Production)

npm run start-managed

Managed start provides:

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

5. Development Mode

npm run dev

Editor Integration

Cursor Editor Configuration

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

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

VS Code Configuration

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

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

Multiple MySQL Server Instances Support

You can configure multiple MySQL server instances with different TOOL_PREFIX and PROJECT_NAME to isolate tools and configurations. This is useful when you need to connect to multiple databases simultaneously.

Example: Cursor Editor Configuration

Create .cursor/mcp.json file:

{
  "mcpServers": {
    "local-mysql": {
      "disabled": false,
      "timeout": 60,
      "command": "npx",
      "args": ["@liangshanli/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "ALLOW_DDL": "true",
        "ALLOW_DROP": "true",
        "ALLOW_DELETE": "false",
        "TOOL_PREFIX": "local",
        "PROJECT_NAME": "local-mysql"
      }
    },
    "online-mysql": {
      "disabled": false,
      "timeout": 60,
      "command": "npx",
      "args": ["@liangshanli/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "your_remote_host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "READONLY": "true",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false",
        "TOOL_PREFIX": "online",
        "PROJECT_NAME": "online-mysql"
      }
    }
  }
}

Benefits of Multiple Instances:

Tool Isolation: Each instance has its own tool names (e.g., local_sql_query, online_sql_query)
Config Isolation: Logs and DDL files are stored in separate directories (e.g., ./.setting.local/, ./.setting.online/)
Different Permissions: Configure different permission levels for each instance (e.g., readonly for production, full access for development)
Project Branding: Each instance can have its own project name for better identification

Note: When using multiple instances, tools will be prefixed with TOOL_PREFIX. For example:

local_sql_query - queries the local database
online_sql_query - queries the online database (readonly)

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

sql_query: Execute SQL queries

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

get_database_info: Get database information

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

get_operation_logs: Get operation logs

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

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
    }
  }
}

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: ./.setting/mcp-mysql.log (or ./.setting.<TOOL_PREFIX>/mcp-mysql.log if TOOL_PREFIX is set)

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: ./.setting/ddl.sql (or ./.setting.<TOOL_PREFIX>/ddl.sql if TOOL_PREFIX is set)

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
READONLY	false	If set to 'true', only SELECT and SHOW commands are allowed. This check has the highest priority and overrides all other permission settings
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
TOOL_PREFIX		Optional tool prefix for tool names and config isolation. Example: `export TOOL_PREFIX="projA"`
PROJECT_NAME		Optional project branding for tool descriptions
MCP_LOG_DIR	./.setting (or ./.setting.<TOOL_PREFIX> if TOOL_PREFIX is set)	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 @liangshanli/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 READONLY=false
export ALLOW_DDL=false
export ALLOW_DROP=false
export ALLOW_DELETE=false

Permission Control Examples:

# Readonly mode: Only SELECT and SHOW commands allowed (highest priority)
export READONLY=true

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

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

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

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

3. Run Server

mcp-server-mysql

License

MIT