mcp-server-mysql — Claude MCP 必备工具中文教程

Requirements

• Node.js v20 or higher
• MySQL 5.7 or higher (MySQL 8.0+ recommended)
• MySQL user with appropriate permissions for the operations you need
• For write operations: MySQL user with INSERT, UPDATE, and/or DELETE privileges

Installation

Database Setup

Before running tests, you need to set up the test database and seed it with test data:

1. Create Test Database and User

   -- Connect as root and create test database
   CREATE DATABASE IF NOT EXISTS mcp_test;

   -- Create test user with appropriate permissions
   CREATE USER IF NOT EXISTS 'mcp_test'@'localhost' IDENTIFIED BY 'mcp_test_password';
   GRANT ALL PRIVILEGES ON mcp_test.* TO 'mcp_test'@'localhost';
   FLUSH PRIVILEGES;
   

1. Run Database Setup Script

   # Run the database setup script
   pnpm run setup:test:db
   

This will create the necessary tables and seed data. The script is located in scripts/setup-test-db.ts

3. Configure Test Environment Create a .env.test file in the project root (if not existing):

   MYSQL_HOST=127.0.0.1
   MYSQL_PORT=3306
   MYSQL_USER=mcp_test
   MYSQL_PASS=mcp_test_password
   MYSQL_DB=mcp_test
   

4. Update package.json Scripts Add these scripts to your package.json:

   {
     "scripts": {
       "setup:test:db": "ts-node scripts/setup-test-db.ts",
       "pretest": "pnpm run setup:test:db",
       "test": "vitest run",
       "test:watch": "vitest",
       "test:coverage": "vitest run --coverage"
     }
   }
   

First-time setup

pnpm run setup:test:db

Development Setup

1. Clone the repository
2. Install dependencies: pnpm install
3. Build the project: pnpm run build
4. Run tests: pnpm test

Quick Start for Claude Code Users

1. Read the Setup Guide: See PROJECT_SETUP_GUIDE.md for detailed instructions
2. Configure SSH Tunnels: Set up automatic SSH tunnels for remote databases
3. Use with Claude: Integrated MCP server works seamlessly with Claude Code

A Model Context Protocol server that provides access to MySQL databases through SSH tunnels. This server enables Claude and other LLMs to inspect database schemas and execute SQL queries securely.

Configuration Example

SCHEMA_INSERT_PERMISSIONS=development:true,test:true,production:false
SCHEMA_UPDATE_PERMISSIONS=development:true,test:true,production:false
SCHEMA_DELETE_PERMISSIONS=development:false,test:true,production:false
SCHEMA_DDL_PERMISSIONS=development:false,test:true,production:false

For complete details and security recommendations, see README-MULTI-DB.md.

List all configured servers

claude mcp list

Configuration

Automatic Configuration with Smithery

If you installed using Smithery, your configuration is already set up. You can view or modify it with:

smithery configure @benborla29/mcp-server-mysql

When reconfiguring, you can update any of the MySQL connection details as well as the write operation settings:

• Basic connection settings:
• MySQL Host, Port, User, Password, Database
• SSL/TLS configuration (if your database requires secure connections)

• Write operation permissions:
• Allow INSERT Operations: Set to true if you want to allow adding new data
• Allow UPDATE Operations: Set to true if you want to allow updating existing data
• Allow DELETE Operations: Set to true if you want to allow deleting data

For security reasons, all write operations are disabled by default. Only enable these settings if you specifically need Claude to modify your database data.

Advanced Configuration Options

For more control over the MCP server's behavior, you can use these advanced configuration options:

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "/path/to/npx/binary/npx",
      "args": [
        "-y",
        "@benborla29/mcp-server-mysql"
      ],
      "env": {
        // Basic connection settings
        "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",

        // Performance settings
        "MYSQL_POOL_SIZE": "10",
        "MYSQL_QUERY_TIMEOUT": "30000",
        "MYSQL_CACHE_TTL": "60000",

        // Security settings
        "MYSQL_RATE_LIMIT": "100",
        "MYSQL_MAX_QUERY_COMPLEXITY": "1000",
        "MYSQL_SSL": "true",

        // Monitoring settings
        "ENABLE_LOGGING": "true",
        "MYSQL_LOG_LEVEL": "info",
        "MYSQL_METRICS_ENABLED": "true",

        // Write operation flags
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false"
      }
    }
  }
}

Environment Variables

Performance Configuration

• MYSQL_POOL_SIZE: Connection pool size (default: "10")
• MYSQL_QUERY_TIMEOUT: Query timeout in milliseconds (default: "30000")
• MYSQL_CACHE_TTL: Cache time-to-live in milliseconds (default: "60000")
• MYSQL_QUEUE_LIMIT: Maximum number of queued connection requests (default: "100")
• MYSQL_CONNECT_TIMEOUT: Connection timeout in milliseconds (default: "10000")

Security Configuration

• MYSQL_RATE_LIMIT: Maximum queries per minute (default: "100")
• MYSQL_MAX_QUERY_COMPLEXITY: Maximum query complexity score (default: "1000")
• MYSQL_SSL: Enable SSL/TLS encryption (default: "false")
• MYSQL_SSL_CA: Path to SSL CA certificate file (PEM format). Only used when MYSQL_SSL=true. Required for connecting to MySQL instances with self-signed certificates or custom CAs.
• MYSQL_SSL_CERT: Path to the client certificate file (PEM format) for mTLS. Only used when MYSQL_SSL=true. Enables mutual TLS (mTLS) authentication, where both the server and client present certificates. Required by some database configurations that enforce client certificate verification.
• MYSQL_SSL_KEY: Path to the client private key file (PEM format) for mTLS. Only used when MYSQL_SSL=true. Must correspond to the certificate specified by MYSQL_SSL_CERT.
• ALLOW_INSERT_OPERATION: Enable INSERT operations (default: "false")
• ALLOW_UPDATE_OPERATION: Enable UPDATE operations (default: "false")
• ALLOW_DELETE_OPERATION: Enable DELETE operations (default: "false")
• ALLOW_DDL_OPERATION: Enable DDL operations (default: "false")
• MYSQL_DISABLE_READ_ONLY_TRANSACTIONS: [NEW] Disable read-only transaction enforcement (default: "false") ⚠️ Security Warning: Only enable this if you need full write capabilities and trust the LLM with your database
• SCHEMA_INSERT_PERMISSIONS: Schema-specific INSERT permissions
• SCHEMA_UPDATE_PERMISSIONS: Schema-specific UPDATE permissions
• SCHEMA_DELETE_PERMISSIONS: Schema-specific DELETE permissions
• SCHEMA_DDL_PERMISSIONS: Schema-specific DDL permissions
• MULTI_DB_WRITE_MODE: Enable write operations in multi-DB mode (default: "false")

Timezone and Date Configuration

• MYSQL_TIMEZONE: Set the timezone for date/time values. Accepts formats like +08:00 (UTC+8), -05:00 (UTC-5), Z (UTC), or local (system timezone). Useful for ensuring consistent date/time handling across different server locations.
• MYSQL_DATE_STRINGS: When set to "true", returns date/datetime values as strings instead of JavaScript Date objects. This preserves the exact database values without any timezone conversion, which is particularly useful for:
• Applications that need precise control over date formatting
• Cross-timezone database operations
• Avoiding JavaScript Date timezone quirks

Monitoring Configuration

• MYSQL_ENABLE_LOGGING: Enable query logging (default: "false")
• MYSQL_LOG_LEVEL: Logging level (default: "info")
• MYSQL_METRICS_ENABLED: Enable performance metrics (default: "false")

Remote MCP Configuration

• IS_REMOTE_MCP: Enable remote MCP mode (default: "false")
• REMOTE_SECRET_KEY: Secret key for remote MCP authentication (default: ""). If not provided, remote MCP mode will be disabled.
• PORT: Port number for the remote MCP server (default: 3000)

Components

Troubleshooting