# MySQL MCP Server

Production-ready [Model Context Protocol (MCP)](https://modelcontextprotocol.io/) 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

```bash
# 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:

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

### Running

```bash
# 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)

```bash
docker compose up -d
```

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

### Standalone

```bash
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

```env
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

```bash
# 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

```bash
# 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

```bash
# 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

```bash
# 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`:
```env
QUERY_TIMEOUT=60
```

### Read-Only Mode

If writes are being rejected, check:
```env
READ_ONLY_MODE=false
```

## License

MIT License - see [LICENSE](LICENSE) for details.