MySQL MCP Server

A proper Model Context Protocol (MCP) server that enables natural language interaction with MySQL databases.

Features

• MCP Protocol Compliance: Implements the official Model Context Protocol specification <!-- - Natural Language to SQL: Convert natural language queries to SQL using Llama 3.2 -->
• Direct SQL Execution: Execute raw SQL queries safely
• Database Schema Exploration: Explore database structure and table information
• MCP Tools: Expose database operations as MCP tools
• MCP Resources: Provide database schema and data as MCP resources
• MCP Prompts: Offer helpful prompts for database analysis
• Error Handling: Comprehensive error handling and logging

Prerequisites

• Python 3.10+
• MySQL Server
• Ollama running with Llama 3.2 model
• MCP-compatible client (Claude Desktop, Windsurf, etc.)

Setup

1. Install Dependencies

pip install -r requirements.txt

<!-- ### 2. Setup Ollama

Make sure Ollama is installed and running:

# Install Ollama (if not already installed)
curl -fsSL https://ollama.com/install.sh | sh

# Start Ollama service
ollama serve

# Pull Llama 3.2 model
ollama pull llama3.2
``` -->

### 2. Configure Environment

Copy the environment template and configure your database settings:

```bash
cp .env.example .env

Edit .env with your MySQL database configuration:

# MySQL Database Configuration
DB_HOST=localhost
DB_USER=your_mysql_user
DB_PASSWORD=your_mysql_password
DB_NAME=your_database_name
DB_PORT=3306

MCP Tools

The server exposes the following MCP tools:

execute_sql_query

Execute a SQL query and return the results.

• Parameters: query (string) - The SQL query to execute
• Returns: Formatted query results

<!-- ### natural_language_query Convert natural language to SQL and execute the query.

• Parameters: natural_query (string) - Natural language description of the query
• Returns: Query results after converting to SQL -->

list_tables

List all tables in the database.

• Parameters: None
• Returns: List of all tables

describe_table

Get detailed information about a specific table.

• Parameters: table_name (string) - Name of the table to describe
• Returns: Table structure and row count

get_table_data

Get sample data from a table.

• Parameters:
  • table_name (string) - Name of the table
  • limit (integer, optional) - Maximum rows to return (default: 10)
• Returns: Sample data from the table

MCP Resources

The server provides the following MCP resources:

schema://database

Get the complete database schema as a resource.

schema://tables/{table_name}

Get schema information for a specific table.

data://tables/{table_name}

Get sample data from a table as a resource.

MCP Prompts

The server offers the following MCP prompts:

sql_query_assistant

Generate a prompt for helping with SQL query creation.

• Parameters: query_description (string) - Description of what you want to query

database_analysis_task

Generate a prompt for database analysis tasks.

• Parameters: analysis_goal (string) - What you want to analyze in the database

Running the Server

Development Mode

Run the server in development mode with MCP Inspector:

uv run mcp dev mcp_server.py

Production Mode

Run the server with stdio transport:

python mcp_server.py

Integration with MCP Clients

Claude Desktop

1. Open Claude Desktop configuration file:

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

2. Add the server configuration:

{
  "mcpServers": {
    "mysql": {
      "command": "python",
      "args": ["/path/to/your/project/mcp_server.py"],
      "env": {
        "DB_HOST": "localhost",
        "DB_USER": "your_mysql_user",
        "DB_PASSWORD": "your_mysql_password",
        "DB_NAME": "your_database_name"
      }
    }
  }
}

3. Restart Claude Desktop

Windsurf Editor

1. Open MCP settings in Windsurf
2. Add a new MCP server with the following configuration:
   • Name: mysql
   • Command: python
   • Args: /path/to/your/project/mcp_server.py
   • Environment variables: Your database configuration

Usage Examples

<!-- ### Natural Language Queries

Once connected to an MCP client, you can use natural language:

"Show me all users from the users table"
"Find orders placed in the last 30 days"
"Count the number of products in each category"
``` -->

### Direct SQL Queries

"Execute: SELECT * FROM users WHERE created_at > '2024-01-01'" "Run: UPDATE products SET price = price * 1.1 WHERE category = 'electronics'"

### Database Exploration

"List all tables in the database" "Describe the structure of the orders table" "Show me sample data from the customers table"

## Testing

Use the provided test script to verify the server functionality:

```bash
python test_mcp_server.py

Troubleshooting

Common Issues

1. Database Connection Errors
   • Verify MySQL is running
   • Check database credentials in .env
   • Ensure the database exists

<!-- 2. Ollama Connection Issues

• Verify Ollama is running: ollama serve
• Check if Llama 3.2 is pulled: ollama list
• Verify Ollama URL is correct -->

2. MCP Server Not Detected
   • Check server configuration in client settings
   • Verify the server script path is correct
   • Check for syntax errors in the server code

Debug Mode

Enable debug logging by setting the log level:

LOG_LEVEL=DEBUG

Security Considerations

• Never expose your .env file in production
• Use database users with limited privileges
• Consider using connection pooling for production
• Validate all SQL queries to prevent injection attacks <!-- - Use HTTPS for Ollama connections in production -->

Contributing

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

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

For issues and questions:

• Check the troubleshooting section
• Review MCP documentation at https://modelcontextprotocol.io
• Open an issue in the project repository