mysql-mcp-server

hightower9/mysql-mcp-server

3.2

If you are the rightful owner of mysql-mcp-server and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to dayong@mcphub.com.

A production-grade Model Context Protocol (MCP) server for MySQL database interactions, enabling AI assistants to safely and efficiently interact with MySQL databases.

Tools
6
Resources
0
Prompts
0

MySQL MCP Server

A production-grade Model Context Protocol (MCP) server for MySQL database interactions. This server enables AI assistants like Claude to safely and efficiently interact with MySQL databases through the MCP protocol.

Features

✨ Production Ready

  • Connection pooling with configurable settings
  • Comprehensive error handling and logging
  • SQL injection prevention and query validation
  • Configurable security controls (read-only, write, DDL)

🔒 Security First

  • Query validation and sanitization
  • Parameterized queries
  • Configurable operation restrictions
  • Maximum result set limits
  • Query timeout enforcement

🛠️ MCP Capabilities

  • Tools: Execute queries, list tables, describe schemas, search tables, database statistics
  • Resources: Access database schemas, table structures, and data previews
  • Prompts: Guided table analysis, query generation, optimization, and migration planning

Installation

Prerequisites

  • Python 3.10 or higher
  • MySQL 5.7 or higher (or MariaDB 10.2+)
  • pip or uv package manager

Setup

  1. Clone the repository:
git clone <repository-url>
cd mysql-mcp-server
  1. Create and activate a virtual environment:
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
  1. Install dependencies:
pip install -e .
# Or for development:
pip install -e ".[dev]"
  1. Configure environment variables:
cp .env.example .env
# Edit .env with your MySQL credentials

Configuration

Edit the .env file with your settings:

# Required
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database

# Optional Security Settings
ENABLE_READ_ONLY=false
ENABLE_WRITE_OPERATIONS=true
ENABLE_DDL_OPERATIONS=false
MAX_QUERY_RESULTS=1000
QUERY_TIMEOUT=30

Security Configuration

  • ENABLE_READ_ONLY: Restrict to SELECT queries only
  • ENABLE_WRITE_OPERATIONS: Allow INSERT, UPDATE, DELETE operations
  • ENABLE_DDL_OPERATIONS: Allow CREATE, ALTER, DROP, TRUNCATE operations
  • MAX_QUERY_RESULTS: Maximum rows returned per query
  • QUERY_TIMEOUT: Maximum query execution time in seconds

Usage

Starting the Server

# Using the installed command
mysql-mcp-server

# Or directly with uv
uv run python -m server.main

# Or directly with Python
python -m server.main

Using with Claude Desktop

Add to your Claude Desktop configuration file:

MacOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "mysql": {
      "command": "uv",
      "args": ["run", "python", "-m", "server.main"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Available Tools

1. execute_query

Execute SQL queries on the database:

# SELECT query
execute_query("SELECT * FROM users WHERE age > 25 LIMIT 10")

# INSERT query
execute_query("INSERT INTO users (name, email) VALUES ('John', 'john@example.com')")

# UPDATE query
execute_query("UPDATE users SET status = 'active' WHERE id = 1")
2. list_tables

Get all tables in the database:

list_tables()
# Returns: {"success": true, "tables": ["users", "orders", "products"], "count": 3}
3. describe_table

Get detailed schema for a table:

describe_table("users")
# Returns full schema including columns, types, constraints, indexes
4. get_table_info

Get summary information about a table:

get_table_info("products")
# Returns: row count, column count, column names, primary key info
5. search_tables

Search for tables by name:

search_tables("user")
# Returns tables containing "user" in the name
6. get_database_stats

Get overall database statistics:

get_database_stats()
# Returns: table count, largest tables, configuration settings

Available Resources

Schema Resource
mysql://schema

Returns the complete database schema.

Table Schema Resource
mysql://tables/{table_name}

Returns schema for a specific table.

Table Preview Resource
mysql://tables/{table_name}/preview

Returns first 10 rows of a table.

Available Prompts

analyze_table

Generate analysis of a table structure and data.

generate_query

Create SQL queries from natural language descriptions.

optimize_query

Get optimization suggestions for SQL queries.

database_migration

Plan database schema migrations.

Project Structure

mysql-mcp-server/
├── server/
│   ├── __init__.py           # Package initialization
│   ├── main.py               # FastAPI application entry point
│   ├── config/
│   │   ├── __init__.py       # Config package init
│   │   ├── settings.py       # Configuration management
│   │   └── database.py       # Database operations
│   ├── core/
│   │   ├── __init__.py       # Core package init
│   │   ├── prompts.py        # MCP prompt definitions
│   │   ├── resources.py      # MCP resource definitions
│   │   └── tools.py          # MCP tool definitions
│   └── utils/
│       ├── __init__.py       # Utils package init
│       └── security.py       # Security validation
├── .env.example              # Example environment variables
├── .gitignore                # Git ignore rules
├── pyproject.toml            # Project metadata and dependencies
└── README.md                 # Project README

Security Best Practices

  1. Never commit .env file - Contains sensitive credentials
  2. Use read-only mode for analysis tasks
  3. Disable DDL operations unless explicitly needed
  4. Set appropriate query timeouts to prevent long-running queries
  5. Limit max results to prevent memory issues
  6. Regular security audits of query logs

Troubleshooting

Connection Issues

Error: Failed to connect to database
  • Verify MySQL is running
  • Check host, port, username, password in .env
  • Ensure database exists
  • Check firewall settings

Permission Errors

Error: Access denied for user
  • Grant appropriate MySQL privileges:
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'host';
FLUSH PRIVILEGES;

Query Validation Errors

Error: Dangerous keyword detected
  • The security validator blocks potentially harmful operations
  • Review security.py for allowed operations
  • Adjust ENABLE_DDL_OPERATIONS if needed

Performance Optimization

  1. Connection Pooling: Adjust POOL_SIZE based on load
  2. Query Limits: Set MAX_QUERY_RESULTS appropriately
  3. Indexes: Ensure proper indexes on frequently queried columns
  4. Pool Recycling: Adjust POOL_RECYCLE for long-running servers

License

MIT License

Contributing

Contributions are welcome! Please:

  1. Fork the repository
  2. Create a feature branch
  3. Add tests for new functionality
  4. Ensure all tests pass
  5. Submit a pull request

Support

For issues and questions:

Changelog

Version 1.0.0

  • Initial production release
  • Complete MCP implementation
  • Security validation
  • Connection pooling
  • Comprehensive documentation