MCP-MySQL-Ops

MCP Server for MySQL Operations and Monitoring

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

- ✅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.

- 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.

Note:Themysqlcontainer included indocker-compose.ymlis 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.envfile (see MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE).
- Indocker-compose.yml, comment out (disable) themysqlandmysql-init-datacontainers to avoid starting the built-in test database.

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

# 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.

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

# 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

# Monitor all container logs docker-compose logs -f # Check if all containers are healthy docker-compose ps

# 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

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

⏳ Important: Please wait2-3 minutesafter runningdocker-compose up -dfor all containers to fully initialize. OpenWebUI starts last to ensure all backend services (MySQL, test data generation, MCP server) are ready.

# 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

📌Note: Web-UI configuration instructions are based on OpenWebUIv0.6.22. Menu locations and settings may differ in newer versions.
- logging in to OpenWebUI with an admin account
- go to "Settings" → "Tools" from the top menu.
- Enter themysql-opsTool address (e.g.,http://localhost:8004/mysql-ops) to connect MCP Tools.
- Setup Ollama or OpenAI.

Congratulations!Your MCP MySQL Operations server is now ready for use. You can start exploring your databases with natural language 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?"

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

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

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

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

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" } } } }

- Always enable authenticationwhen using streamable-http mode in production
- Use strong, randomly generated secret keys(32+ characters recommended)
- Use HTTPSwhen possible (configure reverse proxy with SSL/TLS)
- Restrict network accessusing firewalls or network policies
- Rotate secret keys regularlyfor enhanced security
- Monitor access logsfor unauthorized access attempts

When authentication fails, the server returns:

- 401 Unauthorizedfor missing or invalid tokens
- Detailed error messagesin JSON format for debugging

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

Configuration Priority:CLI arguments > Environment variables > Default values

- --type(-t): Transport type (stdioorstreamable-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)

Copy.env.exampleto.envand configure your environment:

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

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

- Authentication isonly enabledwhen both conditions are met:
- REMOTE_AUTH_ENABLEis explicitly set to a truthy value (true/1/yes/on)
- REMOTE_SECRET_KEYis set to a non-empty string

# 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

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

# 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

# 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.

uv run pytestautomatically 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 ports3357(MySQL 5.7),3380(8.0), and3384(8.4) on127.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:

docker compose -f tests/docker/docker-compose.test.yml up -d uv run pytest -v # reuses running containers, does not tear down

Every push tomainand every pull request triggers.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.

Similar to theMCP-PostgreSQL-Opsproject, this MySQL implementation uses:

{ "mcpServers": { "mcp-mysql-ops": { "command": "uvx", "args": ["--python", "3.11", "mcp-mysql-ops"], "env": { "MYSQL_HOST": "127.0.0.1", "MYSQL_PORT": "13306", "MYSQL_USER": "root", "MYSQL_PASSWORD": "changeme!@34", "MYSQL_DATABASE": "test_ecommerce" } } } }

Method 2: Remote MCP (transport="streamable-http")

{ "mcpServers": { "mcp-mysql-ops": { "type": "streamable-http", "url": "http://localhost:18004/mcp" } } }

With Bearer Token Authentication (Recommended for production):

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

"Display MySQL server capabilities and version information."

"Draw relationships as a Mermaid diagram"

{ "mcpServers": { "mcp-mysql-ops": { "command": "uv", "args": ["run", "python", "-m", "src.mcp_mysql_ops.mcp_main"], "env": { "PYTHONPATH": "/path/to/MCP-MySQL-Ops", "MYSQL_HOST": "127.0.0.1", "MYSQL_PORT": "13306", "MYSQL_USER": "root", "MYSQL_PASSWORD": "changeme!@34", "MYSQL_DATABASE": "test_ecommerce" } } } }

# Stdio mode uvx --python 3.11 mcp-mysql-ops \ --type stdio # HTTP mode uvx --python 3.11 mcp-mysql-ops --type streamable-http \ --host 127.0.0.1 \ --port 8000 \ --log-level DEBUG

(Option) Configure Multiple MySQL Instances