vikashruhil-mysql-mcp 1.0.1

Secure read-only MySQL MCP server with query impact analysis

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):

   cd /Users/vikashruhil/Documents/work/Tray/mcps/mysql
   chmod +x build.sh run.sh
   ./build.sh
   

2. Set database secrets:

   # 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:

   cd /path/to/mcps/mysql
   ./build.sh
   

2. Create environment file:

   cp /mcps/mysql/mcp.json.example .mcp.json
   # Edit .mcp.json with your database credentials
   

3. Use in Claude Code:

   claude code --mcp .mcp.json
   

Multi-Database Setup (with profiles.json):

1. Build the Docker image:

   ./build.sh
   

2. Create configuration files:

   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:

# 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:

# 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:

   cp profiles.json.example profiles.json
   nano profiles.json  # Edit with your database credentials
   

2. Update .env.local:

   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:

./build.sh [tag]

Examples:

./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:

./run.sh [image_tag] [env_file]

Examples:

./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:

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:

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:

{
  "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

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:

   mysql -h localhost -u root -p
   

2. Check credentials:

   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:

   SELECT user FROM mysql.user WHERE user='readonly_user';
   

2. Check user permissions:

   SHOW GRANTS FOR 'readonly_user'@'%';
   

3. Reset password if needed:

   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:

  CREATE INDEX idx_created_at ON orders(created_at);
  

• Check if index is being used:

  EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';
  

Profile Issues

"Profile not found"

1. Check available profiles:

   list_available_databases()
   

2. Verify environment variable is set:

   echo $DB_PROFILES_MYSQL_PROD
   

3. Check JSON format is valid:

   echo '{"host":"prod-db.com","user":"readonly","pass":"pwd","db":"tray"}' | python3 -m json.tool
   

"Connection failed to profile"

1. Test connection manually:

   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

# 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

# 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