Submit
adamosk

MySQL MCP Server

Community adamosk
Updated

MySQL MCP Server

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

  1. Clone and Install

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

  2. 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

  3. 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

MCP Server · Populars

MCP Server · New