# MySQL/MariaDB MCP Server

Complete MCP server for MySQL and MariaDB. 50 tools covering the full lifecycle of database administration, development, and operations.

## 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 (50 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 |
| `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 |

## 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}
```