# mcp-mysql – MCP Server for MySQL & MariaDB

[![Node.js](https://img.shields.io/badge/Node.js-18%2B-339933.svg)](https://nodejs.org)
[![MySQL](https://img.shields.io/badge/MySQL-8.0%2B-4479A1.svg)](https://www.mysql.com)
[![MariaDB](https://img.shields.io/badge/MariaDB-10.5%2B-C0765A.svg)](https://mariadb.org)
[![MCP](https://img.shields.io/badge/MCP-1.0-blueviolet.svg)](https://modelcontextprotocol.io)
[![License](https://img.shields.io/badge/license-MIT-green.svg)](LICENSE)

Full-featured MCP server for MySQL and MariaDB. 99 tools covering databases, tables, columns, indexes, foreign keys, data operations, transactions, user management, server monitoring, query analysis, export, triggers, views, stored routines, events, partitions, replication, slow query log, schema diff, backup, CHECK constraints, object comments, session management, InnoDB metrics, full-text search, and database rename.

Supports dual transport: **stdio** (Claude Desktop / Claude Code) and **HTTP Streamable** (Cursor, remote clients).

---

## Quick Start (Docker)

```bash
cp .env.example .env
# Edit .env with your settings
docker compose up -d
```

MCP server: `http://localhost:4000/mcp`
Health: `http://localhost:4000/health`
MariaDB: `localhost:3307`

## Configuration

| Variable | Default | Description |
|---|---|---|
| `MYSQL_HOST` | `localhost` | MySQL host |
| `MYSQL_PORT` | `3306` | MySQL port |
| `MYSQL_USER` | `mcpuser` | MySQL user |
| `MYSQL_PASSWORD` | `mcppassword` | MySQL password |
| `MYSQL_DATABASE` | `mcpdb` | Default database |
| `MYSQL_ROOT_PASSWORD` | `rootpassword` | Root password (Docker only) |
| `TRANSPORT` | `stdio` | `stdio` or `http` |
| `PORT` | `3000` | HTTP server port |
| `MYSQL_EXPOSE_PORT` | `3306` | Host port for MySQL container |
| `MCP_PORT` | `3000` | Host port for MCP container |

## Claude Desktop / Claude Code Integration

### HTTP Transport
```json
{
  "mcpServers": {
    "mysql": {
      "url": "http://localhost:4000/mcp"
    }
  }
}
```

### stdio Transport (local)
```json
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/path/to/mcp-mysql/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "password"
      }
    }
  }
}
```

## Tools (99 total)

### Databases (5)
| Tool | Description |
|---|---|
| `mysql_list_databases` | List all databases |
| `mysql_create_database` | Create a database |
| `mysql_drop_database` | Drop a database |
| `mysql_show_create_database` | Show CREATE DATABASE statement |
| `mysql_server_info` | Server version, uptime, config |

### Tables (8)
| Tool | Description |
|---|---|
| `mysql_list_tables` | List tables with stats |
| `mysql_describe_table` | Column definitions (SHOW FULL COLUMNS) |
| `mysql_show_create_table` | Full CREATE TABLE DDL |
| `mysql_create_table` | Create table from SQL |
| `mysql_drop_table` | Drop one or more tables |
| `mysql_truncate_table` | Remove all rows |
| `mysql_rename_table` | Rename / move between databases |
| `mysql_get_table_stats` | Size, rows, engine, collation |

### Columns (4)
| Tool | Description |
|---|---|
| `mysql_add_column` | ALTER TABLE ADD COLUMN (supports generated columns) |
| `mysql_drop_column` | ALTER TABLE DROP COLUMN |
| `mysql_modify_column` | ALTER TABLE MODIFY COLUMN |
| `mysql_rename_column` | RENAME COLUMN (MySQL 8+/MariaDB 10.5+) |

### Indexes (3)
| Tool | Description |
|---|---|
| `mysql_list_indexes` | All indexes with cardinality |
| `mysql_create_index` | Create BTREE/HASH/FULLTEXT/SPATIAL index |
| `mysql_drop_index` | Drop an index |

### Foreign Keys (3)
| Tool | Description |
|---|---|
| `mysql_list_foreign_keys` | List FK constraints |
| `mysql_add_foreign_key` | Add FK with ON UPDATE/DELETE rules |
| `mysql_drop_foreign_key` | Drop FK constraint |

### Data Operations (7)
| Tool | Description |
|---|---|
| `mysql_query` | SELECT with pagination and parameterized values |
| `mysql_execute` | Execute any SQL (DDL/DML) |
| `mysql_insert` | Insert single row |
| `mysql_update` | Update rows by WHERE condition |
| `mysql_delete` | Delete rows by WHERE condition |
| `mysql_bulk_insert` | Batch insert up to 5000 rows |
| `mysql_execute_transaction` | Atomic multi-statement transaction |

### User Management (7)
| Tool | Description |
|---|---|
| `mysql_list_users` | List all MySQL accounts |
| `mysql_create_user` | Create user with password |
| `mysql_drop_user` | Drop user |
| `mysql_grant_privileges` | Grant privileges |
| `mysql_revoke_privileges` | Revoke privileges |
| `mysql_show_grants` | Show GRANT statements |
| `mysql_change_password` | Change user password |

### Server Monitoring (6)
| Tool | Description |
|---|---|
| `mysql_show_status` | Global/session status variables |
| `mysql_show_variables` | Global/session config variables |
| `mysql_show_processlist` | Active connections and queries |
| `mysql_kill_process` | Kill connection or query |
| `mysql_show_warnings` | Last statement warnings |
| `mysql_flush_privileges` | Reload grant tables |

### Query Analysis & Maintenance (5)
| Tool | Description |
|---|---|
| `mysql_explain_query` | EXPLAIN with traditional/JSON/TREE format |
| `mysql_analyze_table` | Update optimizer statistics |
| `mysql_optimize_table` | Defragment and reclaim space |
| `mysql_check_table` | Check for corruption |
| `mysql_repair_table` | Repair MyISAM/ARCHIVE tables |

### Export & Import (2)
| Tool | Description |
|---|---|
| `mysql_export_table` | Export as JSON, CSV, or SQL INSERTs |
| `mysql_import_sql` | Execute SQL script |

### Triggers (4)

| Tool | Description |
|---|---|
| `mysql_list_triggers` | List triggers for a database or table |
| `mysql_create_trigger` | Create a trigger |
| `mysql_drop_trigger` | Drop a trigger |
| `mysql_show_create_trigger` | Show CREATE TRIGGER statement |

### Views (4)

| Tool | Description |
|---|---|
| `mysql_list_views` | List views in a database |
| `mysql_create_view` | Create or replace a view |
| `mysql_drop_view` | Drop a view |
| `mysql_show_create_view` | Show CREATE VIEW statement |

### Stored Routines (6)

| Tool | Description |
|---|---|
| `mysql_list_routines` | List stored procedures and functions |
| `mysql_create_procedure` | Create a stored procedure |
| `mysql_create_function` | Create a stored function |
| `mysql_drop_routine` | Drop a procedure or function |
| `mysql_show_create_routine` | Show CREATE PROCEDURE/FUNCTION statement |
| `mysql_call_procedure` | Call a stored procedure |

### Events (5)

| Tool | Description |
|---|---|
| `mysql_list_events` | List scheduled events |
| `mysql_create_event` | Create a scheduled event |
| `mysql_drop_event` | Drop a scheduled event |
| `mysql_alter_event` | Enable, disable, or reschedule an event |
| `mysql_show_create_event` | Show CREATE EVENT statement |

### Partitions (5)

| Tool | Description |
|---|---|
| `mysql_list_partitions` | List partitions and row counts |
| `mysql_add_partition` | Add partition to a table |
| `mysql_drop_partition` | Drop a partition |
| `mysql_coalesce_partitions` | Reduce partition count (HASH/KEY) |
| `mysql_analyze_partitions` | Analyze partition statistics |

### Replication & Binary Logs (5)

| Tool | Description |
|---|---|
| `mysql_show_replication_status` | Replica/slave status (auto-detects MySQL vs MariaDB) |
| `mysql_show_binary_logs` | List binary log files |
| `mysql_show_binlog_events` | Show events in a binary log |
| `mysql_show_master_status` | Primary/master binary log position |
| `mysql_show_replica_hosts` | List connected replicas |

### Slow Query Log & Performance (3)

| Tool | Description |
|---|---|
| `mysql_show_slow_queries` | Query slow log from performance_schema |
| `mysql_get_performance_digest` | Top queries by total latency |
| `mysql_toggle_slow_log` | Enable or disable the slow query log |

### Schema Diff (1)

| Tool                | Description                          |
|---------------------|--------------------------------------|
| `mysql_schema_diff` | Compare schema between two databases |

### Backup & Export (2)

| Tool | Description |
|---|---|
| `mysql_backup_database` | Full logical backup as SQL dump |
| `mysql_export_schema` | Export DDL-only schema (no data) |

### CHECK Constraints (3)

| Tool | Description |
|---|---|
| `mysql_list_check_constraints` | List CHECK constraints for a table |
| `mysql_add_check_constraint` | Add a CHECK constraint |
| `mysql_drop_check_constraint` | Drop a CHECK constraint |

### Object Comments (3)

| Tool | Description |
|---|---|
| `mysql_set_table_comment` | Set comment on a table |
| `mysql_set_column_comment` | Set comment on a column |
| `mysql_get_object_comments` | Get all comments for tables/columns |

### Session Management (5)

| Tool | Description |
|---|---|
| `mysql_show_session_variables` | Show current session variables |
| `mysql_set_session_variable` | Set a session variable |
| `mysql_show_active_sessions` | List active sessions/connections |
| `mysql_get_session_info` | Info about current connection |
| `mysql_kill_session` | Kill a session by ID |

### Metrics & Admin (2)

| Tool | Description |
|---|---|
| `mysql_show_innodb_metrics` | InnoDB engine metrics |
| `mysql_rename_database` | Rename a database (moves all tables) |

### Full-Text Search (1)

| Tool                    | Description                              |
|-------------------------|------------------------------------------|
| `mysql_fulltext_search` | Full-text search with MATCH...AGAINST    |

---

## Development

```bash
npm install
npm run dev       # Development with auto-reload
npm run build     # Production build
```

## Switch to MySQL 8.4

Edit `docker-compose.yml`:
```yaml
mysql:
  image: mysql:8.4   # was mariadb:11.4
  environment:
    MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-rootpassword}
    MYSQL_DATABASE: ${MYSQL_DATABASE:-mcpdb}
    MYSQL_USER: ${MYSQL_USER:-mcpuser}
    MYSQL_PASSWORD: ${MYSQL_PASSWORD:-mcppassword}
```

## MySQL vs MariaDB Compatibility

Server auto-detects engine via `@@version_comment`. Fallbacks in place for renamed commands (`SHOW SLAVE STATUS` → `SHOW REPLICA STATUS`, `SHOW MASTER STATUS` → `SHOW BINARY LOG STATUS`). `EXPLAIN FORMAT=TREE` is MySQL-only and gracefully skipped on MariaDB.