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

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

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

Index Tools

Health Tools

Storage Engine Tools

InnoDB Tools

Memory Tools

Replication Tools

Security Tools

Statement Analysis Tools

Available Prompts

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 .