# MCP MySQL Ops

> 🛠️ MCP server for MySQL ops — 19+ tools for monitoring, diagnostics & performance analysis. Natural language queries, read-only & production-safe, MySQL 5.7.9+/8.0+. Works with Claude, Cursor, Cline & all MCP clients.

- **Type:** MCP server
- **Install:** `agentstack add mcp-call518-mcp-mysql-ops`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [call518](https://agentstack.voostack.com/s/call518)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [call518](https://github.com/call518)
- **Source:** https://github.com/call518/MCP-MySQL-Ops
- **Website:** https://deepwiki.com/call518/MCP-MySQL-Ops

## Install

```sh
agentstack add mcp-call518-mcp-mysql-ops
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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 MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, 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.

```bash
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):**
```bash
#### 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:**
```bash
# 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

```bash
# 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:

```bash
# 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):**
```bash
# 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:**
```bash
# 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:**
```bash
# 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
1. go to "Settings" → "Tools" from the top menu.
1. Enter the `mysql-ops` Tool address (e.g., `http://localhost:8004/mysql-ops`) to connect MCP Tools.
1. 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:**

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

**Or via CLI:**

```bash
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:

```json
{
  "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:

```bash
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:**
```bash
# 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)
```bash
# 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
```bash
# 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.

```bash
# 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 |
|----------|---------|--------|-------|
| **test_ecommerce** | E-commerce system | categories, products, customers, orders, order_items | 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](https://github.com/call518)
- **Source:** [call518/MCP-MySQL-Ops](https://github.com/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.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** yes
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/mcp-call518-mcp-mysql-ops
- Seller: https://agentstack.voostack.com/s/call518
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.