dvalenza/Generic-MySQL-MCP-Server

MySQL MCP Server

A Model Context Protocol (MCP) server that provides tools for connecting to and querying generic MySQL databases. This server allows AI assistants to interact with MySQL databases through a standardized interface.

Features

• Database Connection Management: Secure connection pooling with configurable timeouts
• Query Execution: Support for SELECT, INSERT, UPDATE, DELETE operations
• Schema Inspection: Tools to explore database structure and table schemas
• Connection Testing: Built-in connection health checks
• Query Explanation: EXPLAIN query support for performance analysis
• Security: Parameterized queries to prevent SQL injection

Available Tools

1. query_database

Execute SELECT queries on the MySQL database.

• Input: SQL SELECT query and optional parameters
• Output: JSON formatted query results

2. update_database

Execute INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP queries.

• Input: SQL modification query and optional parameters
• Output: Number of affected rows

3. describe_table

Get detailed schema information for a specific table.

• Input: Table name
• Output: Column definitions, data types, constraints

4. list_tables

Retrieve all table names in the current database.

• Output: Array of table names

5. list_databases

Get all databases available on the MySQL server.

• Output: Array of database names

6. test_connection

Verify the database connection is working.

• Output: Connection status

7. explain_query

Get the execution plan for a SELECT query.

• Input: SQL SELECT query and optional parameters
• Output: Query execution plan details

Installation

1. Clone or create the project directory:

   mkdir mysql-mcp-server
   cd mysql-mcp-server
   

2. Install dependencies:

   pip install -r requirements.txt
   

   Or using the development installation:

   pip install -e .
   

3. Set up environment variables:

   cp .env.example .env
   

   Edit .env with your MySQL database credentials:

   DB_HOST=localhost
   DB_PORT=3306
   DB_USER=your_username
   DB_PASSWORD=your_password
   DB_NAME=your_database_name
   

Configuration

The server uses environment variables for configuration. All settings can be customized in your .env file:

Database Settings

• DB_HOST: MySQL server hostname (default: localhost)
• DB_PORT: MySQL server port (default: 3306)
• DB_USER: MySQL username (required)
• DB_PASSWORD: MySQL password (required)
• DB_NAME: Default database name (optional)
• DB_CHARSET: Character set (default: utf8mb4)

Connection Pool Settings

• DB_POOL_SIZE: Number of connections in pool (default: 10)
• DB_MAX_OVERFLOW: Maximum pool overflow (default: 20)

Timeout Settings

• DB_CONNECT_TIMEOUT: Connection timeout in seconds (default: 10)
• DB_READ_TIMEOUT: Read timeout in seconds (default: 30)
• DB_WRITE_TIMEOUT: Write timeout in seconds (default: 30)

Usage

Running the Server

To run the MCP server:

python -m mysql_mcp_server.main

Using with MCP Clients

This server implements the Model Context Protocol and can be used with any MCP-compatible client. The server communicates over stdio and provides the tools listed above.

Example Queries

Query data:

SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10

Insert data:

INSERT INTO users (name, email, age) VALUES (%(name)s, %(email)s, %(age)s)

Update data:

UPDATE users SET last_login = NOW() WHERE id = %(user_id)s

Get table structure: Use the describe_table tool with table name "users"

Security Considerations

• Parameterized Queries: All queries support parameterization to prevent SQL injection
• Query Validation: Basic validation ensures appropriate query types for each tool
• Connection Pooling: Efficient connection management with automatic cleanup
• Error Handling: Comprehensive error handling and logging

Development

Project Structure

mysql_mcp_server/
├── __init__.py          # Package initialization
├── main.py             # MCP server implementation
├── database.py         # Database connection and operations
└── config.py           # Configuration management

Running Tests

pytest tests/

Code Formatting

black mysql_mcp_server/
ruff mysql_mcp_server/

Requirements

• Python 3.8+
• MySQL 5.7+ or MariaDB 10.3+
• Required Python packages (see requirements.txt):
  • mcp>=1.0.0
  • PyMySQL>=1.1.0
  • python-dotenv>=1.0.0
  • pydantic>=2.0.0

License

MIT License - see LICENSE file for details.

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

Troubleshooting

Connection Issues:

• Verify MySQL server is running and accessible
• Check database credentials in .env file
• Ensure MySQL user has appropriate permissions
• Test connection using the test_connection tool

Permission Issues:

• Ensure MySQL user has SELECT, INSERT, UPDATE, DELETE privileges
• For schema inspection, user needs access to INFORMATION_SCHEMA

Performance Issues:

• Adjust connection pool settings in configuration
• Use EXPLAIN tool to analyze query performance
• Consider adding database indexes for frequently queried columns