# MCP Server MySQL

A Model Context Protocol (MCP) server that provides MySQL database operations. This server enables LLMs to interact with MySQL databases through a standardized protocol.

## Features

- **Database Management**: Create, drop, list, and switch databases
- **Table Operations**: Create, alter, drop, describe, and list tables
- **Data Queries**: Execute SELECT queries and retrieve results
- **Data Modification**: Execute INSERT, UPDATE, DELETE statements
- **Index Management**: Create and drop indexes

## Installation

```bash
npm install
npm run build
```

## Configuration

Set environment variables for database connection:

```bash
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=root
export MYSQL_PASSWORD=your-password
export MYSQL_DATABASE=your-database  # optional
```

### Safety and Permission Controls

The server defaults to read-only mode. Enable write or schema operations explicitly:

```bash
export MYSQL_MODE=readonly       # readonly | data-write | schema-write | admin
export MYSQL_ALLOW_DDL=false     # Set to "true" to enable schema/admin tools
export MYSQL_MAX_ROWS=100        # Optional cap for query result rows
```

Legacy data-write controls are still supported and are enforced when `MYSQL_MODE`
allows data writes:

```bash
export MYSQL_ALLOW_INSERT=true   # Set to "false" to disable INSERT
export MYSQL_ALLOW_UPDATE=true   # Set to "false" to disable UPDATE
export MYSQL_ALLOW_DELETE=false  # Set to "false" to disable DELETE
```

Or use the `connect` tool at runtime to specify connection parameters.

High-risk DDL tools require a `confirm` parameter that matches the target name:

- `alter_table`: `confirm` must match the table name
- `drop_table`: `confirm` must match the table name
- `drop_database`: `confirm` must match the database name
- `drop_index`: `confirm` must match the index name

For AI-assisted development, prefer least-privilege MySQL users. Use a read-only
database account for exploration and enable `MYSQL_MODE=data-write`,
`MYSQL_MODE=schema-write`, or `MYSQL_MODE=admin` only for trusted workflows.

## Supported AI Applications

This MCP server can be used with any application that supports the Model Context Protocol (MCP):

- **[Claude Desktop](https://claude.ai/download)** - Anthropic's official desktop application
- **[Claude Code](https://github.com/anthropics/claude-code)** - Anthropic's official CLI tool for developers
- **[Cline](https://github.com/cline/cline)** - AI coding assistant VS Code extension
- **[Zed](https://zed.dev/)** - High-performance code editor with built-in AI
- **Any MCP-compatible application** - MCP is an open protocol developed by Anthropic

## Usage with Claude Desktop

### Using npx (Recommended)

Add to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["-y", "@nilsir/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DATABASE": "your-database",
        "MYSQL_MODE": "readonly",
        "MYSQL_ALLOW_DDL": "false",
        "MYSQL_ALLOW_INSERT": "true",
        "MYSQL_ALLOW_UPDATE": "true",
        "MYSQL_ALLOW_DELETE": "false",
        "MYSQL_MAX_ROWS": "100"
      }
    }
  }
}
```

### Using Local Installation

```json
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-server-mysql/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DATABASE": "your-database",
        "MYSQL_MODE": "readonly",
        "MYSQL_ALLOW_DDL": "false",
        "MYSQL_ALLOW_INSERT": "true",
        "MYSQL_ALLOW_UPDATE": "true",
        "MYSQL_ALLOW_DELETE": "false",
        "MYSQL_MAX_ROWS": "100"
      }
    }
  }
}
```

## Available Tools

| Tool | Description |
|------|-------------|
| `connect` | Connect to a MySQL database |
| `query` | Execute SELECT queries |
| `explain_query` | Run EXPLAIN for a single SELECT query |
| `execute` | Execute INSERT/UPDATE/DELETE queries when policy allows |
| `dry_run_execute` | Execute INSERT/UPDATE/DELETE in a transaction and roll it back |
| `list_databases` | List all databases |
| `list_tables` | List tables in a database |
| `describe_table` | Get table structure |
| `inspect_schema` | Inspect tables, columns, and indexes for AI context |
| `find_tables` | Find tables by table or column name |
| `sample_rows` | Read a capped sample of rows from a table |
| `create_table` | Create a new table |
| `alter_table` | Modify table structure |
| `drop_table` | Drop a table |
| `create_database` | Create a new database |
| `drop_database` | Drop a database |
| `use_database` | Switch to a database |
| `create_index` | Create an index |
| `drop_index` | Drop an index |

## Examples

### Query data
```
Use the query tool with sql: "SELECT * FROM users WHERE active = ?"
and params: [true]
```

### Analyze a SELECT query plan
```
Use the explain_query tool with:
- sql: "SELECT * FROM users WHERE email = ?"
- params: ["user@example.com"]
- format: "traditional"
```

`explain_query` only accepts one SELECT statement. Use `format: "json"` when an
AI-readable JSON plan is more useful.

### Create a table
```
Use the create_table tool with:
- table: "users"
- columns: [
    {"name": "id", "type": "INT", "primaryKey": true, "autoIncrement": true},
    {"name": "email", "type": "VARCHAR(255)", "nullable": false},
    {"name": "created_at", "type": "TIMESTAMP", "default": "CURRENT_TIMESTAMP"}
  ]
```

### Inspect schema for AI context
```
Use the inspect_schema tool with:
- database: "nilsir"
- includeColumns: true
- includeIndexes: true
```

### Find likely tables or columns
```
Use the find_tables tool with:
- database: "nilsir"
- term: "user"
```

### Sample rows safely
```
Use the sample_rows tool with:
- database: "nilsir"
- table: "users"
- limit: 5
```

Sampling is read-only and capped at 50 rows.

### Insert data
```
Use the execute tool with sql: "INSERT INTO users (email) VALUES (?)"
and params: ["user@example.com"]
```

Requires `MYSQL_MODE=data-write` or higher.

### Dry-run a data change
```
Use the dry_run_execute tool with sql: "UPDATE users SET active = ? WHERE id = ?"
and params: [false, 123]
```

The statement runs inside a transaction and is rolled back before returning.

### Drop a table
```
Use the drop_table tool with:
- table: "users"
- confirm: "users"
```

Requires `MYSQL_MODE=schema-write` or `MYSQL_MODE=admin` and
`MYSQL_ALLOW_DDL=true`.

## Development

```bash
npm install
npm run build
npm test
```

Integration coverage for dry-run rollback is opt-in because it needs a real test
database:

```bash
MYSQL_INTEGRATION_TEST=true npm test
```

## License

MIT