mcp-mysql-server

A simple Python script for interacting with a local MySQL database via standard input/output (stdio), based on FastMCP.

Overview

mcp-mysql-server provides a command-line interface for querying and inspecting the schema of a local MySQL database. It is designed for ease of use in local development environments where a lightweight, script-based interaction is preferred. The script utilizes standard input and output for commands and results, making it easy to integrate with other command-line tools and workflows.

Features

• Query Execution: Execute SQL SELECT statements and view the results in a simple, readable format.
• Data Manipulation: Insert data into tables with the write_table tool.
• Schema Inspection: Retrieve information about the database schema, including:
  • Listing all tables with the list_tables tool.
  • Displaying the columns and their data types for a specific table with get_table_schema.
• Connection Testing: Test database connectivity and get server information.
• Configurable Access Controls: Define connection parameters (host, user, password, database) through configuration, allowing secure access to the database.
• stdio Interface: Interact with the script using standard input for commands and receive output via standard output, facilitating scripting and automation.
• Local Deployment Focus: Optimized for use within local development environments.

Getting Started

Prerequisites

• Python 3.x installed on your system.
• A local MySQL server instance running.
• Required Python libraries (install using the requirements.txt file):

  pip install -r requirements.txt
  

Installation

1. Download or clone the repository to your local machine.

Configuration

1. Create a configuration file (e.g., config.ini) in the same directory as the script or specify the path when running the script. The configuration file should have the following format:

   [mysql]
   host = localhost
   port = 3306  # Optional, defaults to 3306
   user = your_mysql_user
   password = your_mysql_password
   database = your_database_name
   

   Note: Ensure the MySQL user specified has the necessary privileges to perform the actions you intend to execute.

Usage

Run the script with the following command, specifying the path to your configuration file:

python mcp-mysql-server.py --config config.ini

The script exposes the following tools that you can use to interact with your MySQL database:

• test_connection: Tests the connection to the database and returns server information.
• list_tables: Lists all tables in the current database.
• read_table: Reads and returns all data from a specified table.
• write_table: Inserts a new row of data into a specified table.
• get_table_schema: Returns the schema (column definitions) for a specified table.
• execute_sql: Executes a custom SQL query and returns the results.

Project Structure

• mcp-mysql-server.py: Main script containing the MySQL MCP server implementation
• config.ini: Configuration file for MySQL connection parameters
• requirements.txt: List of Python dependencies
• changes.log: Log of changes made to the project

Docker Support

This project can be run in Docker containers using Docker Compose. The setup includes:

1. A MySQL container with sample data
2. The MCP MySQL Server container

Running with Docker

1. Make sure you have Docker and Docker Compose installed on your system.
2. Clone this repository.
3. From the repository root, run:

docker-compose up -d

This will:

• Start a MySQL server with sample data (users and posts tables)
• Start the MCP MySQL Server connected to the MySQL database

Configuration for Docker

A special configuration file (config.docker.ini) is used when running in Docker, which points to the MySQL container.

Testing

Unit tests are provided to ensure the functionality works correctly. The tests use mocking to avoid requiring an actual database connection.

Running Tests Locally

# Make a copy of the main script with underscore naming
cp mcp-mysql-server.py mcp_mysql_server.py

# Run the tests
python -m unittest tests.py

Running Tests with Docker

docker-compose -f docker-compose.test.yml up --build

This will build a test container and run the unit tests within it.

Development

For developers who want to extend this tool, you can add new functionalities by creating additional MCP tools. Each tool is a Python function decorated with @mcp.tool() that handles a specific database operation.

Example of adding a new tool:

@mcp.tool()
def my_new_tool(param1: str, param2: int) -> dict:
    """
    Documentation for the new tool.
    """
    connection = create_mysql_connection()
    cursor = connection.cursor()
    try:
        # Your tool implementation here
        return {"result": "success"}
    finally:
        cursor.close()
        connection.close()