一个支持DDL操作、权限控制和操作日志的MCP MySQL服务器，支持 VS Code Cursor等IDEA

原创 于 2025-07-29 04:38:58 发布 · 825 阅读 · 12 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #服务器 #intellij-idea

MCP MySQL 服务器

一个支持DDL操作、权限控制和操作日志的MCP MySQL服务器。

功能特性

  • ✅ 支持SQL查询执行（DDL和DML）
  • ✅ 数据库信息获取
  • ✅ 操作日志记录
  • ✅ 连接池管理
  • ✅ 自动重连机制
  • ✅ 健康检查
  • ✅ 错误处理和恢复

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

安装

全局安装（推荐）

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

本地安装

npm install @liangshanli/mcp-server-mysql

从源码安装

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

配置

设置环境变量：

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

使用方法

1. 全局安装后直接运行

mcp-server-mysql

2. 使用 npx 运行（推荐）

npx @liangshanli/mcp-server-mysql

3. 直接启动（源码安装）

npm start

4. 托管启动（推荐用于生产环境）

npm run start-managed

托管启动提供以下功能：

  • 自动重启（最多10次）
  • 错误恢复
  • 进程管理
  • 日志记录

5. 开发模式

npm run dev

编辑器集成

Cursor 编辑器配置

  1. 在项目根目录创建 .cursor/mcp.json 文件：
{
  "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",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

VS Code 配置

  1. 安装 VS Code 的 MCP 扩展
  2. 创建 .vscode/settings.json 文件：
{
  "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",
        "ALLOW_DDL": "false",
        "ALLOW_DROP": "false",
        "ALLOW_DELETE": "false"
      }
    }
  }
}

作为MCP服务器

服务器启动后，通过stdin/stdout与MCP客户端通信：

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

可用工具

  1. sql_query: 执行SQL查询

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

  2. get_database_info: 获取数据库信息

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

  3. get_operation_logs: 获取操作日志

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

连接池特性

  • 自动创建: 在notifications/initialized时自动创建连接池
  • 健康检查: 每5分钟检查连接池状态
  • 自动重连: 连接池失效时自动重新创建
  • 连接复用: 使用连接池提高性能
  • 优雅关闭: 服务器关闭时正确释放连接

日志

日志文件位置：./logs/mcp-mysql.log

记录内容：

  • 所有请求和响应
  • SQL操作记录
  • 错误信息
  • 连接池状态变化

错误处理

  • 单个请求错误不会影响整个服务器
  • 连接池错误会自动恢复
  • 进程异常会自动重启（托管模式）

环境变量

变量名默认值说明
MYSQL_HOSTlocalhostMySQL主机地址
MYSQL_PORT3306MySQL端口
MYSQL_USERrootMySQL用户名
MYSQL_PASSWORDMySQL密码
MYSQL_DATABASE数据库名
ALLOW_DDLfalse是否允许DDL操作（CREATE、ALTER、TRUNCATE、RENAME、COMMENT）。设置为’true’启用
ALLOW_DROPfalse是否允许DROP操作。设置为’true’启用
ALLOW_DELETEfalse是否允许DELETE操作。设置为’true’启用
MCP_LOG_DIR./logs日志目录
MCP_LOG_FILEmcp-mysql.log日志文件名

开发

项目结构

mcpmysql/
├── src/
│   └── server-final.js    # 主服务器文件
├── start-server.js        # 托管启动脚本
├── package.json
└── README.md

测试

npm test

快速开始

1. 安装包

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

2. 配置环境变量

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

权限控制示例：

# 默认：禁用所有破坏性操作（安全模式）
export ALLOW_DDL=false
export ALLOW_DROP=false
export ALLOW_DELETE=false

# 允许DDL但禁用DROP和DELETE
export ALLOW_DDL=true
export ALLOW_DROP=false
export ALLOW_DELETE=false

# 允许所有操作但禁用DELETE
export ALLOW_DDL=true
export ALLOW_DROP=true
export ALLOW_DELETE=false

# 启用所有操作（谨慎使用）
export ALLOW_DDL=true
export ALLOW_DROP=true
export ALLOW_DELETE=true

3. 运行服务器

mcp-server-mysql

许可证

MIT

MCP MySQL Server

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

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

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",
        "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:
{
  "mcp.servers": {
    "mysql": {
      "command": "npx",
      "args": ["@liangshanli/mcp-server-mysql"],
      "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",
        "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
    }
  }
}

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

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

Logged content:

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

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

VariableDefaultDescription
MYSQL_HOSTlocalhostMySQL host address
MYSQL_PORT3306MySQL port
MYSQL_USERrootMySQL username
MYSQL_PASSWORDMySQL password
MYSQL_DATABASEDatabase name
ALLOW_DDLfalseWhether to allow DDL operations (CREATE, ALTER, TRUNCATE, RENAME, COMMENT). Set to ‘true’ to enable
ALLOW_DROPfalseWhether to allow DROP operations. Set to ‘true’ to enable
ALLOW_DELETEfalseWhether to allow DELETE operations. Set to ‘true’ to enable
MCP_LOG_DIR./logsLog directory
MCP_LOG_FILEmcp-mysql.logLog filename

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

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

星驰云

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值