MySQL MCP Server: Seamless Integration of AI Applications with Databases

Overview: What is MySQL MCP Server?

The MySQL MCP Server acts as an intermediary between Model Context Protocol (MCP) clients, such as AI applications like Claude Desktop, Continue, and Cursor, and the MySQL database system. It enables these sophisticated AI tools to interact with relational databases through standardized protocols, enhancing their capabilities for data processing and analysis.

🔧 Core Features & MCP Capabilities

This server provides essential functionalities including read-only queries (query), write operations that are ultimately rolled back (test_execute), listing tables, and describing table structures. These features ensure that AI applications can perform various database operations while maintaining the safety and integrity of data through rollback mechanisms.

Detailed Functionality:

1. Execute Read-Only Query:

   • MCP Command: query
   • Payload Example:

     {
       "type": "query",
       "payload": {
         "sql": "SELECT * FROM your_table"
       }
     }
     

   • Response Example:

     {
       "success": true,
       "data": [
         {"id": 1, "name": "example"}
       ]
     }
     

2. Test Query Execution (Write Operation):

   • MCP Command: test_execute
   • Payload Example:

     {
       "type": "test_execute",
       "payload": {
         "sql": "UPDATE your_table SET name = 'updated' WHERE id = 1"
       }
     }
     

   • Response Example:

     {
       "success": true,
       "data": "The UPDATE SQL query can be executed."
     }
     

3. List Tables:

   • MCP Command: list_tables
   • Payload Example (No payload required):

     {}
     

   • Response Example:

     {
       "success": true,
       "data": ["table1", "table2", "table3"]
     }
     

4. Describe Table:

   • MCP Command: describe_table
   • Payload Example:

     {
       "type": "describe_table",
       "payload": {
         "table": "your_table"
       }
     }
     

   • Response Example:

     {
       "success": true,
       "data": [
         {"Field": "id", "Type": "int(11)", "Null": "NO", "Key": "PRI", "Default": null, "Extra": ""},
         {"Field": "name", "Type": "varchar(255)", "Null": "YES", "Key": "", "Default": null, "Extra": ""}
       ]
     }
     

⚙️ MCP Architecture & Protocol Implementation

Implementation Details:

• Language: TypeScript
• Packages:
  • mysql2: A package for interacting with MySQL databases.
• Execution Environment: Runs as a Docker container to facilitate seamless integration and portability.
• Communication: Accepts JSON commands via standard input and outputs JSON responses through standard output, ensuring compatibility with MCP clients.

Security Considerations:

• Environment Variables: Sensitive information (e.g., database credentials) is managed via environment variables for security purposes.
• SQL Injection Prevention: Implementors must ensure proper handling of SQL queries to prevent injection attacks.
• Network Configuration: Appropriate network settings and firewalls are required for production use.

🚀 Getting Started with Installation

To set up the MySQL MCP Server, follow these steps:

Environment Variables Setup

Create or modify your environment variables file ~/.mcp/.env as follows:

MYSQL_HOST=host.docker.internal  # Hostname to access host services from Docker container
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=your_password

Note: Use host.docker.internal for accessing a MySQL server running on your host. Adjust if connecting to a different database.

MCP Configuration

Edit the mcp.json file with the following configuration:

{
  "mcpServers": {
    "mysql": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "--add-host=host.docker.internal:host-gateway",
        "--env-file",
        "/Users/username/.mcp/.env",
        "ghcr.io/xiangma9712/mcp/mysql"
      ]
    }
  }
}

Starting the Server

To start the server, run the following command:

docker run -i --rm --add-host=host.docker.internal:host-gateway --env-file ~/.mcp/.env ghcr.io/xiangma9712/mcp/mysql

For OrbStack users, the --add-host option is automatically supported.

💡 Key Use Cases in AI Workflows

Use Case 1: Data Aggregation for AI Training

Imagine an AI model that requires regular updates of a large dataset from a MySQL database. By integrating with the MySQL MCP Server, the AI application can periodically execute query and list_tables commands to fetch and analyze the latest data.

Use Case 2: Real-time Monitoring & Alerting

A real-time monitoring system can use the test_execute command to simulate write operations before they are committed. If these operations pass validation checks, they are executed. This ensures that any data changes in the AI application are safely reflected in the database without causing disruptions.

🔌 Integration with MCP Clients

The MySQL MCP Server supports integration with major MCP clients, including:

• Claude Desktop
• Continue
• Cursor

See the compatibility matrix for detailed support status across these platforms.

Compatibility Matrix:

MCP Client	Resources	Tools	Prompts	Status
Claude Desktop	✅	✅	✅	Full Support
Continue	✅	✅	✅	Full Support
Cursor	❌	✅	❌	Tools Only

📊 Performance & Compatibility Matrix

Given that the server is implemented in TypeScript and Docker, it ensures high performance and compatibility across a wide range of Linux distributions.

Example Configuration Code Sample:

{
  "mcpServers": {
    "mysql": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "--add-host=host.docker.internal:host-gateway",
        "--env-file",
        "/Users/username/.mcp/.env",
        "ghcr.io/xiangma9712/mcp/mysql"
      ]
    }
  }
}

🛠️ Advanced Configuration & Security

MCP Protocol Flow Diagram:

graph TD
    A[AI Application] -->|MCP Client| B[MCP Protocol]
    B --> C[MCP Server]
    C --> D[Data Source/Tool]
    style A fill:#e1f5fe
    style C fill:#f3e5f5
    style D fill:#e8f5e8

Security Considerations:

• Environment Variables: Use secure environment variable management for sensitive information.
• SQL Injection Prevention: Ensure proper validation and sanitization of SQL queries.
• Firewall Settings: Configure appropriate network settings for both development and production environments.

❓ Frequently Asked Questions (FAQ)

1. Q: How do I install the MySQL MCP Server?

   • A: Follow the steps mentioned in the "Getting Started" section to set up the server via Docker with environment variables configured.

2. Q: Can this server be used with OrbStack or other systems?

   • A: Yes, though host.docker.internal is automatically supported by OrbStack.

3. Q: Is there any limit on the SQL queries executed through MCP clients?

   • A: There are no hardcoded limits; however, performance and capacity are dependent on MySQL database configuration.

4. Q: How does this server handle security concerns like SQL injection?

   • A: Proper validation and sanitization of all incoming SQL commands are crucial to prevent SQL injection attacks.

5. Q: What is the recommended network configuration for production use?

   • A: Use appropriate firewalls and network settings to ensure secure access and performance optimization.

👨‍💻 Development & Contribution Guidelines

Contributions are welcome! To contribute, follow these steps:

1. Fork the repository.
2. Implement new features or bug fixes.
3. Ensure tests pass with npm test.
4. Push changes to your fork and create a pull request.

🌐 MCP Ecosystem & Resources

Explore more about the Model Context Protocol (MCP) on its official website or documentation for further integration insights and resource guidance.

By integrating the MySQL MCP Server, developers can significantly enhance their AI applications' database interactions, ensuring secure, efficient, and scalable data processing capabilities.