# mysql-mcp

Reusable MySQL MCP server for local stdio clients such as Codex and Claude Code.

## Features

- MySQL access over stdio MCP
- `MYSQL_CONFIG_FILE` support
- direct environment override support
- allowed database enforcement
- `readonly` and `write` modes
- sanitized audit logging

## Tools

- `list_tables`
- `describe_table`
- `query`
- `execute`

## Requirements

- Node.js 20+
- a built server entrypoint at `dist/index.js`
- a MySQL user with access only to the databases you intentionally allow

## Install and build

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

## Required environment

- `MYSQL_HOST`
- `MYSQL_USER`
- `MYSQL_PASSWORD`
- `MYSQL_DATABASE`
- `MYSQL_ALLOWED_DATABASES`

## Optional environment

- `MYSQL_PORT`
  Default: `3306`
- `MYSQL_MODE`
  Default: `readonly`
- `MYSQL_MAX_ROWS`
  Default: `200`
- `MYSQL_QUERY_TIMEOUT_MS`
  Default: `10000`
- `MYSQL_AUDIT_LOG_FILE`

## Configuration loading

The server can load values from `MYSQL_CONFIG_FILE`, then apply direct environment variable overrides on top.

Precedence:

1. direct environment variables
2. values inside `MYSQL_CONFIG_FILE`
3. built-in defaults for optional settings

Example env file:

```env
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=demo_user
MYSQL_PASSWORD=secret
MYSQL_DATABASE=demo_test_a
MYSQL_ALLOWED_DATABASES=demo_test_a,demo_test_b
MYSQL_MODE=readonly
MYSQL_MAX_ROWS=200
MYSQL_QUERY_TIMEOUT_MS=10000
MYSQL_AUDIT_LOG_FILE=/Users/your-user/.codex/logs/mysql-mcp-audit.jsonl
```

Example override:

```bash
MYSQL_MODE=write node dist/index.js
```

## Mode behavior

- `readonly`
  `query`, `list_tables`, and `describe_table` are available for read-only SQL use. Write statements, server-scoped `SHOW` statements, and direct `information_schema` reads are rejected.
- `write`
  `execute` is allowed only for database-scoped DML plus table/view DDL, subject to the configured database allowlist. Procedures, triggers, functions, events, server-scoped admin statements, and `information_schema` references are rejected.

## Safety notes

- Keep `MYSQL_ALLOWED_DATABASES` limited to explicitly approved databases.
- If `MYSQL_MODE=write`, use only test or disposable databases.
- Do not point write mode at production, staging, UAT, or any shared non-test database.
- The guard layer blocks table and view references outside the configured allowlist, rejects direct `information_schema` reads from `query`, rejects any `information_schema` reference from `execute`, and rejects server-scoped `SHOW` and `USER` admin statements.
- The `execute` tool supports `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `RENAME TABLE`, and `CREATE` / `ALTER` / `DROP` for tables and views only.
- Audit logs store sanitized statement summaries by default instead of raw SQL text or literals.

## Codex example

```toml
[mcp_servers.mysql_local_dev]
command = "node"
args = ["/Users/your-user/Documents/development/iclaw-workspace/tools/mcp/mysql-mcp/dist/index.js"]
env = { MYSQL_CONFIG_FILE = "/Users/your-user/.codex/mysql-local-dev.env" }
default_tools_approval_mode = "prompt"
```

## Claude Code example

```bash
claude mcp add --transport stdio mysql_local_dev \
  --env MYSQL_CONFIG_FILE=/Users/your-user/.codex/mysql-local-dev.env \
  -- node /Users/your-user/Documents/development/iclaw-workspace/tools/mcp/mysql-mcp/dist/index.js
```

## Development

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