# mysql-mcp-server

A [Model Context Protocol](https://modelcontextprotocol.io) (MCP) server that lets an MCP
client (Claude Desktop, Claude Code, etc.) run SQL against a MySQL database through four
tools: `select`, `insert`, `update`, and `delete`.

The server runs as a `uv`-managed Python package and communicates with the client over
stdio, as a subprocess started by the client.

## Requirements

- Python 3.11+
- [`uv`](https://docs.astral.sh/uv/)
- A reachable MySQL server

## Installation

```bash
uv sync
```

## Configuration

The server requires six values, each settable via environment variable and/or CLI flag
(CLI flags take priority over environment variables):

| Parameter | Env var | CLI flag | Required | Default |
|---|---|---|---|---|
| Mode | `MYSQL_MODE` | `--mysql-mode` | yes | — (`readonly` or `readwrite`) |
| Host | `MYSQL_HOST` | `--mysql-host` | yes | — |
| Port | `MYSQL_PORT` | `--mysql-port` | no | `3306` |
| User | `MYSQL_USER` | `--mysql-user` | yes | — |
| Password | `MYSQL_PASSWORD` | `--mysql-password` | yes | — |
| Database | `MYSQL_DATABASE` | `--mysql-database` | yes | — |

If a required value is missing, or `MYSQL_MODE` is not `readonly`/`readwrite`, the server
prints an error to stderr and exits with status code 1 without starting.

- `readonly` mode: only the `select` tool is allowed. `insert`/`update`/`delete` are
  rejected with a `PERMISSION_DENIED` error.
- `readwrite` mode: all four tools are allowed.

The mode is fixed for the lifetime of the process; it cannot be changed at runtime.

> **Security recommendation**: `readonly` mode is an application-level guard, not a
> substitute for database privileges. Where possible, point `readonly` mode at a MySQL
> account that only has `SELECT` grants.

> **Is a `.env` file required?** No. The server itself never reads `.env` files — it only
> reads CLI flags and real process environment variables (`os.environ`). How you get
> values into that environment depends on how you run it:
>
> - **As an MCP server** (see [Connecting from an MCP client](#connecting-from-an-mcp-client)
>   below): the client (Claude Desktop/Code) spawns the server process and injects the
>   `env` block from its own JSON config directly as environment variables. No `.env`
>   file is involved or needed.
> - **Running the CLI directly for local dev/testing**: `.env` is just a convenience so
>   you don't have to `export` six variables by hand. Copy `.env.example` to `.env`, fill
>   in real values, and load it explicitly — it is not read automatically:
>   ```bash
>   uv run --env-file .env mysql-mcp-server
>   ```
>   `.env` is git-ignored and must never be committed.

## Running

```bash
# Environment variables (or use `uv run --env-file .env mysql-mcp-server`, see above)
export MYSQL_MODE=readonly
export MYSQL_HOST=127.0.0.1
export MYSQL_PORT=3306
export MYSQL_USER=app_user
export MYSQL_PASSWORD=secret
export MYSQL_DATABASE=mydb
uv run mysql-mcp-server

# Or, equivalently, via CLI flags
uv run mysql-mcp-server \
  --mysql-mode readonly \
  --mysql-host 127.0.0.1 \
  --mysql-port 3306 \
  --mysql-user app_user \
  --mysql-password secret \
  --mysql-database mydb
```

## Connecting from an MCP client

### Claude Desktop / Claude Code

Add an entry to your MCP client's server config (e.g. Claude Desktop's
`claude_desktop_config.json`, or `.mcp.json` for Claude Code):

```json
{
  "mcpServers": {
    "mysql": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/mysql-mcp-server",
        "run",
        "mysql-mcp-server"
      ],
      "env": {
        "MYSQL_MODE": "readonly",
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "app_user",
        "MYSQL_PASSWORD": "secret",
        "MYSQL_DATABASE": "mydb"
      }
    }
  }
}
```

Restart the client after editing the config. The `select`, `insert`, `update`, and
`delete` tools (subject to `MYSQL_MODE`) should then be available to the model.

## Tools

All four tools take `{"query": string, "params"?: array}` and always use `%s`
parameter-binding placeholders in `query` — never string-format user input into a query.

| Tool | Allowed in | Query must start with | Success `data` shape |
|---|---|---|---|
| `select` | any mode | `SELECT` / `WITH` | `{rows, row_count, truncated}` (capped at 1000 rows) |
| `insert` | `readwrite` only | `INSERT` | `{affected_rows, last_insert_id}` |
| `update` | `readwrite` only | `UPDATE` | `{affected_rows}` (+ `warning` if no `WHERE`) |
| `delete` | `readwrite` only | `DELETE` | `{affected_rows}` (+ `warning` if no `WHERE`) |

Every tool call returns one of:

```json
{ "success": true, "data": { ... } }
```
```json
{ "success": false, "error": { "code": "...", "message": "..." } }
```

Error codes: `PERMISSION_DENIED`, `INVALID_QUERY_TYPE`, `MULTI_STATEMENT_NOT_ALLOWED`,
`DB_CONNECTION_ERROR`, `DB_EXECUTION_ERROR`, `INTERNAL_ERROR`.

Multi-statement queries (`;`-separated) and any DDL/privilege statement (`DROP`,
`TRUNCATE`, `ALTER`, `GRANT`, `CREATE USER`, ...) are always rejected, since only the
four whitelisted statement types above are ever accepted.

## Development

```bash
uv sync
uv run ruff format .
uv run ruff check .
uv run pytest -v
uv run uv build   # packaging check
```

## Troubleshooting

- **Server exits immediately with status 1**: a required `MYSQL_*` value is missing or
  `MYSQL_MODE` is invalid — check stderr for which one.
- **`DB_CONNECTION_ERROR`**: MySQL is unreachable, or the credentials are wrong. The
  server keeps running and will retry the connection on the next tool call.
- **`PERMISSION_DENIED` on insert/update/delete**: the server is running in `readonly`
  mode; restart it with `MYSQL_MODE=readwrite` if writes are intended.

## Version history

- **0.1.0** — Initial release: `select`/`insert`/`update`/`delete` tools, `readonly`/
  `readwrite` mode policy, stdio MCP transport, automatic reconnect-and-retry on lost
  connections.