# MySQL MCP Server

[English](README.md) | [简体中文](README.zh-CN.md)

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

```bash
# 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`

```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):

```json
{
  "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:

```bash
# In the project directory
npm run build
```

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

```json
{
  "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)

```typescript
// 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

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

### 3. `describe_table`
Get detailed schema information for a specific table

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

### 4. `show_indexes`
Show all indexes for a specific table

```typescript
// 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

```typescript
// 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

```typescript
// 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

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

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

```bash
# Install dependencies
npm install

# Build the project
npm run build

# Run tests
npm test
```

### Testing

```bash
# 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:

```bash
# 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](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](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:

```json
{
  "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):

```bash
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:

```json
{
  "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](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:

```bash
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](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:

```json
{
  "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](https://github.com/modelcontextprotocol/sdk)
- Uses [mysql2](https://github.com/sidorares/node-mysql2) for MySQL connectivity
- Property-based testing with [fast-check](https://github.com/dubzzz/fast-check)

## Support

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