MySQL Performance Tuning MCP

A Model Context Protocol (MCP) server for MySQL performance tuning and analysis.

Overview

mysqltuner_mcp provides AI-powered MySQL database performance analysis through the Model Context Protocol. It offers tools for query optimization, index recommendations, health monitoring

Features

Performance Analysis

• Slow Query Detection: Identify slow queries from performance_schema
• Query Analysis: Get detailed EXPLAIN plans with recommendations
• Table Statistics: Analyze table sizes, row counts, and fragmentation
• Statement Analysis: Analyze SQL statements for temp tables, sorting, and full scans

Index Optimization

• Index Recommendations: AI-powered suggestions based on query patterns
• Unused Index Finder: Identify indexes that are never read
• Duplicate Detection: Find redundant and overlapping indexes
• Index Statistics: Cardinality, selectivity, and usage metrics

Health Monitoring

• Health Check: Comprehensive database health assessment with scoring
• Active Queries: Real-time query monitoring
• Wait Event Analysis: Identify I/O and lock bottlenecks
• Configuration Review: Settings analysis with recommendations

Storage Engine Analysis

• Engine Statistics: Analyze storage engine usage and distribution
• Fragmentation Detection: Find fragmented tables with OPTIMIZE recommendations
• Auto-Increment Analysis: Detect columns approaching overflow limits

InnoDB Analysis

• InnoDB Status: Parse and analyze SHOW ENGINE INNODB STATUS
• Buffer Pool Analysis: Detailed buffer pool usage by schema and table
• Transaction Analysis: Monitor transactions, lock waits, and deadlocks

Memory Analysis

• Memory Calculations: Calculate per-thread and global buffer usage
• Memory by Host/User: Breakdown memory usage by connection source
• Table Cache Analysis: Analyze table open cache efficiency

Replication Monitoring

• Master/Slave Status: Monitor replication health and lag
• Galera Cluster: Full Galera cluster status for MariaDB/Percona
• Group Replication: MySQL Group Replication monitoring

Security Analysis

• Security Audit: Check for anonymous users, weak passwords, dangerous privileges
• User Privileges: Analyze user privileges at all levels
• Audit Log: Check audit logging configuration

Resources & Prompts

• Built-in best practices documentation
• Pre-configured prompts for common tuning tasks
• Index optimization guidelines
• Configuration optimization guide

Installation

From Source

git clone https://github.com/yourusername/mysqltuner_mcp.git
cd mysqltuner_mcp
pip install -e .

Using pip (when published)

pip install mysqltuner_mcp

Configuration

Environment Variables

Variable	Description	Default
MYSQL_URI	MySQL connection URI (required)	-
MYSQL_POOL_SIZE	Connection pool size	5
MYSQL_SSL	Enable SSL/TLS connection	false
MYSQL_SSL_CA	Path to CA certificate file	-
MYSQL_SSL_CERT	Path to client certificate file	-
MYSQL_SSL_KEY	Path to client private key file	-
MYSQL_SSL_VERIFY_CERT	Verify server certificate	true
MYSQL_SSL_VERIFY_IDENTITY	Verify server hostname matches certificate	false

Connection URI Format

Environment Variables

export MYSQL_URI="mysql://user:password@host:3306/database"
export MYSQL_SSL=true
export MYSQL_SSL_CA="/path/to/ca.pem"  # Optional: CA certificate for verification

Connection URI Query Parameters

export MYSQL_URI="mysql://user:password@host:3306/database?ssl=true&ssl_ca=/path/to/ca.pem"

Usage

Running the Server

The server supports three transport modes: stdio (default), SSE, and streamable-http.

# As a module
python -m mysqltuner_mcp

# Using the entry point
mysqltuner-mcp

# Explicitly specifying stdio mode
python -m mysqltuner_mcp --mode stdio

SSE Mode (Server-Sent Events)

HTTP transport using Server-Sent Events, suitable for web-based MCP clients:

# Start SSE server on default port 8080
python -m mysqltuner_mcp --mode sse

# Specify custom host and port
python -m mysqltuner_mcp --mode sse --host 127.0.0.1 --port 3000

# Enable debug mode
python -m mysqltuner_mcp --mode sse --debug

SSE Endpoints:

• http://<host>:<port>/sse - SSE connection endpoint
• http://<host>:<port>/messages/ - Message posting endpoint

Streamable HTTP Mode

Modern HTTP transport with session management:

# Start streamable HTTP server (stateful, with session tracking)
python -m mysqltuner_mcp --mode streamable-http

# Start in stateless mode (fresh transport per request)
python -m mysqltuner_mcp --mode streamable-http --stateless

# Specify custom host and port
python -m mysqltuner_mcp --mode streamable-http --host 127.0.0.1 --port 3000

Streamable HTTP Endpoint:

• http://<host>:<port>/mcp - Single endpoint for all MCP communication

Command-Line Options

Option	Description	Default
--mode	Server mode: stdio, sse, or streamable-http	stdio
--host	Host to bind to (HTTP modes only)	0.0.0.0
--port	Port to listen on (HTTP modes only)	8080 or PORT env var
--stateless	Run in stateless mode (streamable-http only)	false
--debug	Enable debug logging	false

MCP Client Configuration

Add to your MCP client configuration (e.g., Claude Desktop):

{
  "mcpServers": {
    "mysqltuner_mcp": {
      "command": "python",
      "args": ["-m", "mysqltuner_mcp"],
      "env": {
        "MYSQL_URI": "mysql://root:your_password@localhost:3306/your_database"
      }
    }
  }
}

With SSL/TLS Enabled

{
  "mcpServers": {
    "mysqltuner_mcp": {
      "command": "python",
      "args": ["-m", "mysqltuner_mcp"],
      "env": {
        "MYSQL_URI": "mysql://root:your_password@localhost:3306/your_database",
        "MYSQL_SSL": "true",
        "MYSQL_SSL_CA": "/path/to/ca.pem"
      }
    }
  }
}

Available Tools

Performance Tools

Tool	Description
get_slow_queries	Retrieve slow queries from performance_schema with detailed statistics
analyze_query	Get EXPLAIN plan and analysis for a query with optimization recommendations
get_table_stats	Get table statistics including size, row counts, fragmentation, and indexes

Index Tools

Tool	Description
get_index_recommendations	AI-powered index suggestions based on query patterns from performance_schema
find_unused_indexes	Find unused, duplicate, and redundant indexes with DROP statements
get_index_stats	Detailed index statistics including cardinality, selectivity, and usage metrics

Health Tools

Tool	Description
check_database_health	Comprehensive health check with scoring (connections, buffer pool, queries, etc.)
get_active_queries	Monitor currently running queries and identify long-running/blocked queries
review_settings	Analyze MySQL configuration settings with best practice recommendations
analyze_wait_events	Identify wait event bottlenecks (I/O, locks, buffer, log waits)

Storage Engine Tools

Tool	Description
analyze_storage_engines	Analyze storage engine usage, statistics, and recommendations
get_fragmented_tables	Find tables with significant fragmentation and wasted space
analyze_auto_increment	Check auto-increment columns for potential overflow issues

InnoDB Tools

Tool	Description
get_innodb_status	Parse and analyze SHOW ENGINE INNODB STATUS output
analyze_buffer_pool	Detailed InnoDB buffer pool analysis by schema and table
analyze_innodb_transactions	Analyze InnoDB transactions, lock waits, and deadlocks

Memory Tools

Tool	Description
calculate_memory_usage	Calculate MySQL memory usage (per-thread and global buffers)
get_memory_by_host	Get memory usage breakdown by host, user, or event
get_table_memory_usage	Analyze table cache and InnoDB buffer pool by table

Replication Tools

Tool	Description
get_replication_status	Get master/slave replication status and health
get_galera_status	Get Galera cluster status (MariaDB/Percona XtraDB Cluster)
get_group_replication_status	Get MySQL Group Replication status

Security Tools

Tool	Description
analyze_security	Comprehensive security analysis (users, passwords, SSL, privileges)
analyze_user_privileges	Analyze privileges for specific users or all users
check_audit_log	Check audit log configuration and status

Statement Analysis Tools

Tool	Description
analyze_statements	Comprehensive SQL statement analysis from performance_schema
get_statements_with_temp_tables	Find statements creating temporary tables (memory and disk)
get_statements_with_sorting	Find statements with sorting operations and file sorts
get_statements_with_full_scans	Find statements performing full table scans
get_statements_with_errors	Find statements producing errors or warnings

Available Prompts

Prompt	Description
optimize_slow_query	Analyze and optimize a slow query
health_check	Perform comprehensive health assessment
index_review	Review indexes for a database
performance_audit	Full performance audit

Requirements

• Python 3.10+
• MySQL 5.7+ or MySQL 8.0+
• performance_schema enabled (for full functionality)

MySQL Permissions

The MySQL user needs the following privileges:

GRANT SELECT ON performance_schema.* TO 'your_user'@'%';
GRANT SELECT ON information_schema.* TO 'your_user'@'%';
GRANT PROCESS ON *.* TO 'your_user'@'%';
-- For EXPLAIN on user databases:
GRANT SELECT ON your_database.* TO 'your_user'@'%';

Development

Setup Development Environment

git clone https://github.com/yourusername/mysqltuner_mcp.git
cd mysqltuner_mcp
python -m venv .venv
source .venv/bin/activate  # or .venv\Scripts\activate on Windows
pip install -e .