Claude Desktop
     │
     └── mysql_mcp  (one process)
              ├── "app"       → SSH tunnel → VPS:3306 (app_db)       writes ON
              ├── "analytics" → SSH tunnel → VPS:3307 (analytics_db) read-only
              └── "legacy"    → SSH tunnel → VPS:3308 (legacy_db)    read-only

# docker-compose.yml
services:
  mysql-app:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${APP_ROOT_PW}
      MYSQL_DATABASE: app_db
    ports:
      - "127.0.0.1:3306:3306"
    volumes:
      - app_data:/var/lib/mysql

  mysql-analytics:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${ANALYTICS_ROOT_PW}
      MYSQL_DATABASE: analytics_db
    ports:
      - "127.0.0.1:3307:3306"
    volumes:
      - analytics_data:/var/lib/mysql

  mysql-legacy:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${LEGACY_ROOT_PW}
    ports:
      - "127.0.0.1:3308:3306"
    volumes:
      - legacy_data:/var/lib/mysql

volumes:
  app_data:
  analytics_data:
  legacy_data:

-- mysql-app (writes allowed)
CREATE USER 'mcp_app'@'%' IDENTIFIED BY 'strong_password_1';
GRANT SELECT, INSERT, UPDATE, DELETE, SHOW DATABASES, SHOW VIEW
  ON app_db.* TO 'mcp_app'@'%';

-- mysql-analytics (read-only)
CREATE USER 'mcp_analytics'@'%' IDENTIFIED BY 'strong_password_2';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON analytics_db.* TO 'mcp_analytics'@'%';

-- mysql-legacy (read-only, two schemas)
CREATE USER 'mcp_legacy'@'%' IDENTIFIED BY 'strong_password_3';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON legacy_db.*  TO 'mcp_legacy'@'%';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON archive_db.* TO 'mcp_legacy'@'%';

FLUSH PRIVILEGES;

# Generate a dedicated key for the MCP server (no passphrase — it runs unattended)
ssh-keygen -t ed25519 -f ~/.ssh/mcp_vps -C "mysql-mcp" -N ""

# Copy the public key to the VPS
ssh-copy-id -i ~/.ssh/mcp_vps.pub ubuntu@your-vps.example.com

# Verify it works without a password prompt
ssh -i ~/.ssh/mcp_vps -o BatchMode=yes ubuntu@your-vps.example.com echo ok

cd mysql-mcp
python -m venv .venv
source .venv/bin/activate       # Windows: .venv\Scripts\activate
pip install -r requirements.txt

cp connections.example.json connections.json
$EDITOR connections.json        # fill in your VPS hostname, passwords, key path

// connections.json
[
  {
    "name":            "app",
    "label":           "App Database",
    "host":            "127.0.0.1",
    "port":            13306,
    "user":            "mcp_app",
    "password":        "strong_password_1",
    "database":        "app_db",
    "allow_writes":    true,
    "allowed_dbs":     ["app_db"],

    // SSH tunnel — managed automatically by the MCP server
    "ssh_host":        "your-vps.example.com",
    "ssh_user":        "ubuntu",
    "ssh_key":         "/home/you/.ssh/mcp_vps",
    "ssh_remote_port": 3306,
    "ssh_local_port":  13306
  },
  {
    "name":            "analytics",
    "label":           "Analytics Database",
    "host":            "127.0.0.1",
    "port":            13307,
    "user":            "mcp_analytics",
    "password":        "strong_password_2",
    "database":        "analytics_db",
    "allowed_dbs":     ["analytics_db"],
    "max_rows":        1000,

    "ssh_host":        "your-vps.example.com",
    "ssh_user":        "ubuntu",
    "ssh_key":         "/home/you/.ssh/mcp_vps",
    "ssh_remote_port": 3307,
    "ssh_local_port":  13307
  },
  {
    "name":            "legacy",
    "label":           "Legacy Database",
    "host":            "127.0.0.1",
    "port":            13308,
    "user":            "mcp_legacy",
    "password":        "strong_password_3",
    "allowed_dbs":     ["legacy_db", "archive_db"],

    "ssh_host":        "your-vps.example.com",
    "ssh_user":        "ubuntu",
    "ssh_key":         "/home/you/.ssh/mcp_vps",
    "ssh_remote_port": 3308,
    "ssh_local_port":  13308
  }
]

{
  "mcpServers": {
    "mysql": {
      "command": "/absolute/path/to/mysql-mcp/.venv/bin/python",
      "args":    ["/absolute/path/to/mysql-mcp/server.py"],
      "env": {
        "MYSQL_CONFIG": "/absolute/path/to/mysql-mcp/connections.json"
      }
    }
  }
}

[INFO] Loading connections from: /home/you/mysql-mcp/connections.json
[INFO] Connection 'app' (App Database): mysql=127.0.0.1:13306 db=app_db writes=True tunnel=your-vps.example.com:13306
[INFO] Starting 3 SSH tunnel(s)…
[INFO] SSH tunnel 'app' ready on 127.0.0.1:13306 (connect #1)
[INFO] SSH tunnel 'analytics' ready on 127.0.0.1:13307 (connect #1)
[INFO] SSH tunnel 'legacy' ready on 127.0.0.1:13308 (connect #1)
[INFO] MySQL pool ready for 'app' (127.0.0.1:13306)
[INFO] MySQL pool ready for 'analytics' (127.0.0.1:13307)
[INFO] MySQL pool ready for 'legacy' (127.0.0.1:13308)
[INFO] All connections ready ✓

[WARNING] SSH tunnel 'app' died (exit 255). Reconnecting in 5s…
[WARNING] SSH stderr for 'app': Connection to your-vps.example.com closed.
[INFO]    SSH tunnel 'app' ready on 127.0.0.1:13306 (connect #2)

{ "error": "SSH tunnel for 'app' is reconnecting — please try again shortly." }

{
  "name":            "warehouse",
  "label":           "Data Warehouse",
  "host":            "127.0.0.1",
  "port":            13309,
  "user":            "mcp_warehouse",
  "password":        "strong_password_4",
  "database":        "warehouse_db",
  "allowed_dbs":     ["warehouse_db"],
  "ssh_host":        "your-vps.example.com",
  "ssh_user":        "ubuntu",
  "ssh_key":         "/home/you/.ssh/mcp_vps",
  "ssh_remote_port": 3309,
  "ssh_local_port":  13309
}