MySQL MCP Server

A sophisticated Model Context Protocol (MCP) server that provides secure, multi-database MySQL access with configurable security levels, lazy loading, and hot reload capabilities.

🚀 Features

🗄️ Multi-Database Support: Configure and access multiple MySQL databases simultaneously
🔒 Configurable Security: Four security levels from read-only to full access
⚡ Lazy Loading: Connection pools created only when needed for optimal resource usage
🔄 Hot Reload: Database configuration updates without server restart
📊 Complete Schema Information: Full table definitions with indexes, foreign keys, and constraints
🛡️ Explicit Database Selection: Required database parameter prevents accidental operations
🎯 MCP Protocol Integration: Native VS Code integration with resource browsing

🔧 Installation

Clone and Install

git clone <your-repository-url>
cd mysql-mcp-server
npm install

Configure Multi-Database Setup

Copy the example configuration file and customize it:

cp .env.example .env

Edit .env with your database configurations:

# Multi-Database Configuration (JSON Array)
MYSQL_DATABASES='[
  {
    "name": "primary_db",
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "your_database"
  },
  {
    "name": "analytics_db", 
    "host": "analytics.example.com",
    "user": "analytics_user",
    "password": "analytics_password",
    "database": "analytics"
  }
]'

# Security Configuration
# Options: (default), extended, all, or custom comma-separated list
# MYSQL_ALLOWED_COMMANDS=extended

# Connection Pool Settings
MYSQL_CONNECTION_LIMIT=4
MYSQL_WAIT_FOR_CONNECTIONS=true

# Server Metadata
MCP_SERVER_NAME=mysql-mcp-server
MCP_SERVER_VERSION=1.0.0

Add to VS Code MCP Configuration

Add to your VS Code settings.json under MCP servers:

{
  "mcp": {
    "mcpServers": {
      "mysql-mcp-server": {
        "command": "node",
        "args": ["C:/path/to/mysql-mcp-server/mcp-mysql-lite.js"],
        "env": {}
      }
    }
  }
}

🔒 Security Levels

Configure MYSQL_ALLOWED_COMMANDS in your .env file:

Default (Recommended)

# MYSQL_ALLOWED_COMMANDS not set or commented out

Allowed Commands: SELECT, SHOW, DESCRIBE, DESC, EXPLAIN, ANALYZE
Use Case: Safe read-only access for data analysis and exploration

Extended

MYSQL_ALLOWED_COMMANDS=extended

Allowed Commands: All default commands plus CREATE TABLE, ALTER TABLE, DROP TABLE, INSERT, UPDATE, DELETE, TRUNCATE, etc.
Use Case: Development environments where schema and data modifications are needed

All (⚠️ Use with Extreme Caution)

MYSQL_ALLOWED_COMMANDS=all

Allowed Commands: No restrictions - all SQL commands permitted
Use Case: Trusted environments requiring full database administration capabilities

Custom

MYSQL_ALLOWED_COMMANDS="SELECT,INSERT,UPDATE,CREATE TABLE"

Allowed Commands: User-defined comma-separated list
Use Case: Specific operational requirements with tailored permissions

🛠️ Available Tools

All tools require explicit database selection for security.

`query_database`

Execute SQL queries with security validation and explicit database selection.

Parameters:
- sql (string, required) - The SQL query to execute
- database (string, required) - Database name to target (security requirement)
Security: Commands validated against current security level
Returns: Query results with execution metadata

`describe_table`

Get complete table structure using SHOW CREATE TABLE for comprehensive schema information.

Parameters:
- table_name (string, required) - Name of the table to describe
- database (string, required) - Database containing the table (security requirement)
Returns: Full table definition including columns, indexes, foreign keys, and engine details

`list_databases`

Display all configured databases and their connection status.

Parameters: None required
Returns: Database configurations with connection pool status and default database indicator

📚 Available Resources

Tables from the default database are exposed as MCP resources with URIs like:

mysql://database_name/table/table_name

Browse table schemas directly through VS Code's resource explorer.

💡 Usage Examples

Basic Database Query

-- Query with explicit database selection (security requirement)
SELECT COUNT(*) FROM users WHERE active = 1

Database: primary_db

Multi-Database Operations

-- Analytics query on different database
SELECT DATE(created_at), COUNT(*) 
FROM events 
WHERE event_type = 'purchase' 
GROUP BY DATE(created_at) 
ORDER BY DATE(created_at) DESC 
LIMIT 7

Database: analytics_db

Table Schema Exploration

Use describe_table tool:

Table: users
Database: primary_db

Returns complete table definition with foreign keys, indexes, and constraints.

Database Discovery

Use list_databases tool to see all configured databases and their connection status:

[
  {
    "name": "primary_db",
    "host": "localhost", 
    "database": "app_production",
    "user": "app_user",
    "isDefault": true,
    "poolCreated": true
  },
  {
    "name": "analytics_db",
    "host": "analytics.company.com",
    "database": "analytics",
    "user": "analytics_user", 
    "isDefault": false,
    "poolCreated": false
  }
]

🔄 Hot Reload Feature

Update database configurations in .env file - they'll be automatically reloaded:

✅ Credential updates: New passwords/users applied immediately
✅ New databases: Added to available list (restart VS Code to update tool schemas)
✅ Configuration changes: Host/port updates applied on next connection
🔄 Existing connections: Remain active until naturally recycled

⚡ Lazy Loading

Connection pools are created only when needed:

📊 Resource efficient: No unnecessary database connections
🚀 Fast startup: Server starts immediately regardless of database availability
🔍 Status visibility: list_databases shows which pools are active
🛡️ Failure isolation: One database issue doesn't affect others

🏗️ Architecture

VS Code MCP Client
       ↓ (stdio)
MySQL MCP Server
       ↓ (lazy loading)
Connection Pools
       ↓ (MySQL protocol)  
Multiple Databases

Key design principles:

Multi-Database: Each database has independent configuration and connection pool
Security: Command validation before execution, explicit database selection required
Performance: Lazy loading + connection pooling for optimal resource usage
Reliability: Hot reload + error isolation for production stability

🚀 Development

Running the Server

npm start

Testing Configuration

# Test connection to all configured databases
node mcp-mysql-lite.js

Environment Variables

MYSQL_DATABASES: JSON array of database configurations
MYSQL_ALLOWED_COMMANDS: Security level (default/extended/all/custom)
MYSQL_CONNECTION_LIMIT: Max connections per pool (default: 4)
MYSQL_WAIT_FOR_CONNECTIONS: Wait for available connections (default: true)

📄 License

MIT License - see LICENSE file for details.

Built with ❤️ for the Model Context Protocol ecosystem