MySQL MCP Server

Production-ready Model Context Protocol (MCP) server for MySQL databases. Enables AI assistants to interact with MySQL through secure, validated, and audited MCP tools.

Features

• 45+ MCP Tools covering database info, reads, writes, bulk operations, table management, analytics, transactions, and utilities
• Enterprise Security - SQL injection prevention, dangerous statement blocking, read-only mode, parameterized queries
• Connection Pooling - SQLAlchemy 2.x with configurable pool size, pre-ping health checks, and automatic recycling
• Structured Logging - Loguru with query logging, audit trail, rotation, and compression
• Full Validation - Pydantic v2 schemas for all tool inputs with strict type checking
• Docker Ready - Multi-stage Dockerfile and docker-compose with MySQL 8.0
• Comprehensive Tests - 100+ unit and integration tests

Quick Start

Prerequisites

• Python 3.12+
• MySQL 8.0+ (or Docker)

Installation

# Clone the repository
git clone https://github.com/your-org/mysql-mcp-server.git
cd mysql-mcp-server

# Create virtual environment
python -m venv .venv
source .venv/bin/activate  # Linux/macOS
# .venv\Scripts\activate   # Windows

# Install dependencies
pip install -r requirements.txt

# Copy and configure environment
cp .env.example .env
# Edit .env with your MySQL credentials

Configuration

Edit .env with your database credentials:

DB_HOST=localhost
DB_PORT=3306
DB_NAME=my_database
DB_USER=my_user
DB_PASSWORD=my_secure_password

Running

# Development (with MCP Inspector)
mcp dev app/server.py

# Production (stdio transport)
python -m app.server

# Production (HTTP transport)
TRANSPORT=http HTTP_PORT=8000 python -m app.server

Docker

With MySQL (Recommended)

docker compose up -d

This starts both MySQL and the MCP server. The server connects to MySQL automatically.

Standalone

docker build -t mysql-mcp-server .
docker run -it --rm \
  -e DB_HOST=your-mysql-host \
  -e DB_PORT=3306 \
  -e DB_NAME=your_database \
  -e DB_USER=your_user \
  -e DB_PASSWORD=your_password \
  mysql-mcp-server

Available MCP Tools

Database Information

Tool	Description
ping_database	Verify database connectivity
get_database_version	Get MySQL server version
current_database	Get current database name
list_databases	List all accessible databases
health_check	Comprehensive health check with pool stats

Schema Introspection

Tool	Description
list_tables	List all tables in a database
describe_table	Detailed table structure (columns, indexes, FKs, metadata)
show_indexes	Show all indexes for a table
show_foreign_keys	Show foreign key constraints
list_columns	List columns with types and properties
get_schema	Get CREATE TABLE DDL statement

Read Operations

Tool	Description
select_records	Query with filters, sorting, and pagination
search_records	LIKE-based text search across columns
count_rows	Count rows with optional WHERE filter
paginate_results	Paginated results with metadata
execute_select_query	Execute raw SELECT queries

Write Operations

Tool	Description
insert_record	Insert a single record with parameterized values
update_record	Update records (WHERE required for safety)
delete_record	Delete records (WHERE required, preview shown)

Bulk Operations

Tool	Description
bulk_insert	Insert multiple records in a single batch operation
upsert	Insert or update using INSERT...ON DUPLICATE KEY UPDATE
create_table_from_select	Create a new table from a SELECT query result

Table Management

Tool	Description
analyze_table	Update table index statistics for query optimization
optimize_table	Rebuild table to reclaim space and defragment
rename_table	Rename a table atomically
add_column	Add a new column to a table
drop_column	Remove a column from a table
modify_column	Change column data type or nullability
show_triggers	List triggers for a table or database
show_views	List all views in a database
show_procedures	List stored procedures and functions

Data Analytics

Tool	Description
column_statistics	Detailed statistics for a specific column (distinct, nulls, min/max, avg, top values)
table_data_summary	Comprehensive overview of all columns in a table
database_size	Calculate database and table sizes with breakdown
data_diff	Compare results of two SELECT queries to find differences

Advanced Operations

Tool	Description
execute_sql	Execute arbitrary SQL with full validation
execute_transaction	Atomic multi-operation transaction
begin_transaction	Start a transaction block
commit_transaction	Commit current transaction
rollback_transaction	Roll back current transaction

Utility Tools

Tool	Description
show_table_status	MySQL table status information
show_variables	Server system variables
show_processlist	Active server connections
show_grants	User permission grants
explain_query	Query execution plan

Export & Diagnostics

Tool	Description
export_results	Export query results as CSV or JSON
query_history	View recent query execution history
connection_stats	Connection pool statistics and utilization
find_related_tables	Find tables related through foreign keys
relationship_map	Complete map of all table relationships in the database

Security

Blocked Operations

The following operations are blocked by default:

• DROP DATABASE, DROP TABLE
• TRUNCATE
• SHUTDOWN
• ALTER USER, CREATE USER, DROP USER, SET PASSWORD
• GRANT, REVOKE
• LOAD DATA, INTO OUTFILE, INTO DUMPFILE

Protection Mechanisms

• SQL Injection Prevention - 15+ pattern detection for injection attempts
• Parameterized Queries - All user values bound through SQLAlchemy
• Read-Only Mode - Disable all write operations with READ_ONLY_MODE=true
• Row Limits - Automatic LIMIT injection (configurable MAX_RETURNED_ROWS)
• Query Timeout - MAX_EXECUTION_TIME session variable enforcement
• Identifier Sanitization - Table/column names validated against injection
• WHERE Enforcement - UPDATE/DELETE require WHERE clauses to prevent mass operations
• Dangerous Statement Blocking - Configurable blocked keyword list
• Audit Trail - Every tool execution logged with user/host context

Security Configuration

READ_ONLY_MODE=false          # Disable all writes
MAX_RETURNED_ROWS=10000       # Max rows per query
QUERY_TIMEOUT=30              # Seconds before query timeout
ENABLE_QUERY_VALIDATION=true  # Enable all security checks
BLOCKED_KEYWORDS=             # Additional blocked keywords (CSV)

Example Prompts

Connect your AI assistant and try:

Show me all tables in the database
What columns does the users table have?
Count all active users
Find users with "john" in their name or email
Show the 5 most recent orders
What indexes exist on the orders table?
Show me the foreign keys on the order_items table
Get the CREATE TABLE statement for users
Run: SELECT u.name, COUNT(o.id) as orders FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name
What's the database health status?
Show MySQL timeout variables
Explain this query: SELECT * FROM users WHERE email LIKE '%@example.com%'
Bulk insert 100 users from a JSON array
Upsert a user record (insert or update on duplicate)
What are the statistics for the 'amount' column in the orders table?
Show me a summary of the users table
How big is my database? Show table sizes
Compare two queries to find data differences
Export the active users as CSV
Show the query history
What's the connection pool utilization?
Find all tables related to the 'orders' table
Show me the complete relationship map of all tables
Analyze the users table for better query performance
Add a new column 'phone' to the users table
Show all triggers on the orders table
Show all views in the database

Development

Project Structure

mysql-mcp-server/
├── app/
│   ├── server.py          # MCP server entrypoint
│   ├── config.py          # Pydantic Settings configuration
│   ├── database.py        # SQLAlchemy connection management
│   ├── security.py        # Query validation & security
│   ├── logger.py          # Loguru structured logging
│   ├── tools/             # MCP tool implementations
│   │   ├── database_info.py      # ping, version, health, list_databases
│   │   ├── list_tables.py        # list_tables
│   │   ├── describe_table.py     # describe, indexes, foreign_keys, columns
│   │   ├── get_schema.py         # CREATE TABLE DDL
│   │   ├── execute_select.py     # SELECT, search, count, paginate
│   │   ├── insert_record.py      # INSERT operations
│   │   ├── update_record.py      # UPDATE operations
│   │   ├── delete_record.py      # DELETE operations
│   │   ├── bulk_operations.py    # bulk_insert, upsert, create_table_from_select
│   │   ├── table_management.py   # analyze, optimize, rename, add/drop/modify column, triggers, views, procedures
│   │   ├── analytics.py          # column_statistics, table_data_summary, database_size, data_diff
│   │   ├── export_tools.py       # export_results, query_history, connection_stats, relationship tools
│   │   ├── execute_sql.py        # Arbitrary SQL execution
│   │   ├── transaction.py        # Transaction management
│   │   └── utility_tools.py      # SHOW STATUS, VARIABLES, etc.
│   ├── models/
│   │   └── schemas.py    # Pydantic v2 input/output models
│   └── utils/
├── tests/
│   ├── conftest.py       # Fixtures and test configuration
│   ├── test_security.py  # Security validation tests
│   ├── test_config.py    # Configuration tests
│   ├── test_database.py  # Database manager tests
│   └── test_tools.py     # MCP tool integration tests
├── docker/
│   └── init.sql          # Sample database initialization
├── .env.example          # Environment variable template
├── Dockerfile            # Multi-stage production build
├── docker-compose.yml    # Full stack with MySQL
├── requirements.txt      # Runtime dependencies
├── requirements-dev.txt  # Development dependencies
├── pyproject.toml        # Project config, tools, linting
└── README.md

Running Tests

# All tests
pytest

# With coverage
pytest --cov=app --cov-report=html

# Unit tests only (no integration)
pytest -m "not integration"

# Specific test file
pytest tests/test_security.py -v

Code Quality

# Linting
ruff check app/ tests/

# Auto-fix
ruff check --fix app/ tests/

# Formatting
black app/ tests/

# Type checking
mypy app/

Architecture

The server follows clean architecture principles:

1. Config Layer (config.py) - Immutable settings from environment
2. Security Layer (security.py) - Validates and sanitizes all inputs
3. Database Layer (database.py) - Connection pooling and query execution
4. Tool Layer (tools/) - MCP tool implementations (stateless functions)
5. Model Layer (models/) - Pydantic schemas for type safety
6. Server Layer (server.py) - MCP SDK integration and tool registration

Data flows: MCP Client → Server → Tool → Security → Database → MySQL

Troubleshooting

Connection Refused

# Verify MySQL is running
mysql -h localhost -u root -p

# Check Docker status
docker compose ps

# View MCP server logs
docker compose logs mcp

Authentication Failed

# Verify credentials in .env
cat .env

# Test connection manually
mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $DB_NAME

Query Timeout

Increase the timeout in .env:

QUERY_TIMEOUT=60

Read-Only Mode

If writes are being rejected, check:

READ_ONLY_MODE=false

License

MIT License - see LICENSE for details.