# 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):
    ```bash
    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:

    ```ini
    [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:

```bash
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:

```bash
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

```bash
# 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

```bash
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:

```python
@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()
```