npm package: @jonathanzak/mysql-mcp-server
Description: Multi-Database MySQL MCP Server supporting connections to multiple MySQL databases simultaneously
Author: Jonathan Zak
Latest version: 1.0.0

# Multi-Database MySQL MCP Server

This MCP server supports connecting to multiple MySQL databases simultaneously, allowing you to run queries against different databases from a single MCP connection.

## Features

- **Multiple Database Support**: Connect to multiple MySQL databases using environment variable prefixes
- **Database Selection**: Choose which database to query via the `database` parameter
- **Legacy Compatibility**: Maintains backward compatibility with single-database configurations
- **Connection Health**: Tests all database connections on startup
- **Graceful Shutdown**: Properly closes all database connections on exit

## Environment Variables

### Configuration Control Variables

Before setting up individual databases, configure which database prefixes to look for:

```bash
# Specify which database prefixes to scan for (comma-separated)
MCP_DB_PREFIXES=HQ,BOOKIE,DB1,ANALYTICS

# Enable/disable legacy (no prefix) database support (default: true)
MCP_ENABLE_LEGACY=false
```

### Multi-Database Configuration

For each database you want to connect to, set environment variables with a unique prefix:

```bash
# Database 1 (HQ Backend)
HQ_MYSQL_HOST=localhost
HQ_MYSQL_PORT=3306
HQ_MYSQL_USER=hq_user
HQ_MYSQL_PASS=hq_password
HQ_MYSQL_DB=hq_backend
HQ_MYSQL_SSL=false

# Database 2 (Bookie)
BOOKIE_MYSQL_HOST=localhost
BOOKIE_MYSQL_PORT=3306
BOOKIE_MYSQL_USER=bookie_user
BOOKIE_MYSQL_PASS=bookie_password
BOOKIE_MYSQL_DB=hq_bookie
BOOKIE_MYSQL_SSL=false

# Database 3 (Analytics)
DB1_MYSQL_HOST=analytics.example.com
DB1_MYSQL_PORT=3306
DB1_MYSQL_USER=analytics_user
DB1_MYSQL_PASS=analytics_password
DB1_MYSQL_DB=analytics_db
DB1_MYSQL_SSL=true
DB1_MYSQL_SSL_REJECT_UNAUTHORIZED=true
```

### Configurable Prefixes

The server looks for databases based on the `MCP_DB_PREFIXES` environment variable. If not set, it defaults to:
- `HQ`, `BOOKIE`, `DB1`, `DB2`, `DB3`, `MAIN`, `SECONDARY`

You can customize this by setting `MCP_DB_PREFIXES` to any comma-separated list of prefixes:
```bash
# Custom database prefixes
MCP_DB_PREFIXES=PROD,STAGING,TEST,ANALYTICS
```

Legacy support (no prefix) can be controlled via `MCP_ENABLE_LEGACY` (default: true).

### Required Variables (per database)

- `[PREFIX_]MYSQL_HOST`: Database hostname
- `[PREFIX_]MYSQL_USER`: Database username  
- `[PREFIX_]MYSQL_PASS`: Database password
- `[PREFIX_]MYSQL_DB`: Database name

### Optional Variables (per database)

- `[PREFIX_]MYSQL_PORT`: Database port (default: 3306)
- `[PREFIX_]MYSQL_SSL`: Enable SSL (default: false)
- `[PREFIX_]MYSQL_SSL_REJECT_UNAUTHORIZED`: Reject unauthorized SSL certificates (default: false)

## MCP Tools

### 1. `list-databases`

Lists all configured and connected databases.

**Parameters**: None

**Example Response**:
```json
[
  {"name": "hq", "status": "connected"},
  {"name": "bookie", "status": "connected"},
  {"name": "db1", "status": "connected"}
]
```

### 2. `run-query`

Execute a SQL query against a specified database.

**Parameters**:
- `query` (required): SQL query to execute
- `database` (optional): Database name to query. If not specified, uses the first available database.

**Examples**:

```javascript
// Query specific database
{
  "query": "SELECT COUNT(*) FROM users",
  "database": "hq"
}

// Query default database (first available)
{
  "query": "SELECT * FROM quotes LIMIT 5"
}
```

**Example Response**:
```
Query executed on database: hq

[
  {"count": 1234}
]
```

## Usage Examples

### Basic Setup

1. **Configure database prefixes** (optional):
   ```bash
   export MCP_DB_PREFIXES=HQ,BOOKIE,PROD
   export MCP_ENABLE_LEGACY=false
   ```

2. **Set environment variables for your databases**:
   ```bash
   export HQ_MYSQL_HOST=localhost
   export HQ_MYSQL_USER=user
   export HQ_MYSQL_PASS=password
   export HQ_MYSQL_DB=hq_database
   ```

3. **Build and run the MCP server**:
   ```bash
   npm run build
   node dist/mcp/mysql-mcp.js
   ```

### Querying Multiple Databases

```javascript
// List available databases first
await mcp.call("list-databases", {});

// Query HQ database
await mcp.call("run-query", {
  "query": "SELECT COUNT(*) FROM reservations WHERE status = 'confirmed'",
  "database": "hq"
});

// Query Bookie database  
await mcp.call("run-query", {
  "query": "SELECT * FROM quotes WHERE created_at > '2024-01-01'",
  "database": "bookie"
});

// Query without specifying database (uses first available)
await mcp.call("run-query", {
  "query": "SHOW TABLES"
});
```

## Error Handling

- **Database Not Found**: Returns error with list of available databases
- **Connection Failed**: Logs error but continues with other databases
- **SQL Error**: Returns the MySQL error message
- **No Databases**: Exits with error if no valid database configurations found

## Migration from Single Database

The server maintains full backward compatibility. If you have existing environment variables without prefixes:

```bash
MYSQL_HOST=localhost
MYSQL_USER=user
MYSQL_PASS=password
MYSQL_DB=database
```

These will be automatically detected and available as the "default" database.

## Troubleshooting

### No Database Connections

If you see "No database configurations found!", check that you have set the required environment variables with proper prefixes.

### Connection Failures

Check the console output for specific connection error messages. Common issues:
- Incorrect host/port
- Invalid credentials
- Network connectivity
- SSL configuration mismatches

### Database Not Available in Query

Use the `list-databases` tool to see which databases are actually connected and available.