Metadata-Version: 2.4
Name: vikashruhil-mysql-mcp
Version: 1.0.1
Summary: Secure read-only MySQL MCP server with query impact analysis
License-Expression: MIT
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: mcp[cli]>=1.2.0
Requires-Dist: aiomysql>=0.2.0

# MySQL Read-Only MCP Server

A secure, feature-rich Model Context Protocol (MCP) server providing safe read-only access to MySQL databases with **query impact analysis** and **multi-database support**.

## Overview

This MCP server enables Claude (via Claude Desktop or Claude Code) to safely query MySQL databases while protecting against performance issues through intelligent query analysis. It supports:

- **Read-only SELECT queries** with automatic validation and sanitization
- **Query impact analysis** using EXPLAIN to detect slow queries before execution
- **Multi-database support** with dynamic database switching via profiles
- **Schema introspection** (list tables, describe structures)
- **Database statistics** (size, row counts, table metrics)
- **Smart caching** for improved performance

### Key Features

✅ **Security First**
- Read-only enforcement (no INSERT, UPDATE, DELETE, ALTER)
- Dangerous keyword blocking
- SQL injection prevention via identifier sanitization
- Parameterized queries

✅ **Performance Protection**
- EXPLAIN-based query analysis before execution
- Detection of full table scans and slow queries
- Optimization suggestions
- HIGH-risk query blocking

✅ **Multi-Database Support**
- Switch between different MySQL databases and instances
- Environment-variable based profile configuration
- Connection validation before switching
- Automatic schema cache clearing

✅ **Developer Friendly**
- Natural language query descriptions
- Detailed analysis reports with metrics
- Table structure introspection
- Database size and statistics

## Quick Start

### Prerequisites

- Docker installed and running
- MySQL server with database access
- Claude Desktop or Claude Code installed

### For Claude Desktop

**Fastest Way:**

1. **Build the Docker image** (one time):
   ```bash
   cd /Users/vikashruhil/Documents/work/Tray/mcps/mysql
   chmod +x build.sh run.sh
   ./build.sh
   ```

2. **Set database secrets:**
   ```bash
   # Interactive setup
   ./setup-secrets.sh

   # OR manually:
   docker mcp secret set DB_HOST localhost
   docker mcp secret set DB_USER readonly_user
   docker mcp secret set DB_PASS your_password
   docker mcp secret set DB_NAME your_database
   docker mcp secret set DB_PORT 3306
   ```

3. **Restart Claude Desktop**

4. **MySQL tools should now be available!**

### For Claude Code

**Single Database Setup:**

1. **Build the Docker image:**
   ```bash
   cd /path/to/mcps/mysql
   ./build.sh
   ```

2. **Create environment file:**
   ```bash
   cp /mcps/mysql/mcp.json.example .mcp.json
   # Edit .mcp.json with your database credentials
   ```

3. **Use in Claude Code:**
   ```bash
   claude code --mcp .mcp.json
   ```

**Multi-Database Setup (with profiles.json):**

1. **Build the Docker image:**
   ```bash
   ./build.sh
   ```

2. **Create configuration files:**
   ```bash
   cp /mcps/mysql/profiles.json.example profiles.json
   cp /mcps/mysql/.env.local .env.local
   ```

3. **Edit profiles.json** with your database credentials

4. **Create .mcp.json** with volume mount for profiles.json (see Configuration Guide)

5. **Use in Claude Code** and switch databases dynamically

## Tools Reference

### list_tables

Lists all tables in the currently connected database.

**Usage:**
```
Show me what tables are in the database
```

**Output Example:**
```
📊 Tables in 'tray' (15 total) [Profile: default]:

  • users
  • products
  • orders
  • ...
```

---

### describe_table

Shows detailed schema information about a specific table including columns, data types, keys, and row count.

**Parameters:**
- `table_name` (required): Name of the table to describe

**Usage:**
```
Describe the users table
What's the structure of the orders table?
```

**Output Example:**
```
📋 Table: users
📊 Total Rows: 1,250

Columns:

  • id
    Type: bigint(20)
    Null: NO
    Key: PRI
    Extra: auto_increment

  • email
    Type: varchar(255)
    Null: NO
    Key: UNI
    Default: NULL
```

---

### query

Executes safe SELECT queries with **automatic impact analysis**. High-risk queries are blocked unless optimized.

**Parameters:**
- `sql` (required): SELECT query to execute
- `limit` (optional): Max rows to return (default: 100, max: 1000)

**Usage:**
```
SELECT * FROM orders WHERE created_at > '2025-01-01'
Find all users with more than 10 orders
```

**Impact Analysis Output:**
```
⚠️ QUERY IMPACT ANALYSIS

Risk Level: 🟡 MEDIUM

⚠️ Issues Found:
  • Full table scan on 'orders' (500,000 rows)
  • Query has no WHERE clause - will scan entire table(s)

📊 Metrics:
  • total_rows_scanned_estimate: 500,000
  • join_count: 0
  • orders_index: NONE

💡 Optimization Suggestions:
  1. Add or optimize WHERE clause to reduce rows scanned
  2. Consider adding indexes on filtered or joined columns

✅ Query Results (25 rows):
...
```

---

### stats

Returns comprehensive database statistics.

**Usage:**
```
Show me database statistics
How big is the database?
```

**Output:**
```
📊 Database Statistics: tray [Profile: default]

🗄️ Total Size: 2,450.50 MB
📁 Total Tables: 15

Table Details:

  • sales
    Rows: 50,000,000
    Size: 1,800.25 MB

  • products
    Rows: 125,000
    Size: 45.50 MB

  • users
    Rows: 8,500
    Size: 2.10 MB

📈 Total Rows Across All Tables: 50,250,000
```

---

### list_available_databases

Lists all configured database profiles and the currently active connection.

**Usage:**
```
Show me available databases
What databases can I switch to?
```

**Output:**
```
📂 Available Database Profiles:

Current: 🟢 PROD
  Host: prod-db.com
  Database: tray
  User: readonly

Other Available Profiles:

1. DEV
   Host: localhost
   Database: tray_dev
   User: root

2. DW
   Host: dw-server.com
   Database: dw_tray
   User: dw_user

To switch profiles, use: switch_database(profile_name='DEV')
```

---

### switch_database

Switches to a different database profile. Validates connection before switching.

**Parameters:**
- `profile_name` (required): Name of the profile to switch to

**Usage:**
```
Switch to DEV database
Switch to the data warehouse
```

**Output:**
```
✅ Successfully switched to profile: DEV

🗄️ Database: tray_dev
🖥️ Host: localhost:3306
👤 User: root

You can now query this database using the standard tools.
```

---

## Configuration Guide

### Environment Variables

| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `DB_HOST` | Yes | - | MySQL server address |
| `DB_USER` | Yes | - | Database username (use read-only user) |
| `DB_PASS` | Yes | - | Database password |
| `DB_NAME` | Yes | - | Target database name |
| `DB_PORT` | No | 3306 | MySQL port |
| `CACHE_SCHEMA` | No | true | Enable schema caching |
| `DB_PROFILES_FILE` | No* | - | Path to profiles.json (Claude Code only, optional) |

**Legend:**
- `*` Only for Claude Code when using profiles.json for multi-database support

### Single Database Setup (Using Helper Script - Easiest)

The easiest way is to use the provided `run.sh` script:

```bash
# 1. Create .env.local from template
cp profiles-example.env .env.local

# 2. Edit with your credentials
nano .env.local

# 3. Run the server
./run.sh
```

### Single Database Setup (Manual Docker Command)

For single database access without the helper script:

```bash
# Create .env.local with your credentials
cat > .env.local << EOF
DB_HOST=localhost
DB_USER=readonly_user
DB_PASS=my_secure_password
DB_NAME=tray
DB_PORT=3306
CACHE_SCHEMA=true
EOF

# Run the server with --env-file
docker run -it --rm --env-file .env.local mysql-mcp-server:latest
```

### Dynamic Database Switching

Instead of pre-defining profiles, you can dynamically switch to any database using the `switch_database()` tool:

#### For Claude Desktop:

```
switch_database(
  host="prod-db.example.com",
  user="prod_readonly",
  pass_="secure_password",
  db="tray",
  port=3306
)
```

#### For Claude Code (Optional: with profiles.json):

If you want to reuse database profiles:

1. **Create profiles.json from template:**
   ```bash
   cp profiles.json.example profiles.json
   nano profiles.json  # Edit with your database credentials
   ```

2. **Update .env.local:**
   ```bash
   echo "DB_PROFILES_FILE=$(pwd)/profiles.json" >> .env.local
   ```

3. **When switching databases:**
   ```
   switch_database(host="prod-db.example.com")
   ```
   The tool will:
   - Check profiles.json for matching host
   - Show you the profile and ask for confirmation
   - Use it or ask for different credentials

#### Benefits:
- ✅ Simple and flexible
- ✅ Works with both Claude Desktop and Claude Code
- ✅ No pre-configuration needed (just provide credentials when switching)
- ✅ Optional profiles.json for Claude Code (convenience feature)
- ✅ Supports unlimited database connections

## Helper Scripts

The MySQL MCP includes convenient shell scripts to simplify building and running the Docker container.

### build.sh - Build Docker Image

Builds the MySQL MCP Docker image with proper validation and user feedback.

**Usage:**
```bash
./build.sh [tag]
```

**Examples:**
```bash
./build.sh              # Build as mysql-mcp-server:latest
./build.sh v2.0.0       # Build as mysql-mcp-server:v2.0.0
./build.sh my-version   # Build as mysql-mcp-server:my-version
```

**What it does:**
- Verifies all required files (Dockerfile, requirements.txt, mysql_server.py)
- Builds the Docker image
- Provides helpful instructions after successful build
- Shows error messages if build fails

### run.sh - Run Docker Container

Runs the MySQL MCP container with automatic environment file validation and helpful error messages.

**Usage:**
```bash
./run.sh [image_tag] [env_file]
```

**Examples:**
```bash
./run.sh                    # Run latest image with .env.local
./run.sh v2.0.0             # Run specific version with .env.local
./run.sh latest .env.prod   # Run latest with .env.prod
```

**What it does:**
- Verifies Docker image exists
- Checks environment file exists and has required variables
- Shows configuration summary before starting
- Lists configured database profiles
- Runs container with `--env-file` for clean environment handling
- Provides helpful error messages if setup is incomplete

**Example Output:**
```
MySQL MCP Server - Docker Container
==========================================
Image: mysql-mcp-server:latest
Environment File: .env.local
Container: mysql-mcp-server-1732000000

✓ Image found
✓ Environment file found
✓ Required environment variables present
✓ Found 3 database profile(s)
  - DB_PROFILES_MYSQL_PROD
  - DB_PROFILES_MYSQL_DEV
  - DB_PROFILES_MYSQL_DW

Starting MySQL MCP Server...
```

## Configuration Comparison

| Method | Ease | Multi-DB | Team Friendly |
|--------|------|----------|---------------|
| **Helper Scripts** | ⭐⭐⭐ | ✅ Yes | ✅ Yes |
| **Manual Docker** | ⭐⭐ | ✅ Yes | ⚠️ Manual |
| **Claude Desktop** | ⭐⭐⭐ | ⚠️ Limited | ✅ Yes |
| **Claude Code** | ⭐⭐ | ✅ Yes | ✅ Yes |

**Recommendation:** Use helper scripts (`./build.sh` and `./run.sh`) for local development with proper environment management.

### Claude Desktop Configuration

Add to `~/.docker/mcp/catalogs/custom.yaml`:

**Single database:**
```yaml
registry:
  mysql:
    title: "MySQL Read-Only Server"
    type: server
    image: mysql-mcp-server:latest
    secrets:
      - name: DB_HOST
        env: DB_HOST
      - name: DB_USER
        env: DB_USER
      - name: DB_PASS
        env: DB_PASS
      - name: DB_NAME
        env: DB_NAME
      - name: DB_PORT
        env: DB_PORT
    env:
      - name: CACHE_SCHEMA
        default: "true"
```

**Multi-database with profiles:**
```yaml
registry:
  mysql:
    title: "MySQL Read-Only Server (Multi-DB)"
    type: server
    image: mysql-mcp-server:latest
    secrets:
      - name: DB_HOST
        env: DB_HOST
      - name: DB_USER
        env: DB_USER
      - name: DB_PASS
        env: DB_PASS
      - name: DB_NAME
        env: DB_NAME
    env:
      - name: DB_PROFILES_MYSQL_PROD
        value: '{"host":"prod-db.com","user":"readonly","pass":"pwd","db":"tray"}'
      - name: DB_PROFILES_MYSQL_DEV
        value: '{"host":"localhost","user":"root","pass":"pwd","db":"tray_dev"}'
      - name: CACHE_SCHEMA
        default: "true"
```

### Claude Code Configuration

Create `.mcp.json` in project root:

```json
{
  "mcpServers": {
    "mysql": {
      "type": "stdio",
      "command": "docker",
      "args": [
        "run",
        "-it",
        "--rm",
        "-e", "DB_HOST=${DB_HOST}",
        "-e", "DB_USER=${DB_USER}",
        "-e", "DB_PASS=${DB_PASS}",
        "-e", "DB_NAME=${DB_NAME}",
        "-e", "DB_PROFILES_MYSQL_PROD=${DB_PROFILES_MYSQL_PROD}",
        "-e", "DB_PROFILES_MYSQL_DEV=${DB_PROFILES_MYSQL_DEV}",
        "mysql-mcp-server:latest"
      ]
    }
  }
}
```

Create `.env.local` (not in git):

```
DB_HOST=localhost
DB_USER=root
DB_PASS=dev_password
DB_NAME=tray_dev
DB_PROFILES_MYSQL_PROD={"host":"prod-db.com","user":"readonly","pass":"pwd","db":"tray"}
DB_PROFILES_MYSQL_DEV={"host":"localhost","user":"root","pass":"pwd","db":"tray_dev"}
```

Add to `.gitignore`:

```
.env.local
```

## Advanced Features

### Query Impact Analysis

Before executing any query, the MySQL MCP automatically analyzes it using EXPLAIN to detect potential performance issues.

**Risk Levels:**

- **🟢 LOW**: Safe to execute, minimal performance impact
- **🟡 MEDIUM**: May cause performance issues, provides suggestions
- **🔴 HIGH**: Blocked - likely to cause significant performance impact

**Detected Issues:**

- Full table scans on large tables (>100K rows)
- Queries without WHERE clauses
- Complex joins (>3 JOIN operations)
- Missing indexes
- Subqueries

**Example - High Risk Query:**

```
User: SELECT * FROM orders

System Response:
⚠️ QUERY IMPACT ANALYSIS

Risk Level: 🔴 HIGH

⚠️ Issues Found:
  • Full table scan on 'orders' (2,500,000 rows) - no index used
  • Query has no WHERE clause - will scan entire table(s)

❌ EXECUTION BLOCKED: High-risk query detected.
Optimize the query and try again, or approve if you understand the risks.

Suggestion: Add WHERE clause with created_at to filter recent orders
```

**Optimized Query:**

```
User: SELECT * FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)

System Response:
⚠️ QUERY IMPACT ANALYSIS

Risk Level: 🟡 MEDIUM

💡 Optimization Suggestions:
  1. Consider adding indexes on filtered or joined columns

✅ Query Results (42 rows):
...
```

### Multi-Database Workflows

**Development to Production Workflow:**

```
# 1. Test changes on DEV database
switch_database(profile_name='DEV')
SELECT * FROM products LIMIT 10

# 2. Verify data structure
describe_table(table_name='products')

# 3. Check production database for comparison
switch_database(profile_name='PROD')
SELECT * FROM products LIMIT 10

# 4. Compare statistics between environments
stats()
```

**Data Warehouse Analysis:**

```
# 1. Switch to production for current data
switch_database(profile_name='PROD')
SELECT COUNT(*) FROM sales WHERE date = CURDATE()

# 2. Switch to DW for historical analysis
switch_database(profile_name='DW')
SELECT SUM(amount) FROM fact_sales GROUP BY day
```

## Security Best Practices

### Create Read-Only MySQL User

```sql
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON database_name.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;
```

### Credential Management

**For Development:**
- Store credentials in `.env.local` (not in git)
- Use environment-specific passwords
- Rotate credentials regularly

**For Production:**
- Use MySQL users with minimal privileges (SELECT only)
- Store passwords in secrets management (Docker secrets, Vault, etc.)
- Use VPN or SSH tunneling for remote connections
- Enable query logging for audit trails
- Monitor for unusual query patterns

### Profile Security

- Keep profile passwords in environment variables or secrets
- Never commit credentials to version control
- Rotate database passwords periodically
- Restrict network access to MySQL servers
- Use different credentials for different environments

### Query Blocking

The server blocks:
- INSERT, UPDATE, DELETE, TRUNCATE (data modification)
- DROP, CREATE, ALTER (schema changes)
- GRANT, REVOKE (privilege changes)
- EXEC, EXECUTE (stored procedures)
- INTO OUTFILE, INTO DUMPFILE (file operations)

## Troubleshooting

### Connection Issues

**"Can't connect to MySQL server"**

1. Verify MySQL is running:
   ```bash
   mysql -h localhost -u root -p
   ```

2. Check credentials:
   ```bash
   echo "DB_HOST: $DB_HOST, DB_USER: $DB_USER"
   ```

3. Verify host is correct:
   - Use `127.0.0.1` if `localhost` fails
   - Check firewall allows connection
   - Verify MySQL bind address

**"Access denied for user"**

1. Verify user exists:
   ```sql
   SELECT user FROM mysql.user WHERE user='readonly_user';
   ```

2. Check user permissions:
   ```sql
   SHOW GRANTS FOR 'readonly_user'@'%';
   ```

3. Reset password if needed:
   ```sql
   ALTER USER 'readonly_user'@'%' IDENTIFIED BY 'new_password';
   ```

### Query Issues

**"Query blocked - dangerous keywords"**

- Only SELECT queries are allowed
- Remove INSERT, UPDATE, DELETE, DROP, etc.
- Use WHERE clause instead of modifying data

**"Results limited to X rows"**

- Default limit is 100, maximum is 1000
- Add LIMIT clause to query
- Use WHERE clause to filter results
- Refine query for better performance

**"Full table scan detected"**

- Add WHERE clause with indexed columns
- Add index on frequently filtered columns:
  ```sql
  CREATE INDEX idx_created_at ON orders(created_at);
  ```
- Check if index is being used:
  ```sql
  EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';
  ```

### Profile Issues

**"Profile not found"**

1. Check available profiles:
   ```bash
   list_available_databases()
   ```

2. Verify environment variable is set:
   ```bash
   echo $DB_PROFILES_MYSQL_PROD
   ```

3. Check JSON format is valid:
   ```bash
   echo '{"host":"prod-db.com","user":"readonly","pass":"pwd","db":"tray"}' | python3 -m json.tool
   ```

**"Connection failed to profile"**

1. Test connection manually:
   ```bash
   mysql -h prod-db.com -u readonly -p -D tray
   ```

2. Check credentials are correct
3. Verify network access to remote server
4. Check for firewall rules blocking connection

## Performance Tips

1. **Use specific WHERE clauses** to filter large tables
2. **Add indexes** on frequently filtered columns
3. **Limit result sets** - use LIMIT or refine WHERE clause
4. **Cache is enabled by default** - schema information is cached in memory
5. **Check EXPLAIN** - high-risk queries show execution details
6. **Monitor tables sizes** - use stats() to understand data volume before querying

## Examples

### Common Queries

**Find users by status:**
```
SELECT * FROM users WHERE status = 'active' LIMIT 50
```

**List products in category:**
```
SELECT * FROM products WHERE category = 'Electronics' LIMIT 100
```

**Recent orders:**
```
SELECT * FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
```

**Sales by day:**
```
SELECT DATE(created_at) as day, COUNT(*) as order_count, SUM(total) as revenue FROM orders GROUP BY DATE(created_at) ORDER BY day DESC LIMIT 30
```

### Schema Exploration

```
# 1. List all tables
list_tables()

# 2. Explore specific table
describe_table(table_name='orders')

# 3. Check database size
stats()

# 4. Sample data from table
query(sql='SELECT * FROM orders LIMIT 5')
```

### Multi-Database Analysis

```
# Compare table row counts across environments
list_available_databases()
switch_database(profile_name='PROD')
stats()

switch_database(profile_name='DEV')
stats()
```

## Limitations

- **Read-only enforcement**: No data modifications allowed
- **Query blocking**: Dangerous keywords are blocked
- **Row limits**: Maximum 1000 rows per query (default 100)
- **Query length**: Maximum 10,000 characters
- **No JOINs with external data**: Can only query MySQL database
- **Schema cache**: In-memory only, cleared on restart or database switch

## Environment Variables Reference

```bash
# Required for default connection
DB_HOST=localhost                    # MySQL server hostname/IP
DB_USER=readonly_user               # Database username
DB_PASS=secure_password             # Database password
DB_NAME=tray                        # Database name

# Optional
DB_PORT=3306                        # MySQL port (default: 3306)
CACHE_SCHEMA=true                   # Enable caching (default: true)

# Database profiles (JSON format)
DB_PROFILES_MYSQL_PROD='{"host":"prod-db.com","user":"readonly","pass":"pwd","db":"tray"}'
DB_PROFILES_MYSQL_DEV='{"host":"localhost","user":"root","pass":"pwd","db":"tray_dev"}'
DB_PROFILES_MYSQL_DW='{"host":"dw-server.com","user":"dw_user","pass":"pwd","db":"dw_tray"}'
```

## Architecture

```
Claude Desktop / Claude Code
           ↓
   MCP Server (stdio)
           ↓
   MySQL MCP Server (Docker)
    ├── Profile Manager
    ├── Query Validator
    ├── Query Analyzer (EXPLAIN)
    ├── Connection Pool
    └── Results Formatter
           ↓
    MySQL Database
```

## Development

### File Structure

```
.
├── mysql_server.py      # Main MCP server implementation
├── requirements.txt     # Python dependencies
├── Dockerfile           # Container configuration
├── README.md           # This file
└── CLAUDE.md           # Developer documentation
```

### Dependencies

- `mcp[cli]>=1.2.0` - Model Context Protocol framework
- `aiomysql>=0.2.0` - Async MySQL client
- `httpx` - HTTP client (for MCP protocol)

### Building Custom Versions

```bash
# Build Docker image
docker build -t mysql-mcp-server:custom .

# Test locally
docker run -it --rm \
  -e DB_HOST=localhost \
  -e DB_USER=root \
  -e DB_PASS=password \
  -e DB_NAME=test \
  mysql-mcp-server:custom
```

## License

MIT

## Support

For issues or feature requests, refer to the project documentation or create an issue in the repository.

## Changelog

### v2.0.0 (Current)
- ✨ Query impact analysis with EXPLAIN-based detection
- ✨ Multi-database support with dynamic switching
- ✨ Database profile management via environment variables
- 🔧 Enhanced query blocking for HIGH-risk operations
- 📚 Comprehensive documentation and examples
- 🐛 Bug fixes and performance improvements

### v1.0.0
- Initial release with basic query execution
- Schema introspection
- Database statistics
- Security controls

---

**Last Updated:** November 2025
**Version:** 2.0.0
**Status:** Production Ready
