mcp-server-mysql-py

Enterprise-grade MySQL MCP Server with security controls, multi-connection support, and AI-powered database operations.

Features

• Security First: Read-only mode by default, DDL/write restrictions, confirmation required for write operations
• Multi-Connection: Manage multiple database environments (dev, test, prod) with easy switching
• SQL Analysis: Static SQL analysis for risk assessment before execution
• Query Controls: Automatic result truncation, query timeout, execution plan analysis
• Schema Exploration: Search tables/columns, view indexes, show CREATE TABLE DDL
• MCP Resources: Access database metadata via resource URIs
• Backward Compatible: Works with existing single-connection configurations

Installation

pip install mcp-server-mysql-py

Quick Start

Basic Configuration (Single Connection)

{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "password",
        "MYSQL_DB": "mydb"
      }
    }
  }
}

Multi-Connection Configuration

Create a config.json file:

{
  "connections": {
    "dev": {
      "host": "dev-db.local",
      "port": 3306,
      "user": "dev",
      "password": "devpass",
      "database": "devdb",
      "description": "Development Environment"
    },
    "prod": {
      "host": "prod-db.local",
      "port": 3306,
      "user": "readonly",
      "password": "prodpass",
      "database": "proddb",
      "description": "Production Environment (Read-Only)"
    }
  },
  "default_connection": "dev"
}

Then configure MCP:

{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_CONFIG_FILE": "/path/to/config.json"
      }
    }
  }
}

Configuration

Environment Variables

Variable	Description	Default
MYSQL_HOST	Database host	127.0.0.1
MYSQL_PORT	Database port	3306
MYSQL_USER	Database user	root
MYSQL_PASS	Database password	(required)
MYSQL_DB	Database name	(optional)
MYSQL_CONFIG_FILE	Path to multi-connection config file	(optional)
MYSQL_DEFAULT_CONNECTION	Default connection name	default
ALLOW_WRITE	Allow INSERT/UPDATE/DELETE	false
ALLOW_DDL	Allow CREATE/ALTER/DROP/TRUNCATE	false
REQUIRE_CONFIRM_FOR_WRITE	Require confirmation for writes	true
MAX_ROWS	Maximum rows to return	1000
QUERY_TIMEOUT	Query timeout in seconds	30
LOG_LEVEL	Logging level (DEBUG, INFO, WARNING, ERROR)	INFO

Security Settings

Default Security Posture:

• ALLOW_WRITE=false - Write operations blocked
• ALLOW_DDL=false - DDL operations blocked
• REQUIRE_CONFIRM_FOR_WRITE=true - Write confirmation required

Enabling Write Operations:

{
  "env": {
    "ALLOW_WRITE": "true",
    "REQUIRE_CONFIRM_FOR_WRITE": "true"
  }
}

When REQUIRE_CONFIRM_FOR_WRITE=true, write operations require confirmed=true parameter:

{
  "sql": "UPDATE users SET status = 'active' WHERE id = 1",
  "confirmed": true
}

Tools

Core Tools

mysql_query

Execute SQL queries with security controls.

Parameters:

• sql (required): SQL statement to execute
• connection (optional): Connection name to use
• confirmed (optional): Set to true to confirm write operations

Example:

{
  "sql": "SELECT * FROM users LIMIT 10",
  "connection": "dev"
}

mysql_tables

List all tables in the current database.

Parameters:

• connection (optional): Connection name to use

mysql_describe

Show columns of a specific table.

Parameters:

• table (required): Table name
• connection (optional): Connection name to use

Schema Exploration Tools

show_create_table

Show complete CREATE TABLE DDL statement.

Parameters:

• table (required): Table name
• connection (optional): Connection name

show_indexes

Show index information for a table.

Parameters:

• table (required): Table name
• connection (optional): Connection name

search_tables

Search for tables by name (fuzzy match).

Parameters:

• keyword (required): Search keyword
• connection (optional): Connection name

search_columns

Search for columns across all tables.

Parameters:

• keyword (required): Column name or keyword
• connection (optional): Connection name

Example:

{
  "keyword": "user_id"
}

Returns:

t_order.user_id
t_user.user_id
t_log.user_id

Analysis Tools

analyze_sql

Statically analyze SQL without execution.

Parameters:

• sql (required): SQL statement to analyze

Returns:

• SQL type (SELECT, INSERT, UPDATE, DELETE, DDL, etc.)
• Involved tables
• Whether DDL/DML
• Dangerous operation detection
• Full table update risk
• Missing WHERE clause detection

explain_sql

Execute EXPLAIN to analyze query execution plan.

Parameters:

• sql (required): SQL query to explain
• connection (optional): Connection name

Returns:

• type: Join type
• key: Index used
• rows: Estimated rows
• extra: Additional info

Connection Management

list_connections

List all configured database connections.

Returns:

• Connection names
• Host/port/database info
• Descriptions
• Current active connection indicator

Resources

Access database metadata via resource URIs:

db://config

Get current configuration (without passwords).

db://databases

List all accessible databases.

db://tables/{database}

List all tables in a specific database.

db://table/{database}/{table}

Show schema for a specific table.

db://indexes/{database}/{table}

Show indexes for a specific table.

Prompts

ask_user_which_connection_prompt

Interactive prompt for selecting database connection on first access.

Parameters:

• force_ask (optional): Force asking even if already selected

Architecture

src/mcp_server_mysql/
├── __init__.py          # Package initialization
├── config.py            # Pydantic configuration models
├── log.py               # Logging module
├── sql_analyzer.py      # SQL static analysis
├── db.py                # Connection management
├── tools.py             # MCP tool implementations
├── resources.py         # MCP resource implementations
├── prompts.py           # MCP prompt implementations
└── server.py            # Main entry point

Module Responsibilities

• config: Pydantic models for configuration, environment variable parsing
• log: Centralized logging with configurable levels
• sql_analyzer: Static SQL analysis for security and risk assessment
• db: Connection pooling, query execution with timeout/limits, permission checks
• tools: MCP tool definitions and handlers
• resources: MCP resource URI handlers
• prompts: MCP prompt definitions and handlers
• server: MCP server initialization and routing

Development

Install Dependencies

pip install -e ".[dev]"

Run Tests

pytest

Run Tests with Coverage

pytest --cov=mcp_server_mysql --cov-report=term-missing

Migration from v0.1.x

The v1.0.0 release is fully backward compatible with v0.1.x configurations. Your existing environment variables will continue to work:

{
  "env": {
    "MYSQL_HOST": "127.0.0.1",
    "MYSQL_PORT": "3306",
    "MYSQL_USER": "root",
    "MYSQL_PASS": "password",
    "MYSQL_DB": "mydb"
  }
}

New in v1.0.0:

• Security controls (read-only by default)
• Multi-connection support via config file
• Additional tools (show_create_table, show_indexes, analyze_sql, etc.)
• Resource URIs for metadata access
• Connection switching

Version Upgrade

From v0.1.0 to v1.0.0

pip install --upgrade mcp-server-mysql-py

Breaking Changes: None. Fully backward compatible.

Recommended: Add security settings to your configuration:

{
  "env": {
    "ALLOW_WRITE": "false",
    "ALLOW_DDL": "false",
    "MAX_ROWS": "1000",
    "QUERY_TIMEOUT": "30"
  }
}

License

MIT

Contributing

Contributions welcome! Please ensure tests pass before submitting PRs.

mcp-server-mysql-py

A Python MCP server for MySQL database queries.

Installation

pip install mcp-server-mysql-py

Configuration

Set environment variables:

• MYSQL_HOST - Database host (default: 127.0.0.1)
• MYSQL_PORT - Database port (default: 3306)
• MYSQL_USER - Database user (default: root)
• MYSQL_PASS - Database password
• MYSQL_DB - Database name

Usage

MCP Client Configuration

{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "password",
        "MYSQL_DB": "mydb"
      }
    }
  }
}

Tools

• mysql_query - Execute SQL queries
• mysql_tables - List all tables
• mysql_describe - Show table columns