Labsco
call518 logo

MCP-MySQL-Ops

★ 2

from call518

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.

🔥🔥🔥🔥✓ VerifiedPaid serviceAdvanced setup
#automation#analytics#e-commerce#database#cloud#devops#git#maps

MCP Server for MySQL Operations and Monitoring

License: MIT Python Docker Pulls MySQL BuyMeACoffee

Deploy to PyPI with tag PyPI PyPI - Downloads

Architecture & Internal (DeepWiki)

Ask DeepWiki

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.

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 + REMOTE_AUTH_ENABLE=false: Remote access without authentication ⚠️ NOT RECOMMENDED for production
  3. streamable-http + REMOTE_AUTH_ENABLE=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

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

DatabasePurposeTablesScale
test_ecommerceE-commerce systemcategories, products, customers, orders, order_items10 categories, 500 products, 100 customers, 1000 orders, 2500 order items
test_analyticsAnalytics & reportingpage_views, sales_summary500 page views, 30 sales summaries
test_inventoryWarehouse managementsuppliers, inventory_items, purchase_orders10 suppliers, 100 items, 50 purchase orders
test_hrHR managementdepartments, employees, payroll5 departments, 50 employees, 150 payroll records

Total Records: ~2,745 records across all test databases

Test users created: app_readonly, app_readwrite, analytics_user, backup_user

User Permission Management: The system automatically creates specified MYSQL_USER (from .env) and grants full permissions on the 4 test databases only, ensuring secure access control.

Features for Testing:

  • ✅ Foreign key relationships with proper referential integrity
  • ✅ Various storage engines (InnoDB optimization)
  • ✅ Mixed index types (used/unused for testing index analysis tools)
  • ✅ Time-series data for analytics testing
  • ✅ Realistic business scenarios across multiple domains
  • ✅ Safe test environment with isolated user permissions

📋 PostgreSQL-Style Init Pattern

Similar to the MCP-PostgreSQL-Ops project, this MySQL implementation uses:

  • Dedicated init container (mysql-init-data) for one-time data generation
  • Health check dependencies ensuring MySQL is ready before data creation
  • Root privileges for database creation, then permission delegation to specified user
  • Comprehensive logging and error handling during initialization

Tool Compatibility Matrix

Automatic Adaptation: All tools work transparently across supported versions - no configuration needed!

🟢 Professional MySQL Tools (19 Tools Available)

Tool NameMySQL VersionsFeaturesInformation Source
get_server_infoMySQL 5.7.9+ / 8.0+✅ Server version, configuration, status variablesSHOW VERSION, INFORMATION_SCHEMA
get_database_listMySQL 5.7.9+ / 8.0+✅ Database sizes, character sets, collationsINFORMATION_SCHEMA.SCHEMATA, information_schema.tables
get_table_listMySQL 5.7.9+ / 8.0+✅ Table information, storage engines, row countsINFORMATION_SCHEMA.TABLES
get_table_schema_infoMySQL 5.7.9+ / 8.0+✅ Columns, indexes, constraints, foreign keysINFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.STATISTICS
get_database_overviewMySQL 5.7.9+ / 8.0+✅ Database summary, table counts, sizesINFORMATION_SCHEMA.TABLES, aggregated statistics
get_user_listMySQL 5.7.9+ / 8.0+✅ MySQL users, hosts, privileges, account statusmysql.user, INFORMATION_SCHEMA.USER_PRIVILEGES
get_active_connectionsMySQL 5.7.9+ / 8.0+✅ Active connections, connection details, process listperformance_schema.processlist
get_server_statusMySQL 5.7.9+ / 8.0+✅ Server status variables, performance countersSHOW STATUS, system status variables
get_table_size_infoMySQL 5.7.9+ / 8.0+✅ Table sizes, index sizes, data/index ratiosINFORMATION_SCHEMA.TABLES (DATA_LENGTH, INDEX_LENGTH)
get_database_size_infoMySQL 5.7.9+ / 8.0+✅ Database sizes, storage usage analysisAggregated INFORMATION_SCHEMA.TABLES data
get_index_usage_statsMySQL 5.7.9+ / 8.0+✅ Index usage, cardinality, efficiency analysisINFORMATION_SCHEMA.STATISTICS, SHOW INDEX

🚀 Performance Schema Enhanced Tools (8 Additional Tools)

Tool NameMySQL VersionsFeaturesInformation Source
get_mysql_configMySQL 5.7.9+ / 8.0+✅ MySQL configuration variables and settingsSHOW VARIABLES, system configuration
get_slow_queriesMySQL 5.7.9+ / 8.0+✅ Slow query analysis and performance insightsPerformance Schema, slow query log
get_table_io_statsMySQL 5.7.9+ / 8.0+✅ Table I/O statistics and access patternsPerformance Schema I/O monitoring
get_lock_monitoringMySQL 5.7.9+ / 8.0+✅ Lock analysis and contention monitoringPerformance Schema lock tables
get_all_databases_tablesMySQL 5.7.9+ / 8.0+✅ Cross-database table overview and analysisMulti-database INFORMATION_SCHEMA queries
get_all_databases_table_sizesMySQL 5.7.9+ / 8.0+✅ Global table size analysis across databasesAggregated size statistics
get_connection_infoMySQL 5.7.9+ / 8.0+✅ Connection details and session informationperformance_schema.processlist
get_current_database_infoMySQL 5.7.9+ / 8.0+✅ Current database context and detailsActive database information

🚀 Version-Aware Enhancements

FeatureMySQL 5.7.9+MySQL 8.0+Enhanced Capabilities
Performance Schema✅ Basic✅ EnhancedMySQL 8.0+: Advanced query monitoring, improved Performance Schema tables
Information Schema✅ Standard✅ EnhancedMySQL 8.0+: Additional metadata tables and improved statistics
Storage Engine Info✅ InnoDB Focus✅ Multi-EngineMySQL 8.0+: Enhanced storage engine statistics and monitoring
JSON Support✅ Basic✅ AdvancedMySQL 8.0+: Improved JSON functions and indexing capabilities
User Management✅ Traditional✅ Role-BasedMySQL 8.0+: Role-based access control and enhanced security features

📋 MySQL Version Support: Requires MySQL 5.7.9 or newer (5.7.9, 8.0+, 8.4+ are supported). The minimum is dictated by performance_schema.processlist, introduced in MySQL 5.7.9 and used by get_active_connections, get_connection_info, and get_lock_monitoring. MySQL 8.1+ and 8.2+ compatibility will be added as they reach stable release status.

Environment Variables

VariableDescriptionDefaultProject Default
PYTHONPATHPython module search path for MCP server imports-/app/src
MCP_LOG_LEVELServer logging verbosity (DEBUG, INFO, WARNING, ERROR)INFOINFO
FASTMCP_TYPEMCP transport protocol (stdio for CLI, streamable-http for web)stdiostreamable-http
FASTMCP_HOSTHTTP server bind address (0.0.0.0 for all interfaces)127.0.0.10.0.0.0
FASTMCP_PORTHTTP server port for MCP communication80008000
MYSQL_VERSIONMySQL major version for Docker image selection8.08.0
MYSQL_HOSTMySQL server hostname or IP address127.0.0.1host.docker.internal
MYSQL_PORTMySQL server port number330613306
MYSQL_USERMySQL connection username (auto-granted permissions on test DBs)roottestuser
MYSQL_PASSWORDMySQL user password (supports special characters)changeme!@34testpass
MYSQL_DATABASEDefault database name for connectionsinformation_schematestdb
MYSQL_ROOT_HOSTMySQL root host access pattern for Docker container%%
MYSQL_ROOT_PASSWORDMySQL root password for Docker container initializationchangeme!@34changeme!@34
DOCKER_EXTERNAL_PORT_OPENWEBUIHost port mapping for Open WebUI container80803004
DOCKER_EXTERNAL_PORT_MCP_SERVERHost port mapping for MCP server container800018004
DOCKER_EXTERNAL_PORT_MCPO_PROXYHost port mapping for MCPO proxy container80008004

Note: MYSQL_DATABASE serves as the default target database for operations when no specific database is specified. In Docker environments, if set to a custom database name, this database will be automatically created during initial MySQL startup.

User Permission Management: When using a non-root MYSQL_USER, the initialization process automatically:

  • Creates the specified user if it doesn't exist
  • Grants full permissions on the 4 test databases (test_ecommerce, test_analytics, test_inventory, test_hr)
  • Maintains security by restricting access to only the necessary databases
  • Enables monitoring capabilities through automatic information_schema/performance_schema access

RDS/Aurora MySQL Compatibility

  • This server is read-only and works with regular roles on AWS RDS MySQL and Aurora MySQL. All core functionality is available through standard Information Schema and Performance Schema access.
  • Performance Schema is enabled by default on RDS/Aurora MySQL 8.0+ instances.
  • For enhanced monitoring capabilities, consider Parameter Group configuration: 
    -- Check Performance Schema status
SHOW VARIABLES LIKE 'performance_schema';

-- Verify Information Schema settings
SHOW VARIABLES LIKE 'information_schema_stats_expiry';

-- Recommended user permissions for monitoring
GRANT SELECT ON *.* TO <monitoring_user>@'%';
GRANT PROCESS ON *.* TO <monitoring_user>@'%';

Example Queries

🟢 Core MySQL Monitoring Tools (Always Available)

  • get_server_info

    • "Show MySQL server version and configuration status."
    • "Check server system variables and runtime configuration."
    • "Display MySQL server capabilities and version information."
    • 📋 Features: Server version, system variables, configuration status, feature availability
    • 🔧 MySQL 5.7.9+/8.0+: Fully compatible, no additional setup required

  • get_database_list

    • "List all databases and their sizes."
    • "Show database list with character sets and collation information."
    • "Display database storage usage and table counts."
    • 📋 Features: Database sizes, character sets, collations, table counts, storage usage
    • 🔧 MySQL 5.7.9+/8.0+: Uses Information Schema for comprehensive database information

  • get_table_list

    • "List all tables in the test_ecommerce database."
    • "Show table information with storage engines and row counts."
    • "Display table creation dates and update timestamps."
    • 📋 Features: Table names, storage engines, row counts, sizes, creation/update times
    • ⚠️ Required: database_name parameter must be specified
    • 💡 Usage: Supports filtering by table name patterns

  • get_table_schema_info

    • "Show detailed schema information for the customers table in test_ecommerce database."
    • "Get column details and constraints for products table in test_ecommerce database."
    • "Analyze table structure with indexes and foreign keys for orders table in test_ecommerce database."
    • "Show schema overview for all tables in test_inventory database."
    • 📋 Features: Column types, constraints, indexes, foreign keys, table metadata
    • ⚠️ Required: database_name parameter must be specified
    • 💡 Usage: Leave table_name empty for database-wide schema analysis

  • get_database_overview

    • "Show comprehensive database overview for test_ecommerce database."
    • "Get detailed summary of test_analytics database structure and statistics."
    • "Analyze database overview with table counts and sizes for test_inventory database."
    • "Show database structure summary for test_hr database."
    • 📋 Features: Database overview, table statistics, storage summary, schema analysis
    • ⚠️ Required: database_name parameter must be specified

  • get_user_list

    • "List all MySQL users and their privileges."
    • "Show user accounts with host information and account status."
    • "Display user privilege summary and authentication details."
    • 📋 Features: User accounts, host patterns, privileges, account status, authentication info
    • 🔧 MySQL 5.7.9+/8.0+: Enhanced user management information in MySQL 8.0+

  • get_active_connections

    • "Show all active connections and their details."
    • "List current database connections with user and host information."
    • "Monitor active sessions and their current operations."
    • "Display connection statistics and process information."
    • 📋 Features: Active connections, process list, connection details, session information
    • 🔧 MySQL 5.7.9+/8.0+: Enhanced process information with Performance Schema integration

  • get_server_status

    • "Show MySQL server status variables and performance counters."
    • "Display current server performance metrics and statistics."
    • "Monitor server operational status and key performance indicators."
    • "Analyze server health metrics and resource utilization."
    • 📋 Features: Status variables, performance counters, connection statistics, resource metrics
    • 🔧 MySQL 5.7.9+/8.0+: Comprehensive server status monitoring

  • get_table_size_info

    • "Show table and index size analysis for test_ecommerce database."
    • "Find largest tables by data and index size."
    • "Analyze storage efficiency and table size distribution."
    • "Display table size details with data/index ratios."
    • 📋 Features: Table sizes, index sizes, data/index ratios, storage efficiency analysis
    • ⚠️ Required: database_name parameter for accurate size analysis

  • get_database_size_info

    • "Show database capacity analysis and storage usage."
    • "Find the largest databases by total size."
    • "Display comprehensive database size statistics."
    • "Analyze storage distribution across databases."
    • 📋 Features: Database sizes, table counts, storage distribution, capacity analysis
    • 🔧 MySQL 5.7.9+/8.0+: Accurate size calculation using Information Schema

  • get_index_usage_stats

    • "Analyze index usage and efficiency statistics."
    • "Show index cardinality and selectivity information."
    • "Find potentially unused or redundant indexes."
    • "Display index performance and usage patterns."
    • 📋 Features: Index statistics, cardinality, selectivity, usage analysis, optimization recommendations
    • ⚠️ Required: database_name parameter for targeted index analysis
    • 💡 Enhanced with: Performance Schema enabled for more detailed statistics

🚀 Version-Enhanced Tools

  • get_server_info (Enhanced!)
    • "Show server version and MySQL 8.0 advanced features."
    • "Check server compatibility and available enhancements."
    • "Display MySQL version-specific capabilities and recommendations."
    • 📈 MySQL 8.0+: Enhanced JSON support, improved Performance Schema, CTE support, window functions
    • 📊 MySQL 5.7.9+: Core functionality with basic JSON and Performance Schema support

💡 Natural Language Query Examples

Test tools with realistic prompts - never use function names directly:

  • ✅ "Show me the current server status and key performance metrics"
  • ❌ "Run get_server_status"

📊 Monitoring Examples:

  • "What databases exist and how much space do they use?"
  • "Show me all tables in the ecommerce database with their sizes"
  • "Which tables have the most rows and largest indexes?"
  • "Display current database connections and their activity"
  • "Analyze the schema structure of the test_ecommerce database"
  • "Show me MySQL server configuration and performance status"
  • "List all users and their database privileges"
  • "Find tables that might need index optimization"

💡 Pro Tip: All tools support multi-database operations using the database_name parameter. This allows MySQL root users to analyze and monitor multiple databases from a single MCP server instance.

🚀 Adding Custom Tools

This MCP server is designed for easy extensibility. Follow these 5 simple steps to add your own custom tools:

Step-by-Step Guide

1. Add Helper Functions (Optional)

Add reusable data functions to src/<package_name>/functions.py:

async def get_your_custom_data(target_resource: str = None) -> List[Dict[str, Any]]:
    """Your custom data retrieval function."""
    # Example implementation - adapt to your service
    data_source = await get_data_connection(target_resource)
    results = await fetch_data_from_source(
        source=data_source,
        filters=your_conditions,
        aggregations=["count", "sum", "avg"],
        sorting=["count DESC", "timestamp ASC"]
    )
    return results

2. Create Your MCP Tool

Add your tool function to src/<package_name>/mcp_main.py:

@mcp.tool()
async def get_your_custom_analysis(limit: int = 50, target_name: Optional[str] = None) -> str:
    """
    [Tool Purpose]: Brief description of what your tool does
    
    [Exact Functionality]:
    - Feature 1: Data aggregation and analysis
    - Feature 2: Resource monitoring and insights
    - Feature 3: Performance metrics and reporting
    
    [Required Use Cases]:
    - When user asks "your specific analysis request"
    - Your business-specific monitoring needs
    
    Args:
        limit: Maximum results (1-100)
        target_name: Target resource/service name
    
    Returns:
        Formatted analysis results
    """
    try:
        limit = max(1, min(limit, 100))  # Always validate input
        
        results = await get_your_custom_data(target_resource=target_name)
        
        if results:
            results = results[:limit]
        
        return format_table_data(results, f"Custom Analysis (Top {len(results)})")
        
    except Exception as e:
        logger.error(f"Failed to get custom analysis: {e}")
        return f"Error: {str(e)}"

3. Update Imports (If Needed)

Add your helper function to imports in src/<package_name>/mcp_main.py:

from .functions import (
    # ...existing imports...
    get_your_custom_data,  # Add your new function
)

4. Update Prompt Template (Recommended)

Add your tool description to src/<package_name>/prompt_template.md for better natural language recognition:

### **Your Custom Analysis Tool**

### X. **get_your_custom_analysis**
**Purpose**: Brief description of what your tool does
**Usage**: "Show me your custom analysis" or "Get custom analysis for database_name"
**Features**: Data aggregation, resource monitoring, performance metrics
**Required**: `target_name` parameter for specific resource analysis

5. Test Your Tool

# Local testing
./scripts/run-mcp-inspector-local.sh

# Or with Docker
docker-compose up -d
docker-compose logs -f mcp-server

# Test with natural language:
# "Show me your custom analysis"
# "Get custom analysis for target_name"

That's it! Your custom tool is ready to use with natural language queries.

Development

Testing & Development

# Test with MCP Inspector
./scripts/run-mcp-inspector-local.sh

# Direct execution methods for debugging
# Method 1: Console script (after pip install -e .)
pip install -e .
mcp-mysql-ops --log-level DEBUG

# Method 2: Module execution
PYTHONPATH=src python -m mcp_mysql_ops --log-level DEBUG

# Test with different MySQL versions
# Modify MYSQL_HOST in .env to point to different MySQL instances

# Run tests (if you add any)
uv run pytest

Version Compatibility Testing

The MCP server automatically adapts to MySQL versions 5.7.9+ and 8.0+. To test across versions:

  1. Set up test databases: Different MySQL versions (5.7, 8.0, 8.1+)
  2. Run compatibility tests: Point to each version and verify tool behavior
  3. Check feature detection: Ensure proper version detection and feature availability
  4. Verify performance: Confirm optimal performance across MySQL versions

Security Notes

  • All tools are read-only - no data modification capabilities
  • Sensitive information (passwords) are masked in outputs
  • No direct SQL execution - only predefined, safe queries
  • Follows principle of least privilege
  • Compatible with MySQL security best practices

Contributing

🤝 Got ideas? Found bugs? Want to add cool features?

We're always excited to welcome new contributors! Whether you're fixing a typo, adding a new monitoring tool, or improving documentation - every contribution makes this project better.

Ways to contribute:

  • 🐛 Report issues or bugs
  • 💡 Suggest new MySQL monitoring features
  • 📝 Improve documentation
  • 🚀 Submit pull requests
  • ⭐ Star the repo if you find it useful!

Pro tip: The codebase is designed to be super friendly for adding new tools. Check out the existing @mcp.tool() functions in mcp_main.py.

MCPO Swagger Docs

[MCPO Swagger URL] http://localhost:8004/mysql-ops/docs

MCPO Swagger APIs

License

Freely use, modify, and distribute under the MIT License.

⭐ Other Projects

Other MCP servers by the same author: