MCP MySQL Ops

MCP Server for MySQL Operations and Monitoring

[](https://opensource.org/licenses/MIT)

[](https://www.buymeacoffee.com/call518)

[](https://github.com/call518/MCP-MySQL-Ops/actions/workflows/pypi-publish.yml)

---

Architecture & Internal (DeepWiki)

[](https://deepwiki.com/call518/MCP-MySQL-Ops)

---

Overview

You are working with the MCP MySQL Operations Server, a powerful tool that provides comprehensive MySQL database monitoring and analysis capabilities through natural language queries. This server offers 19 specialized tools for database administration, performance monitoring, and system analysis. Leverages MySQL's Performance Schema and Information Schema for deep insights into database operations and performance metrics.

---

Features

• ✅ Zero Configuration: Works with MySQL 5.7.9+ and 8.0+ out-of-the-box with automatic version detection.
• ✅ Natural Language: Ask questions like "Show me slow queries" or "Analyze table sizes."
• ✅ Production Safe: Read-only operations, AWS RDS/Aurora MySQL compatible with regular user permissions.
• ✅ Performance Schema Integration: Advanced query analytics using MySQL's built-in Performance Schema.
• ✅ Comprehensive Database Monitoring: Storage engine analysis, connection monitoring, and performance insights.
• ✅ Smart Query Analysis: Query performance identification using Performance Schema statistics.
• ✅ Schema & Structure Discovery: Database structure exploration with detailed table and index analysis.
• ✅ Storage Engine Intelligence: InnoDB monitoring, table optimization recommendations.
• ✅ Multi-Database Operations: Seamless cross-database analysis and monitoring.
• ✅ Enterprise-Ready: Safe read-only operations with AWS RDS/Aurora MySQL compatibility.
• ✅ Developer-Friendly: Simple codebase for easy customization and tool extension.

🔧 Advanced Capabilities

• Performance Schema-based query monitoring and analysis.
• Real-time connection and process monitoring.
• Storage engine status and optimization analysis.
• Database capacity and table size analysis.
• Index usage and efficiency tracking.

Tool Usage Examples

---

---

---

⭐ Quickstart (5 minutes)

> Note: The mysql container included in docker-compose.yml is intended for quickstart testing purposes only. You can connect to your own MySQL instance by adjusting the environment variables as needed.

> If you want to use your own MySQL instance instead of the built-in test container: > - Update the target MySQL connection information in your .env file (see MYSQLHOST, MYSQLPORT, MYSQLUSER, MYSQLPASSWORD, MYSQL_DATABASE). > - In docker-compose.yml, comment out (disable) the mysql and mysql-init-data containers to avoid starting the built-in test database.

Flow Diagram of Quickstart/Tutorial

1. Environment Setup

> Note: The system automatically handles user permissions - both root users and regular users are supported with appropriate access control.

git clone https://github.com/call518/MCP-MySQL-Ops.git
cd MCP-MySQL-Ops

# Copy and check environment configuration
cp .env.example .env

Default configuration (works out-of-the-box):

#### MySQL Root Configuration for Docker:
MYSQL_ROOT_HOST=%
MYSQL_ROOT_PASSWORD=changeme!@34

#### MySQL Host Configuration:
MYSQL_HOST=host.docker.internal
MYSQL_PORT=13306
MYSQL_USER=root
MYSQL_PASSWORD=${MYSQL_PASSWORD}
MYSQL_DATABASE=test_ecommerce

For your own MySQL server:

# Edit .env file with your MySQL connection details
MYSQL_HOST=your-mysql-server.com
MYSQL_PORT=3306
MYSQL_USER=your_username     # Will auto-grant permissions on test DBs
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_default_db

# Then disable built-in containers in docker-compose.yml
# Comment out: mysql and mysql-init-data services

> Note: The MySQL container is configured with proper volume mapping for data persistence and initial database setup.

Additional Resources:

• MCP Tool Features (Swagger): http://localhost:8004/docs
• MCPO Proxy API Documentation: http://localhost:8004/mysql-ops/docs

2. Run Docker Stack

# Start all services (MySQL + MCP server + test interfaces)
docker-compose up -d

# Check container status
docker-compose ps

# Watch the logs (Ctrl+C to exit)
docker-compose logs -f mysql-init-data

⏱️ Container Startup Sequence & Wait Time:

• MySQL Container: Starts first and initializes database (~30-60 seconds)
• MySQL Init Data: Generates test data automatically (~1-2 minutes)
• MCP Server: Starts after MySQL is ready (~10-20 seconds)
• OpenWebUI: Starts last to ensure all services are available (~10-30 seconds)

💡 Please wait 2-3 minutes for all containers to fully initialize before accessing the web interface. You can monitor the startup progress with:

# Monitor all container logs
docker-compose logs -f

# Check if all containers are healthy
docker-compose ps

3. Automatic Test Data Generation

🎉 No manual setup required! Test data is automatically generated during first startup by the mysql-init-data container.

What happens automatically:

• ✅ 4 comprehensive test databases created (test_ecommerce, test_analytics, test_inventory, test_hr)
• ✅ ~2,745 realistic records with proper foreign key relationships
• ✅ User permissions automatically configured for your MYSQL_USER (from .env)
• ✅ Test users and roles created for different access scenarios

Manual execution (if needed):

# Force regenerate test data (optional)
docker-compose run --rm mysql-init-data /scripts/create-test-data.sh

# Check generation logs
docker logs mcp-mysql-ops-mysql-init-data

Verification:

# Connect and verify test databases exist
docker exec -it mcp-mysql-ops-mysql-8 mysql -u [your_mysql_user] -p -e "SHOW DATABASES;"

4. Access to OpenWebUI

🌐 Web Interface: http://localhost:3004/

> ⏳ Important: Please wait 2-3 minutes after running docker-compose up -d for all containers to fully initialize. OpenWebUI starts last to ensure all backend services (MySQL, test data generation, MCP server) are ready.

Quick Status Check:

# Verify all containers are running
docker-compose ps

# If any container shows "starting" or "unhealthy", wait a bit longer
# You can watch the startup logs:
docker-compose logs -f

5. Registering the Tool in OpenWebUI

> 📌 Note: Web-UI configuration instructions are based on OpenWebUI v0.6.22. Menu locations and settings may differ in newer versions.

1. logging in to OpenWebUI with an admin account
2. go to "Settings" → "Tools" from the top menu.
3. Enter the mysql-ops Tool address (e.g., http://localhost:8004/mysql-ops) to connect MCP Tools.
4. Setup Ollama or OpenAI.

6. Complete!

Congratulations! Your MCP MySQL Operations server is now ready for use. You can start exploring your databases with natural language queries.

🚀 Try These Example Queries:

• "Show me the current active connections"
• "What are the current server status and configuration?"
• "Analyze table sizes and storage efficiency"
• "Show me database size information"
• "What tables have the most rows?"

---

Security & Authentication

Bearer Token Authentication

For streamable-http mode, this MCP server supports Bearer token authentication to secure remote access. This is especially important when running the server in production environments.

Configuration

Enable Authentication:

# In .env file
REMOTE_AUTH_ENABLE=true
REMOTE_SECRET_KEY=your-secure-secret-key-here

Or via CLI:

python -m mcp_mysql_ops --type streamable-http --auth-enable --secret-key your-secure-secret-key-here

Security Levels

1. stdio mode (Default): Local-only access, no authentication needed
2. streamable-http + REMOTEAUTHENABLE=false: Remote access without authentication ⚠️ NOT RECOMMENDED for production
3. streamable-http + REMOTEAUTHENABLE=true: Remote access with Bearer token authentication ✅ RECOMMENDED for production

Client Configuration

When authentication is enabled, MCP clients must include the Bearer token in the Authorization header:

{
  "mcpServers": {
    "mcp-mysql-ops": {
      "type": "streamable-http",
      "url": "http://your-server:8000/mcp",
      "headers": {
        "Authorization": "Bearer your-secure-secret-key-here"
      }
    }
  }
}

Security Best Practices

• Always enable authentication when using streamable-http mode in production
• Use strong, randomly generated secret keys (32+ characters recommended)
• Use HTTPS when possible (configure reverse proxy with SSL/TLS)
• Restrict network access using firewalls or network policies
• Rotate secret keys regularly for enhanced security
• Monitor access logs for unauthorized access attempts

Error Handling

When authentication fails, the server returns:

• 401 Unauthorized for missing or invalid tokens
• Detailed error messages in JSON format for debugging

---

🐛 Usage & Configuration

This MCP server supports two connection modes: stdio (traditional) and streamable-http (Docker-based). You can configure the transport mode using CLI arguments or environment variables.

Configuration Priority: CLI arguments > Environment variables > Default values

CLI Arguments

• --type (-t): Transport type (stdio or streamable-http) - Default: stdio
• --host: Host address for HTTP transport - Default: 127.0.0.1
• --port (-p): Port number for HTTP transport - Default: 8000
• --auth-enable: Enable Bearer token authentication for streamable-http mode - Default: false
• --secret-key: Secret key for Bearer token authentication (required when auth enabled)

Environment Variables

| Variable | Description | Default | Project Default | |----------|-------------|---------|-----------------| | PYTHONPATH | Python module search path for MCP server imports | - | /app/src | | MCP_LOG_LEVEL | Server logging verbosity (DEBUG, INFO, WARNING, ERROR) | INFO | INFO | | FASTMCP_TYPE | MCP transport protocol (stdio for CLI, streamable-http for web) | stdio | streamable-http | | FASTMCP_HOST | HTTP server bind address (0.0.0.0 for all interfaces) | 127.0.0.1 | 0.0.0.0 | | FASTMCP_PORT | HTTP server port for MCP communication | 8000 | 8000 | | REMOTE_AUTH_ENABLE | Enable Bearer token authentication for streamable-http mode. If undefined/empty, defaults to false. Accepts: true/false, 1/0, yes/no, on/off (case insensitive) | false | false | | REMOTE_SECRET_KEY | Secret key for Bearer token authentication. If undefined/empty, authentication will be disabled even if REMOTE_AUTH_ENABLE=true. Recommended: 32+ character random string | "" (empty) | your-secret-key-here | | MYSQL_HOST | MySQL server hostname or IP address | 127.0.0.1 | host.docker.internal | | MYSQL_PORT | MySQL server port number | 3306 | 13306 | | MYSQL_USER | Username for MySQL server authentication | root | root | | MYSQL_PASSWORD | Password for MySQL server authentication | - | changeme!@34 | | MYSQL_DATABASE | Name of the default target MySQL database | - | test_ecommerce | | DOCKER_EXTERNAL_PORT_OPENWEBUI | Host port mapping for Open WebUI container | 8080 | 3004 |

Environment Setup

Copy .env.example to .env and configure your environment:

cp .env.example .env
# Edit .env file with your specific configuration

Environment Variable Defaults Policy

Authentication Variables:

• REMOTE_AUTH_ENABLE: If undefined, commented out, or empty → defaults to false
• REMOTE_SECRET_KEY: If undefined, commented out, or empty → defaults to "" (empty string)

Security Behavior:

• Authentication is only enabled when both conditions are met:

1. REMOTE_AUTH_ENABLE is explicitly set to a truthy value (true/1/yes/on)
2. REMOTE_SECRET_KEY is set to a non-empty string

• If either condition fails, the server runs without authentication
• This ensures safe defaults and prevents accidental authentication bypass

Example configurations:

# Authentication disabled (all equivalent)
# REMOTE_AUTH_ENABLE=            # undefined/commented
REMOTE_AUTH_ENABLE=false         # explicit false
REMOTE_AUTH_ENABLE=""           # empty string

# Authentication enabled (requires both)
REMOTE_AUTH_ENABLE=true         # or 1, yes, on
REMOTE_SECRET_KEY=my-secret-key # non-empty string

---

🛠️ Local Development & Installation

For developers wanting to run the MCP server locally or integrate it into their own projects:

Method 1: Console Script (Recommended)

# Clone and install
git clone https://github.com/call518/MCP-MySQL-Ops.git
cd MCP-MySQL-Ops
pip install -e .

# Run with simple command
mcp-mysql-ops --type stdio
mcp-mysql-ops --type streamable-http --host 127.0.0.1 --port 8000

Method 2: Module Execution

# Clone and set PYTHONPATH
git clone https://github.com/call518/MCP-MySQL-Ops.git
cd MCP-MySQL-Ops
export PYTHONPATH=$(pwd)/src

# Run as module
python -m mcp_mysql_ops --type stdio
python -m mcp_mysql_ops --type streamable-http --host 127.0.0.1 --port 8000

> 💡 Pro Tip: Use Method 1 (console script) for cleaner integration. Method 2 is useful when you need to modify the source code directly.

Running Tests

Two test suites are available:

| Suite | File | Requires Docker | |-------|------|----------------| | Unit tests (version compatibility logic) | tests/test_version_compat.py | No | | Integration tests (all tools × MySQL 5.7 / 8.0 / 8.4) | tests/test_tools_integration.py | Yes |

Run everything with a single command

uv run pytest automatically starts the Docker test containers (MySQL 5.7, 8.0, 8.4), waits for them to be fully initialized (schema + seed data + Performance Schema), runs all tests, then tears everything down.

# Install dev dependencies (one-time)
uv sync --extra dev

# Run all tests (unit + integration) — Docker is managed automatically
uv run pytest -v

# Unit tests only (no Docker needed)
uv run pytest tests/test_version_compat.py -v

# Integration tests only
uv run pytest tests/test_tools_integration.py -v

# Run against a specific MySQL version only
uv run pytest -v -k MySQL80

> Note: Docker must be running. The test stack uses ports 3357 (MySQL 5.7), 3380 (8.0), and 3384 (8.4) on 127.0.0.1. > > If containers are already running (e.g. between repeated debug runs), the test fixture detects them and skips the up/down lifecycle — useful for fast iteration. To pre-start manually: > > ``bash > docker compose -f tests/docker/docker-compose.test.yml up -d > uv run pytest -v # reuses running containers, does not tear down > ``

Continuous Integration

Every push to main and every pull request triggers [.github/workflows/test.yml](.github/workflows/test.yml), which runs the unit suite and the full MySQL 5.7 / 8.0 / 8.4 integration matrix on GitHub-hosted runners.

---

(NOTE) Sample Test Data Overview

The test data generation system follows the PostgreSQL MCP project pattern - using a dedicated mysql-init-data container that automatically creates comprehensive test databases on first startup.

🚀 Automatic Test Data Generation

The mysql-init-data container (defined in docker-compose.yml) automatically executes scripts/create-test-data.sh and scripts/create-test-data.sql on first startup, generating realistic business data for MCP tool testing.

| Database | Purpose | Tables | Scale | |----------|---------|--------|-------| | testecommerce | E-commerce system | categories, products, customers, orders, orderitems | 10 categories, 500 products, 100 customers, 1000 orders, 2500 order items | | test_analytics | Anal

…

Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: call518
• Source: call518/MCP-MySQL-Ops
• License: MIT
• Homepage: https://deepwiki.com/call518/MCP-MySQL-Ops

Install and usage instructions live in the source repository linked above.