返回

MCP服务器连接MySQL数据库配置指南

什么是MCP？

MCP（Model Context Protocol）是Anthropic开发的协议，允许AI助手（如Claude）安全地访问外部数据源和工具，包括数据库。

配置方案选择

方案一：使用官方MCP MySQL服务器（推荐）

这是最简单、最安全的方法。

1. 安装MCP MySQL服务器

npm install -g @modelcontextprotocol/server-mysql

或者使用npx直接运行：

npx @modelcontextprotocol/server-mysql

2. 创建配置文件

在项目根目录创建 mcp-config.json：

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": [
        "-e",
        "const { MySQLServer } = require('@modelcontextprotocol/server-mysql');\nnew MySQLServer({\n  host: 'localhost',\n  port: 3306,\n  user: 'your_username',\n  password: 'your_password',\n  database: 'your_database'\n}).run();"
      ],
      "env": {
        "NODE_ENV": "production"
      }
    }
  }
}

3. 安全配置（使用环境变量）

创建 .env 文件：

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database

更新配置使用环境变量：

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": [
        "-e",
        "const { MySQLServer } = require('@modelcontextprotocol/server-mysql');\nnew MySQLServer({\n  host: process.env.MYSQL_HOST || 'localhost',\n  port: parseInt(process.env.MYSQL_PORT) || 3306,\n  user: process.env.MYSQL_USER,\n  password: process.env.MYSQL_PASSWORD,\n  database: process.env.MYSQL_DATABASE\n}).run();"
      ],
      "env": {
        "NODE_ENV": "production",
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "${env:MYSQL_USER}",
        "MYSQL_PASSWORD": "${env:MYSQL_PASSWORD}",
        "MYSQL_DATABASE": "${env:MYSQL_DATABASE}"
      }
    }
  }
}

方案二：自定义MCP服务器

如果你需要更复杂的控制，可以创建自定义MCP服务器：

1. 创建项目结构

mysql-mcp-server/
├── package.json
├── server.js
├── .env
└── README.md

2. package.json

{
  "name": "mysql-mcp-server",
  "version": "1.0.0",
  "type": "module",
  "dependencies": {
    "@modelcontextprotocol/sdk": "^0.5.0",
    "mysql2": "^3.11.0",
    "dotenv": "^16.4.5"
  },
  "scripts": {
    "start": "node server.js"
  }
}

3. server.js

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import mysql from "mysql2/promise";
import dotenv from "dotenv";

dotenv.config();

class MySQLServer {
  constructor() {
    this.server = new Server(
      {
        name: "mysql-server",
        version: "1.0.0",
      },
      {
        capabilities: {
          resources: {},
          tools: {
            executeQuery: {
              description: "执行SQL查询",
              inputSchema: {
                type: "object",
                properties: {
                  query: {
                    type: "string",
                    description: "SQL查询语句",
                  },
                  params: {
                    type: "array",
                    description: "查询参数",
                    items: { type: "string" },
                  },
                },
                required: ["query"],
              },
            },
            listTables: {
              description: "列出所有表",
              inputSchema: {
                type: "object",
                properties: {},
              },
            },
            describeTable: {
              description: "查看表结构",
              inputSchema: {
                type: "object",
                properties: {
                  tableName: {
                    type: "string",
                    description: "表名",
                  },
                },
                required: ["tableName"],
              },
            },
          },
        },
      }
    );

    this.connection = null;
    this.setupHandlers();
  }

  async connect() {
    try {
      this.connection = await mysql.createConnection({
        host: process.env.MYSQL_HOST || "localhost",
        port: process.env.MYSQL_PORT || 3306,
        user: process.env.MYSQL_USER,
        password: process.env.MYSQL_PASSWORD,
        database: process.env.MYSQL_DATABASE,
      });
      console.error("Connected to MySQL database");
    } catch (error) {
      console.error("Failed to connect to MySQL:", error.message);
      throw error;
    }
  }

  setupHandlers() {
    this.server.setRequestHandler("tools/executeQuery", async request => {
      const { query, params = [] } = request.params;

      try {
        const [rows] = await this.connection.execute(query, params);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      } catch (error) {
        return {
          content: [
            {
              type: "text",
              text: `Error: ${error.message}`,
            },
          ],
          isError: true,
        };
      }
    });

    this.server.setRequestHandler("tools/listTables", async () => {
      try {
        const [rows] = await this.connection.execute(
          "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()"
        );
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(
                rows.map(r => r.table_name),
                null,
                2
              ),
            },
          ],
        };
      } catch (error) {
        return {
          content: [
            {
              type: "text",
              text: `Error: ${error.message}`,
            },
          ],
          isError: true,
        };
      }
    });

    this.server.setRequestHandler("tools/describeTable", async request => {
      const { tableName } = request.params;

      try {
        const [rows] = await this.connection.execute(`DESCRIBE ${tableName}`);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      } catch (error) {
        return {
          content: [
            {
              type: "text",
              text: `Error: ${error.message}`,
            },
          ],
          isError: true,
        };
      }
    });
  }

  async run() {
    await this.connect();
    const transport = new StdioServerTransport();
    await this.server.connect(transport);
    console.error("MySQL MCP server running on stdio");
  }
}

const server = new MySQLServer();
server.run().catch(error => {
  console.error("Server error:", error);
  process.exit(1);
});

在WorkBuddy/Coding Copilot中配置

方法1：通过配置文件

  1. 确保你的MySQL MCP服务器已经安装并运行
  2. 在WorkBuddy/Coding Copilot设置中，添加MCP服务器配置：

对于VS Code插件，编辑 settings.json：

{
  "workbuddy.mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/path/to/your/mysql-server/server.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

方法2：使用Claw配置

创建 mcp-mysql-config.ps1 PowerShell脚本：

# mcp-mysql-config.ps1
# MySQL MCP服务器配置脚本

$env:MYSQL_HOST = "localhost"
$env:MYSQL_PORT = "3306"
$env:MYSQL_USER = "your_username"
$env:MYSQL_PASSWORD = "your_password"
$env:MYSQL_DATABASE = "your_database"

# 启动MCP MySQL服务器
$serverPath = Join-Path $PSScriptRoot "mysql-mcp-server\server.js"
if (Test-Path $serverPath) {
    Write-Host "启动MySQL MCP服务器..." -ForegroundColor Green
    node $serverPath
} else {
    Write-Host "未找到MCP服务器，请先安装" -ForegroundColor Yellow
    Write-Host "运行: npm install -g @modelcontextprotocol/server-mysql" -ForegroundColor Cyan
}

安全最佳实践

1. 使用环境变量

2. 权限限制

-- 创建专门用于MCP的数据库用户
CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, EXECUTE ON your_database.* TO 'mcp_user'@'localhost';
-- 限制为只读权限（根据需求调整）

3. 连接池配置

// 在生产环境中使用连接池
const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

测试连接

创建测试脚本 test-mysql-connection.ps1：

# test-mysql-connection.ps1
# 测试MySQL MCP服务器连接

param(
    [string]$Host = "localhost",
    [int]$Port = 3306,
    [string]$User,
    [string]$Password,
    [string]$Database
)

Write-Host "=== MySQL MCP服务器连接测试 ===" -ForegroundColor Cyan

# 检查Node.js
if (-not (Get-Command node -ErrorAction SilentlyContinue)) {
    Write-Host "错误: Node.js未安装" -ForegroundColor Red
    exit 1
}

# 检查MySQL客户端
try {
    $mysqlTest = mysql --version 2>$null
    if ($mysqlTest) {
        Write-Host "MySQL客户端: $mysqlTest" -ForegroundColor Green
    }
} catch {
    Write-Host "提示: MySQL客户端未安装，建议安装MySQL Workbench或CLI工具" -ForegroundColor Yellow
}

# 测试连接
Write-Host "`n测试直接MySQL连接..." -ForegroundColor Cyan
$connectionString = "mysql -h $Host -P $Port -u $User -p$Password $Database -e 'SELECT 1 as test'"
try {
    Invoke-Expression $connectionString 2>$null
    if ($LASTEXITCODE -eq 0) {
        Write-Host "✓ 直接连接成功" -ForegroundColor Green
    } else {
        Write-Host "✗ 直接连接失败" -ForegroundColor Red
    }
} catch {
    Write-Host "✗ 连接测试异常: $_" -ForegroundColor Red
}

Write-Host "`n=== 配置完成 ===" -ForegroundColor Green
Write-Host "1. 安装MCP MySQL服务器: npm install -g @modelcontextprotocol/server-mysql"
Write-Host "2. 启动服务器: npx @modelcontextprotocol/server-mysql"
Write-Host "3. 在WorkBuddy中配置MCP服务器设置" -ForegroundColor Cyan

故障排除

常见问题：

  1. 连接被拒绝

    • 检查MySQL服务是否运行：sudo service mysql status 或 Get-Service MySQL
    • 检查防火墙设置
    • 确保用户有远程连接权限（如果需要）

  2. 认证失败

    • 验证用户名和密码
    • 检查MySQL认证插件（mysql_native_password vs caching_sha2_password）

  3. MCP服务器不响应

    • 检查Node.js版本（需要Node 16+）
    • 查看服务器日志输出
    • 验证MCP协议版本兼容性

调试命令：

# 检查MCP服务器状态
node -e "console.log(require('@modelcontextprotocol/server-mysql'))"

# 测试MCP连接
curl -X POST http://localhost:3000/mcp -H "Content-Type: application/json" -d '{"method":"list_tables"}'

进阶功能

1. 添加更多工具

你可以扩展MCP服务器添加更多功能：

2. 集成监控

3. Web界面

创建简单的Web界面来管理MCP服务器连接。

快速开始脚本

创建 setup-mcp-mysql.ps1：

# setup-mcp-mysql.ps1
# 一键安装和配置MySQL MCP服务器

param(
    [string]$MySQLHost = "localhost",
    [string]$MySQLUser,
    [string]$MySQLPassword,
    [string]$MySQLDatabase
)

Write-Host "=== MySQL MCP服务器安装向导 ===" -ForegroundColor Cyan

# 1. 检查Node.js
Write-Host "`n1. 检查Node.js..." -ForegroundColor Yellow
$nodeVersion = node --version 2>$null
if ($nodeVersion) {
    Write-Host "   ✓ Node.js已安装: $nodeVersion" -ForegroundColor Green
} else {
    Write-Host "   ✗ Node.js未安装" -ForegroundColor Red
    Write-Host "   请从 https://nodejs.org/ 下载安装" -ForegroundColor Yellow
    exit 1
}

# 2. 安装MCP MySQL服务器
Write-Host "`n2. 安装MCP MySQL服务器..." -ForegroundColor Yellow
npm install -g @modelcontextprotocol/server-mysql
if ($LASTEXITCODE -eq 0) {
    Write-Host "   ✓ 安装成功" -ForegroundColor Green
} else {
    Write-Host "   ✗ 安装失败" -ForegroundColor Red
    exit 1
}

# 3. 创建配置文件
Write-Host "`n3. 创建配置文件..." -ForegroundColor Yellow
$configContent = @"
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": [
        "-e",
        "const { MySQLServer } = require('@modelcontextprotocol/server-mysql');\nnew MySQLServer({\n  host: '$MySQLHost',\n  port: 3306,\n  user: '$MySQLUser',\n  password: '$MySQLPassword',\n  database: '$MySQLDatabase'\n}).run();"
      ]
    }
  }
}
"@

$configContent | Out-File -FilePath "mcp-config.json" -Encoding UTF8
Write-Host "   ✓ 配置文件已创建: mcp-config.json" -ForegroundColor Green

# 4. 创建环境文件（可选）
Write-Host "`n4. 创建环境配置文件..." -ForegroundColor Yellow
$envContent = @"
MYSQL_HOST=$MySQLHost
MYSQL_PORT=3306
MYSQL_USER=$MySQLUser
MYSQL_PASSWORD=$MySQLPassword
MYSQL_DATABASE=$MySQLDatabase
"@

$envContent | Out-File -FilePath ".env.example" -Encoding UTF8
Write-Host "   ✓ 环境配置文件已创建: .env.example" -ForegroundColor Green
Write-Host "   提示: 请将.env.example复制为.env并确保在.gitignore中排除" -ForegroundColor Cyan

# 5. 测试脚本
Write-Host "`n5. 创建测试脚本..." -ForegroundColor Yellow
$testScript = @'
# test-mysql-mcp.ps1
# 测试MySQL MCP连接

Write-Host "启动MySQL MCP服务器测试..." -ForegroundColor Cyan

# 设置环境变量（从.env文件读取）
if (Test-Path ".env") {
    Get-Content ".env" | ForEach-Object {
        if ($_ -match "^\s*([^=]+)=(.*)") {
            $key = $matches[1].Trim()
            $value = $matches[2].Trim()
            [Environment]::SetEnvironmentVariable($key, $value)
        }
    }
}

# 启动MCP服务器
try {
    npx @modelcontextprotocol/server-mysql
} catch {
    Write-Host "启动失败: $_" -ForegroundColor Red
}
'@

$testScript | Out-File -FilePath "test-mysql-mcp.ps1" -Encoding UTF8
Write-Host "   ✓ 测试脚本已创建: test-mysql-mcp.ps1" -ForegroundColor Green

Write-Host "`n=== 安装完成 ===" -ForegroundColor Green
Write-Host "下一步:"
Write-Host "1. 编辑.env文件，填入你的MySQL连接信息"
Write-Host "2. 运行: .\test-mysql-mcp.ps1 测试连接"
Write-Host "3. 在WorkBuddy/Coding Copilot中配置MCP服务器"
Write-Host "`n提示: 确保你的MySQL数据库允许连接" -ForegroundColor Cyan

总结

通过MCP连接MySQL数据库，你可以：

  1. 让AI助手直接查询数据库数据
  2. 自动生成SQL查询语句
  3. 分析数据库结构
  4. 导出数据报告
  5. 与现有自动化脚本集成

选择适合你需求的方案开始配置吧！

Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Previous Post
OpenClaw 安装 & 卸载教程（2026 最新）
Next Post
SQL获取分组最新记录的四种姿势