mysql-mcp

MySQL MCP Server

A secure and professional MySQL Model Context Protocol (MCP) server with clean architecture, designed for seamless integration with GitHub Copilot, Cline, and other AI coding assistants in VS Code.

Features

• Clean Architecture: Follows Single Responsibility Principle for maintainability and scalability
• Security First: Built-in permission system, audit logging, and query validation
• Connection Pooling: Efficient MySQL connection management with connection pooling
• Schema Inspection: Tools for exploring database structure
• Query Execution: Secure SQL query execution with timeout controls
• Audit Logging: Comprehensive logging of all database operations

Quick Start

Installation

npm install

Configuration

Copy the example configuration and update credentials:

cp .env.example .env

Then edit .env in the project root:

# MySQL Connection
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database

# Security Settings
SECURITY_ENABLED=true
ALLOWED_OPERATIONS=SELECT,SHOW,DESCRIBE,EXPLAIN
MAX_QUERY_EXECUTION_TIME=30000

# Audit Settings
AUDIT_ENABLED=true
AUDIT_LOG_LEVEL=info

Build

npm run build

Run

npm start

Integration with VS Code

GitHub Copilot

Recommended for this repository: use the workspace MCP file at .vscode/mcp.json.

It is already included in this project and should look like:

{
  "servers": {
    "mysql-local": {
      "type": "stdio",
      "command": "node",
      "args": ["${workspaceFolder}/dist/index.js"],
      "env": {
        "MYSQL_HOST": "${env:MYSQL_HOST}",
        "MYSQL_PORT": "${env:MYSQL_PORT}",
        "MYSQL_USER": "${env:MYSQL_USER}",
        "MYSQL_PASSWORD": "${env:MYSQL_PASSWORD}",
        "MYSQL_DATABASE": "${env:MYSQL_DATABASE}"
      }
    }
  }
}

Alternative: add to VS Code user settings (settings.json):

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/path/to/mysql-mcp/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Cline

Configure in Cline settings:

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/path/to/mysql-mcp/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Available Tools

Query Tools

execute_query

Execute SQL queries against the database.

Parameters:

• sql (required): SQL query to execute
• database (optional): Database name
• userId (optional): User ID for permission checking
• timeout (optional): Query timeout in milliseconds

Schema Tools

list_databases

List all available databases.

Parameters:

• userId (optional): User ID for permission checking

list_tables

List all tables in a database.

Parameters:

• database (optional): Database name
• userId (optional): User ID for permission checking

describe_table

Get the structure of a table.

Parameters:

• table (required): Table name
• database (optional): Database name
• userId (optional): User ID for permission checking

Admin Tools

get_server_status

Get MySQL server status variables.

Parameters:

• userId (optional): User ID for permission checking

get_process_list

Get list of running MySQL processes.

Parameters:

• userId (optional): User ID for permission checking

kill_query

Kill a running MySQL process.

Parameters:

• processId (required): Process ID to kill
• userId (optional): User ID for permission checking

get_table_indexes

Get index information for a table.

Parameters:

• table (required): Table name
• database (optional): Database name
• userId (optional): User ID for permission checking

get_table_constraints

Get constraint information for a table.

Parameters:

• table (required): Table name
• database (optional): Database name
• userId (optional): User ID for permission checking

analyze_table

Analyze table statistics.

Parameters:

• table (required): Table name
• database (optional): Database name
• userId (optional): User ID for permission checking

optimize_table

Optimize a table.

Parameters:

• table (required): Table name
• database (optional): Database name
• userId (optional): User ID for permission checking

RBAC Tools

All RBAC tools require authentication and API key permission rbac_admin.

Role and Permission Management

• create_role(name, desc)
• delete_role(id)
• list_roles()
• create_permission(name, desc)
• list_permissions()
• assign_permission_to_role(roleId, permId)
• revoke_permission_from_role(roleId, permId)

User Role Assignment

• assign_role_to_user(userId, roleId)
• revoke_role_from_user(userId, roleId)
• list_user_roles(userId)

Authorization Checks

• check_user_permission(userId, permissionName)
• current_user_permissions()
• can_access(userId, resource, action, attributes?)

RBAC Audit

• audit_log(event, actor, target, details)
• query_audit_logs(filter)

RBAC Database Operations

• db_migrate_rbac()
• db_seed_rbac_defaults()
• db_check_integrity()

Available Resources

mysql://status

Current database connection status and pool information.

mysql://config

Server configuration (sanitized, no sensitive data).

mysql://audit/statistics

Statistics about database operations.

mysql://audit/logs

Recent database operation logs.

Architecture

src/
├── config/           # Configuration management
├── mysql/            # MySQL connection management
├── security/         # Permission and audit logging
├── tools/            # MCP tools implementation
├── resources/        # MCP resources
├── types/            # TypeScript type definitions
└── index.ts          # Main entry point

Key Components

1. ConfigManager: Handles configuration loading and validation
2. MySQLConnectionManager: Manages connection pooling and query execution
3. PermissionManager: Handles permission checking and access control
4. AuditLogger: Logs all database operations for security
5. QueryTool: Executes SQL queries with security validation
6. SchemaTool: Inspects database schema
7. DatabaseResource: Provides MCP resources

Security

• Permission-based access control
• Query timeout enforcement
• Operation whitelisting/blacklisting
• Audit logging of all operations
• Rate limiting support

See docs/security.md for detailed security documentation.

Documentation

• Getting Started
• Configuration
• Security
• Tools Reference
• Resources Reference
• Integration Guide

Development

# Install dependencies
npm install

# Run in development mode
npm run dev

# Build
npm run build

# Lint
npm run lint

# Format
npm run format

Requirements

• Node.js >= 18.0.0
• MySQL 5.7+ or MariaDB 10.3+

License

MIT License - see LICENSE file for details.

Contributing

Contributions are welcome! Please read our contributing guidelines before submitting pull requests.