MCP MySQL Server

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

Version History

v3.1.0 (Latest)

• ✅ BIGINT Precision Support: Added supportBigNumbers and bigNumberStrings options to prevent precision loss for large integers (e.g., order_id, user_id > 2^53 - 1). Values are returned as strings to maintain full precision.

v3.0.0

• ✅ 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

1. 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

1. Install the MCP extension for VS Code

2. 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

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: ./.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

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