# MySQL MCP

Feature-rich MCP server for **MySQL** and **MariaDB** with **multi-connection** support. Connect many instances at once — query schemas, run scripts, test stored procedures with sandbox rollback.

- **npm:** `@raviraj87/mysql-mcp`
- Runs locally via stdio (any MCP-compatible client)
- Supports MySQL 5.7+, 8.x, MariaDB 10.x–11.x, InnoDB/Aria/MyISAM

---

## Quick start

Connect to the **MySQL server** — no database required in the URI. Use `list_databases`, then pass `database` on each tool call (or set `default_database` in config).

```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["-y", "@raviraj87/mysql-mcp"],
      "env": {
        "MYSQL_URI": "mysql://user:pass@localhost:3306"
      }
    }
  }
}
```

**Typical workflow**

1. `list_databases` — discover databases on the server
2. Pick a database (agent asks, or you pass it explicitly)
3. `list_tables`, `query`, etc. with `"database": "myapp"`

Optional: set `default_database` in YAML so tools skip the `database` param for that connection.

**Multiple connections** — copy `config.example.yaml` → `~/.mysql-mcp.yaml`:

```yaml
default_connection: local
connections:
  local:
    uri_env: MYSQL_URI
    default_database: myapp   # optional — omit to choose per tool call
  staging:
    uri_env: MYSQL_URI_STAGING
  prod:
    uri_env: MYSQL_URI_PROD
    read_only: true
```

Set URIs in MCP env. Every tool accepts optional `connection: "staging"`.

**Extra connections via env:**

```bash
MYSQL_EXTRA_CONNECTIONS=staging:MYSQL_URI_STAGING,prod:MYSQL_URI_PROD
```

### Server vs database

| Level | Config | Example |
|-------|--------|---------|
| **Server** | `MYSQL_URI` host only | `mysql://user:pass@host:3306` |
| **Default DB** | `default_database` in YAML (optional) | `myapp` |
| **Per tool** | `database` param | `{ "database": "nsm", "table": "users" }` |

Tools like `ping`, `list_databases`, and `server_info` work without a database. Schema/query tools require `database` or `default_database`.

---

## Tools (~55)

**Connection:** `list_connections`, `ping`, `server_info`, `connect`

**Metadata:** `list_databases`, `list_tables`, `describe_table`, `show_create_table`, `list_indexes`, `list_foreign_keys`, `table_stats`, `list_views`, `list_triggers`, `list_events`

**Read:** `query`, `query_one`, `explain`, `count`, `sample_rows`, `distinct_values`, `export_query`

**Write:** `execute`, `execute_batch`, `insert_rows`, `delete_rows`, `truncate_table`

**Routines:** `list_procedures`, `list_functions`, `describe_procedure`, `show_create_procedure`, `show_create_function`, `call_procedure`, `call_function`, `test_procedure`, `create_procedure`, `drop_procedure`, `routine_overview`

**Scripts:** `split_script`, `validate_script`, `run_script`, `test_script`, `load_script_file`, `run_script_with_vars`

**Admin:** `execute_ddl`, `create_database`, `drop_database`, `create_index`, `drop_index`, `show_grants`

**Diagnostics:** `processlist`, `status`, `variables`, `innodb_status`, `replication_status`, `kill_query`, `health_check`

**Composite:** `explore_table`, `database_overview`, `analyze_query`, `compare_tables`, `index_health`

---

## Stored procedures & scripts

**Test a procedure (rollback by default):**

```json
{
  "tool": "test_procedure",
  "arguments": {
    "database": "myapp",
    "procedure": "sp_create_order",
    "params": { "p_customer_id": 42, "p_amount": 99.99 },
    "setup_sql": ["INSERT INTO customers (id) VALUES (42)"]
  }
}
```

**Test a SQL script:**

```json
{
  "tool": "test_script",
  "arguments": {
    "database": "myapp",
    "script": "DELIMITER $$\nCREATE PROCEDURE ...\nEND$$\nDELIMITER ;\nCALL my_proc(1);"
  }
}
```

`split_script` handles `DELIMITER` changes. Sandbox rollback requires InnoDB/Aria unless `compatibility.allow_myisam_sandbox: true`.

---

## Safety

- Global or per-connection `read_only`
- `max_rows` auto-LIMIT on SELECT
- `query_timeout_ms` via session `max_execution_time` / `max_statement_time`
- `explain_check` — reject full table scans
- `confirmation_required_tools` — destructive ops need `confirmed: true`
- `test_sandbox_default: true` — scripts/SP tests roll back unless committed
- `allowed_databases` per connection
- Blocks `INTO OUTFILE`, `LOAD DATA`, `SHUTDOWN`

---

## Install from source

```bash
cd mysql-mcp
npm install
npm run build
```

```json
{
  "command": "node",
  "args": ["/path/to/mysql-mcp/dist/index.js"]
}
```

## License

Copyright © 2026 Ravi Raj. Licensed under the [MIT License](LICENSE).