MCP Server for MySQL based on NodeJS

Demo

A Model Context Protocol server that provides access to MySQL databases with fine-grained access control. Supports multiple databases, access modes, and table-level permissions.

Features

Multi-database support: Connect to multiple MySQL databases simultaneously
Access control: readonly, readwrite, full modes per database
Table-level permissions: Whitelist/blacklist with wildcards and regex
Read-only transaction protection: Database-level safety for readonly mode
Backward compatible: Works with single database environment variables

Components

Tools

Tool	Description
`mysql_query`	Read-only SELECT queries (always available)
`mysql_execute`	Write operations (only visible when writable databases exist)

Resources

mysql://connections: List of database connections with access modes
mysql://{db}/tables/{table}/schema: Table schema

Prompts

mysql_usage_guide: Dynamic guide based on configured databases

Configuration

Config File

Create ~/.mcp_mysql/databases.json:

{
  "databases": [
    {
      "name": "prod",
      "host": "prod-db.example.com",
      "port": 3306,
      "user": "readonly_user",
      "password": "***",
      "database": "production",
      "accessMode": "readonly"
    },
    {
      "name": "dev",
      "host": "localhost",
      "port": 3306,
      "user": "dev_user",
      "password": "***",
      "database": "development",
      "accessMode": "readwrite",
      "allowedTables": ["log_*", "tmp_*", "/^test_.*/i"]
    }
  ]
}

Access Modes

Mode	SELECT	INSERT/UPDATE/DELETE	DDL
`readonly` (default)	✅	❌	❌
`readwrite`	✅	✅	❌
`full`	✅	✅	✅

Table Patterns

Format	Example	Matches
Exact	`log_table`	Only `log_table`
Wildcard	`log_*`	`log_` prefix
Regex	`/^test_\d+$/i`	`test_` + digits

Environment Variables

Variable	Description
`MYSQL_CONFIG_PATH`	Custom config file path
`MYSQL_HOST`	Legacy single-database host
`MYSQL_PORT`	Legacy single-database port
`MYSQL_USER`	Legacy single-database user
`MYSQL_PASS`	Legacy single-database password
`MYSQL_DB`	Legacy single-database name

Usage with Claude Desktop

Multi-database configuration (Recommended)

First, create ~/.mcp_mysql/databases.json with your database configurations.

Then add to your claude_desktop_config.json:

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "@zhengyun1008/mcp-server-mysql"
      ]
    }
  }
}

Single database (Legacy, backward compatible)

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "@zhengyun1008/mcp-server-mysql"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "",
        "MYSQL_DB": "db_name"
      }
    }
  }
}

Usage Examples

User: "查询 prod 库中的用户表"
→ mysql_query(database="prod", sql="SELECT * FROM users")

User: "在 dev 库的 log_test 表插入一条记录"
→ mysql_execute(database="dev", sql="INSERT INTO log_test ...", confirm=true)

Security

readonly mode: Uses SET SESSION TRANSACTION READ ONLY for database-level protection
SQL validation: Validates statement type before execution
Table validation: Checks whitelist/blacklist before write operations
Confirmation required: Write operations require confirm=true

Troubleshooting

If you encounter an error "Could not connect to MCP server mcp-server-mysql", you may need to explicitly set the path of all required binaries such as the configuration below:

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "/path/to/npx/binary/npx",
      "args": [
        "-y",
        "@zhengyun1008/mcp-server-mysql"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "",
        "MYSQL_DB": "db_name",
        "PATH": "/path/to/node/bin:/usr/bin:/bin"
      }
    }
  }
}

License

This MCP server is licensed under the MIT License. This means you are free to use, modify, and distribute the software, subject to the terms and conditions of the MIT License. For more details, please see the LICENSE file in the project repository.

mcp-server-mysql