MySQL MCP Server Documentation

Overview

This is a production-ready MySQL MCP (Model Context Protocol) server that provides secure database access through MCP tools. The server includes authentication, monitoring, and comprehensive database operations.

Quick Start

Prerequisites

• Python 3.8+
• MySQL database server
• Claude Desktop application
• Git

Installation

1. Clone and navigate to the project:

cd C:\Users\MCP-Procuction_MySQL

2. Install dependencies:

pip install -r requirements.txt

3. Set up environment variables (create .env file):

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

# Security
SECRET_KEY=your-secret-key-here
ALLOWED_OPERATIONS=SELECT,INSERT,UPDATE,DELETE

# Optional: GitHub OAuth
GITHUB_CLIENT_ID=your_github_client_id
GITHUB_CLIENT_SECRET=your_github_client_secret

# Optional: Monitoring
SENTRY_DSN=your_sentry_dsn

Running the Server

Primary method:

python -m src.main

Alternative methods:

# Using run script
python run_server.py

# Standalone mode
python mcp_standalone.py

Claude Desktop Integration

MCP Configuration for Claude Desktop

Add this configuration to your Claude Desktop MCP settings file. The configuration file is typically located at:

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

{
  "mcpServers": {
    "mysql-mcp": {
      "command": "python",
      "args": ["src/server.py"],
      "cwd": "/path/to/your/MYSQL-Production"
    }
  }
}

Setup Steps for Claude Desktop

1. Install the MCP server (follow installation steps above)
2. Locate your Claude Desktop config file using the paths above
3. Add the MCP configuration to the config file
4. Update the cwd path to match your actual installation directory
5. Set up environment variables in a .env file in your project directory
6. Restart Claude Desktop to load the new MCP server
7. Verify connection by asking Claude to list database tables

Configuration Notes

• Replace your_username, your_password, etc. with your actual database credentials
• Update the cwd path to match your actual installation directory
• Ensure Python is available in your system PATH
• The server will be available as "mysql-mcp" in Claude Desktop

Testing Claude Integration

Once configured, you can test the integration by asking Claude:

• "List all tables in my database"
• "Show me the structure of the users table"
• "Query the first 5 rows from my products table"

Project Structure

MYSQL-Production/
├── src/
│   ├── main.py              # Main MCP server entry point
│   ├── server.py            # Core MCP server implementation
│   ├── config.py            # Configuration management
│   ├── models.py            # Data models
│   ├── auth/                # Authentication modules
│   │   ├── github_oauth.py  # GitHub OAuth integration
│   │   └── session.py       # Session management
│   ├── database/            # Database modules
│   │   ├── connection.py    # Database connection handling
│   │   ├── security.py      # Security and validation
│   │   └── utils.py         # Database utilities
│   ├── tools/               # MCP tools implementation
│   │   ├── basic_tools.py   # Basic database operations
│   │   ├── advanced_tools.py# Advanced database features
│   │   ├── write_tools.py   # Write operations
│   │   └── transaction_tools.py # Transaction management
│   └── monitoring/          # Monitoring and logging
│       └── sentry.py        # Sentry integration
├── tests/                   # Test suite
├── requirements.txt         # Python dependencies
├── pyproject.toml          # Project configuration
├── docker-compose.yml      # Docker setup
└── README.md               # Basic documentation

Available MCP Tools

Basic Operations

• mysql-mcp:query_database - Execute SELECT queries
• mysql-mcp:list_tables - List all database tables
• mysql-mcp:describe_table - Get table schema information

Write Operations

• mysql-mcp:execute_sql - Execute INSERT, UPDATE, DELETE operations
• mysql-mcp:create_table - Create new tables

Advanced Features

• Transaction management
• Query optimization
• Performance monitoring
• Security validation

Configuration

Environment Variables

| Variable | Description | Required | Default | |----------|-------------|----------|---------| | MYSQL_HOST | MySQL server host | Yes | localhost | | MYSQL_PORT | MySQL server port | No | 3306 | | MYSQL_USER | Database username | Yes | - | | MYSQL_PASSWORD | Database password | Yes | - | | MYSQL_DATABASE | Database name | Yes | - | | SECRET_KEY | Security secret key | Yes | - | | ALLOWED_OPERATIONS | Permitted SQL operations | No | SELECT,INSERT,UPDATE,DELETE | | MAX_CONNECTIONS | Connection pool size | No | 10 | | QUERY_TIMEOUT | Query timeout (seconds) | No | 30 |

Security Features

• SQL injection prevention
• Query validation and sanitization
• Operation restrictions
• Connection pooling with limits
• Session-based authentication
• Optional GitHub OAuth integration

Usage Examples

Basic Query

# Using MCP client
result = await client.call_tool("mysql-mcp:query_database", {
    "sql": "SELECT * FROM users LIMIT 10"
})

Table Operations

# List tables
tables = await client.call_tool("mysql-mcp:list_tables", {})

# Describe table structure
schema = await client.call_tool("mysql-mcp:describe_table", {
    "table": "users"
})

Data Modification

# Insert data
result = await client.call_tool("mysql-mcp:execute_sql", {
    "sql": "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')"
})

Testing

Run the test suite:

# Run all tests
python -m pytest tests/

# Run specific test
python test_mcp.py

Docker Deployment

Use the provided Docker setup:

# Start services
docker-compose up -d

# View logs
docker-compose logs -f

Monitoring and Debugging

Logging

The server includes structured logging with different levels:

• INFO: General operations
• WARNING: Potential issues
• ERROR: Operation failures
• DEBUG: Detailed debugging info

Sentry Integration

Configure Sentry for error tracking:

SENTRY_DSN=your_sentry_dsn
SENTRY_ENVIRONMENT=production

Debug Mode

Enable debug mode in debug_settings.py:

DEBUG = True
LOG_LEVEL = "DEBUG"

Troubleshooting

Common Issues

1. Connection Errors

   • Verify MySQL server is running
   • Check credentials in .env file
   • Ensure network connectivity

2. Permission Errors

   • Verify database user permissions
   • Check ALLOWED_OPERATIONS configuration

3. Performance Issues

   • Monitor connection pool usage
   • Check query execution times
   • Review database indexes

Debug Commands

# Test database connection
python -c "from src.database.connection import test_connection; test_connection()"

# Check MCP server status
python -c "from src.main import main; main()"

Development

Code Style

The project uses:

• Black for code formatting
• Ruff for linting
• MyPy for type checking

# Format code
black src/

# Run linting
ruff check src/

# Type checking
mypy src/

Adding New Tools

1. Create tool function in appropriate module
2. Register in src/tools/register_tools.py
3. Add tests in tests/
4. Update documentation

Production Deployment

See DEPLOYMENT.md for detailed production deployment instructions including:

• Environment setup
• Security hardening
• Performance optimization
• Monitoring configuration

Security Considerations

• Always use parameterized queries
• Implement proper authentication
• Regularly update dependencies
• Monitor for suspicious activity
• Use SSL/TLS for connections
• Implement rate limiting

Support

For issues and questions:

1. Check the troubleshooting section
2. Review logs for error details
3. Consult the existing README.md and DEPLOYMENT.md files
4. Check the test files for usage examples