MySQL MCP Server

English | 简体中文

A Model Context Protocol (MCP) server implementation for MySQL databases, enabling AI assistants like Claude to interact with MySQL databases in a safe, standardized, and controlled way.

Features

• MCP Protocol Compliant: Fully implements the Model Context Protocol specification

• Safe Query Execution: Built-in query validation and safety controls

• Schema Inspection: Tools to explore database structure

• Transaction Support: Full transaction management (BEGIN, COMMIT, ROLLBACK)

• Resource Exposure: Database schemas exposed as MCP resources

• Comprehensive Error Handling: Detailed error messages with SQL state codes

• Property-Based Testing: Extensively tested with property-based testing for correctness

Installation

From Source

# Clone the repository
git clone <repository-url>
cd mysql-mcp-server

# Install dependencies
npm install

# Build the project
npm run build

# Link globally (makes the command available system-wide)
npm link

After installation, the mysql-mcp-server command will be available system-wide.

Configuration

The server is configured entirely through environment variables:

Required Environment Variables

• MYSQL_HOST - MySQL server hostname (default: localhost)

• MYSQL_PORT - MySQL server port (default: 3306)

• MYSQL_USER - MySQL username (required)

• MYSQL_PASSWORD - MySQL password (required)

• MYSQL_DATABASE - Database name (required)

Optional Environment Variables

• MYSQL_CONNECTION_LIMIT - Maximum number of connections in pool (default: 10)

• MAX_SELECT_ROWS - Maximum rows returned by SELECT queries (default: 1000)

• ALLOW_DDL - Allow DDL operations (CREATE, DROP, ALTER) (default: false)

• ALLOW_MULTIPLE_STATEMENTS - Allow multiple SQL statements (default: false)

• REQUIRE_WHERE_CLAUSE - Require WHERE clause for UPDATE/DELETE (default: true)

• MCP_LOG_LEVEL - Logging level: debug, info, warn, error (default: info)

Usage

With Claude Desktop

Add to your Claude Desktop configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "mysql": {
      "command": "mysql-mcp-server",
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

With Cursor

Add to your Cursor MCP settings (.cursor/mcp.json in your project):

{
  "mcpServers": {
    "mysql": {
      "command": "mysql-mcp-server",
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

With npx (Direct Execution)

You can also run the server directly using npx without installation. First, build the project locally:

# In the project directory
npm run build

Then configure your MCP client to use npx with the local path:

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/absolute/path/to/mysql-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Available Tools

The server provides the following MCP tools:

1. query

Execute SQL queries (SELECT, INSERT, UPDATE, DELETE)

// Example usage in Claude
"Execute a query to find all users: SELECT * FROM users WHERE active = 1"

Safety Features:

• Automatically adds LIMIT to SELECT queries without one

• Rejects DELETE/UPDATE without WHERE clause (configurable)

• Rejects multiple statements

• Rejects DDL operations by default

2. list_tables

List all tables in the current database

// Example usage
"Show me all tables in the database"

3. describe_table

Get detailed schema information for a specific table

// Example usage
"Describe the structure of the users table"

4. show_indexes

Show all indexes for a specific table

// Example usage
"Show me the indexes on the orders table"

5. begin_transaction

Start a new database transaction

6. commit_transaction

Commit the current transaction

7. rollback_transaction

Rollback the current transaction

// Example transaction usage
"Start a transaction, update the user's email, then commit"

Available Resources

The server exposes database schemas as MCP resources:

• URI Format: mysql://{database}/{table}

• Content: Structured JSON with table schema information

// Example usage
"Read the schema resource for the users table"

Security Considerations

Default Safety Controls

1. Query Validation: All queries are validated before execution

2. Row Limits: SELECT queries are automatically limited to prevent memory exhaustion

3. WHERE Clause Enforcement: DELETE/UPDATE require WHERE clause by default

4. DDL Restrictions: CREATE, DROP, ALTER are blocked by default

5. Single Statement: Multiple statements are rejected by default

Recommended Practices

1. Use Read-Only Users: Create a MySQL user with SELECT-only permissions for read-only use cases

2. Limit Permissions: Grant only necessary permissions to the MySQL user

3. Network Security: Use localhost or secure network connections

4. Environment Variables: Never commit credentials to version control

5. Enable Logging: Use MCP_LOG_LEVEL=info to monitor query execution

Example: Creating a Read-Only User

-- Create a read-only user
CREATE USER 'mcp_readonly'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT ON your_database.* TO 'mcp_readonly'@'localhost';
FLUSH PRIVILEGES;

Example: Creating a Limited Write User

-- Create a user with limited write permissions
CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE ON your_database.* TO 'mcp_user'@'localhost';
FLUSH PRIVILEGES;

Development

Prerequisites

• Node.js >= 18.0.0

• npm or yarn

• MySQL database (for testing and development)

• Docker (optional, for integration tests)

Setup

# Install dependencies
npm install

# Build the project
npm run build

# Run tests
npm test

Testing

# Run all tests
npm test

# Run specific test suites
npm run test:unit          # Unit tests only
npm run test:property      # Property-based tests only
npm run test:integration   # Integration tests only

# Run tests in watch mode
npm run test:watch

# Run tests with coverage
npm run test:coverage

Integration Tests

Integration tests require a MySQL database. You can use the provided Docker setup:

# Start test database
npm run db:start

# Run integration tests
npm run test:integration

# Stop test database
npm run db:stop

# View database logs
npm run db:logs

# Connect to test database
npm run db:connect

See tests/integration/README.md for more details.

Project Structure

mysql-mcp-server/
├── src/                    # TypeScript source files
│   ├── index.ts           # Main entry point
│   ├── config.ts          # Configuration management
│   ├── database.ts        # Database connection handling
│   ├── validator.ts       # Query validation
│   ├── transaction.ts     # Transaction management
│   ├── resources.ts       # MCP resource handlers
│   ├── errors.ts          # Error handling
│   └── logger.ts          # Logging system
├── tests/
│   ├── unit/              # Unit tests
│   ├── property/          # Property-based tests (fast-check)
│   └── integration/       # Integration tests
├── dist/                  # Compiled JavaScript output
├── .kiro/specs/           # Project specifications
│   └── mysql-mcp-server/
│       ├── requirements.md # Formal requirements (EARS format)
│       ├── design.md      # Design document with correctness properties
│       └── tasks.md       # Implementation task list
├── package.json
├── tsconfig.json
└── vitest.config.ts

Technology Stack

• Runtime: Node.js with TypeScript

• MCP SDK: @modelcontextprotocol/sdk

• Database: mysql2

• Validation: Zod

• Testing: Vitest + fast-check (property-based testing)

Troubleshooting

Network Access in Different MCP Clients

Important: Different MCP clients have different network access policies:

• AI IDEs (Cursor, Windsurf, etc.): Usually restrict private network access (192.168.x.x, 10.x.x.x) for security

• Desktop Apps (Claude Desktop): Full network access, no restrictions

• CLI Tools: Full network access, inherit terminal permissions

This affects ALL database MCP servers (MySQL, PostgreSQL, Redis, MongoDB, etc.) when connecting to local network databases.

See MCP_CLIENT_NETWORK_COMPARISON.md for detailed comparison and solutions.

Connection Issues

Problem: Server fails to connect to MySQL

Solutions:

• Verify MySQL is running: mysql -h localhost -u your_user -p

• Check credentials in environment variables

• Verify network connectivity and firewall rules

• Check MySQL user permissions

Problem: EHOSTUNREACH error when connecting to remote MySQL server

This error can occur when running the MCP server in Cursor, specifically when connecting to private network (LAN) MySQL servers:

Affected scenarios:

• ❌ Local network MySQL: 192.168.x.x, 10.x.x.x, 172.16.x.x - 172.31.x.x

• ✅ Cloud/Public MySQL: AWS RDS, Alibaba Cloud RDS, public IPs - should work directly

Root cause: Cursor may run MCP servers in a sandboxed environment that restricts access to private networks for security reasons.

Solutions:

If your MySQL is on a cloud server (public IP/domain):

Simply use the public endpoint directly - no special configuration needed:

{
  "mcpServers": {
    "mysql": {
      "command": "mysql-mcp-server",
      "env": {
        "MYSQL_HOST": "your-rds.amazonaws.com",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

If your MySQL is on a local network (192.168.x.x, etc.):

Option 1: Use SSH Tunnel (Recommended)

SSH tunnel forwards the remote MySQL port to your local machine, allowing Cursor to access it via localhost.

Step-by-step guide:

1. Open a terminal and run this command (keep the terminal open):

ssh -L 3307:192.168.1.200:3306 user@192.168.1.200

Replace:

• 3307 - Local port (can be any unused port)

• 192.168.1.200:3306 - Your MySQL server IP and port

• user@192.168.1.200 - Your SSH username and server IP

2. Enter your SSH password when prompted

3. Keep the terminal window open (minimize it, don't close it)

4. Update your Cursor config to use localhost:

{
  "mcpServers": {
    "mysql": {
      "command": "mysql-mcp-server",
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3307",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Key changes:

• ✅ MYSQL_HOST: Change from 192.168.1.200 to 127.0.0.1

• ✅ MYSQL_PORT: Change from 3306 to 3307 (match the local port in SSH command)

• ⚠️ Keep username, password, and database unchanged

5. Restart Cursor completely and test the connection

See CURSOR_NETWORK_WORKAROUND.md for detailed tutorial with screenshots and troubleshooting.

Option 2: Test if it's a Cursor limitation

Run the test script to verify the connection works outside of Cursor:

export MYSQL_HOST=192.168.1.200
export MYSQL_PORT=3306
export MYSQL_USER=your_username
export MYSQL_PASSWORD=your_password
export MYSQL_DATABASE=your_database

node test-connection.js

If the test succeeds but Cursor fails, it confirms Cursor's sandbox is blocking the connection.

Option 3: Use Docker

Run the MCP server in Docker, which typically has fewer network restrictions.

See CURSOR_NETWORK_WORKAROUND.md for detailed solutions and alternatives.

Query Rejected

Problem: Query is rejected with validation error

Solutions:

• Add WHERE clause to DELETE/UPDATE queries

• Add LIMIT to SELECT queries (or let server add it automatically)

• Check if DDL operations are needed (set ALLOW_DDL=true)

• Verify query syntax

Transaction Errors

Problem: Transaction commit/rollback fails

Solutions:

• Ensure transaction was started with begin_transaction

• Check for connection issues

• Verify no nested transactions (not supported)

• Check MySQL logs for database-level errors

Logging

Enable debug logging to troubleshoot issues:

{
  "env": {
    "MCP_LOG_LEVEL": "debug"
  }
}

Logs are written to stderr and won't interfere with MCP protocol communication.

Contributing

Contributions are welcome! Please:

1. Fork the repository

2. Create a feature branch

3. Write tests for new functionality

4. Ensure all tests pass

5. Submit a pull request

License

MIT

Acknowledgments

• Built with the Model Context Protocol SDK

• Uses mysql2 for MySQL connectivity

• Property-based testing with fast-check

Support

For issues, questions, or contributions, please visit the project repository.