MCP MySQL Server

This is a Node.js-based MCP (Managed Connector Platform) server for MySQL, exposing functionalities to interact with a MySQL database.

Features

query: Executes a SQL query with optional parameters.
describe_table: Describes the schema of a specified table.
list_tables: Lists all tables in the connected database.
update: Performs update operations on a table.
insert: Inserts a single record into a table.
delete: Deletes records from a table based on a condition (implemented by deleteSingle internally).

Setup and Configuration

Prerequisites

Node.js (LTS version recommended)
npm (Node Package Manager)
MySQL Server running and accessible
Express.js (included as a dependency)

Installation

Clone the repository (or create the project structure manually):
```
git clone <repository-url>
cd mcp-mysql
```
If you are setting this up manually, ensure you have package.json and index.js as provided.
Install dependencies:

Navigate to the project directory and run:
```
npm install
```

Configuration

Create a .env file:

In the root directory of the project, create a file named .env.
Add database credentials to .env:

Populate the .env file with your MySQL database connection details:
```
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=password
DB_DATABASE=mysql_db
```
Note: Replace localhost, 3306, root, password, mysql_db, and your_mysql_port with your actual database host, port, username, password, database name, and desired server port respectively.

MCP JSON Configuration for IDEs (e.g., Cursor AI, VS Code)

To allow IDEs to automatically discover and interact with your MCP MySQL server, you can add the following JSON configuration to your IDE's .cursor/mcp.json file.

{
  "mcpServers": {
    "mcp_mysql_dheer": {
      "command": "node",
      "args": ["index.js"],
      "env": {
        "DB_HOST": "your_db_host",
        "DB_PORT": "your_db_port",
        "DB_USER": "your_db_user",
        "DB_PASSWORD": "your_db_pwd",
        "DB_DATABASE": "your_db"
      }
    }
  }
}

Docker Usage

This MCP server runs as a stdio application. Below are the instructions to build a Docker image and run the server within a container.

Build the Docker Image:

Navigate to the project directory containing the Dockerfile and package.json, then run:
```
docker build -t mcp_mysql_dheer .
```
Run the Docker Container (as a stdio server):

When running the Docker container, you'll need to provide the MySQL database credentials as environment variables.
```
docker run -i mcp_mysql_dheer node index.js \
  -e DB_HOST=your_mysql_host \
  -e DB_USER=your_mysql_user \
  -e DB_PASSWORD=your_mysql_password \
  -e DB_DATABASE=your_mysql_database \
  -e DB_PORT=your_mysql_port
```
Note:
- -i flag is crucial for STDIN to remain open, allowing the MCP client to communicate with the server.
- Replace your_mysql_host, your_mysql_user, your_mysql_password, your_mysql_database, and your_mysql_port with your actual database connection details.

Usage (Example with a hypothetical MCP Client)

This server is designed to be consumed by an MCP client. Here's a conceptual example of how an MCP client might interact with these functionalities:

const client = new MCPClient({
  server: {
    command: 'docker',
    args: [
      'run',
      '-i',
      '--rm',
      'mcp_mysql_dheer',
      'node',
      'index.js',
      '-e', 'DB_HOST=your_mysql_host',
      '-e', 'DB_USER=your_mysql_user',
      '-e', 'DB_PASSWORD=your_mysql_password',
      '-e', 'DB_DATABASE=your_mysql_database',
      '-e', 'DB_PORT=your_mysql_port'
    ],
  },
});

async function exampleUsage() {
  // Example: List tables
  const tables = await client.callTool('list_tables', {});
  console.log('Tables:', tables);

  // Example: Query data
  const users = await client.callTool('query', { sql: 'SELECT * FROM users' });
  console.log('Users:', users);
}

exampleUsage();

mcp-mysql