MySQL MCP Server

The MySQL Model Context Protocol (MCP) server is a tool that connects to a local MySQL database, enabling large language models (LLMs) to execute SQL queries.

Requirements

• Node.js: 20.0.0 or higher
• MySQL: MySQL or MariaDB server version 5.7 or higher

Features

• Execution of MySQL Queries: Directly execute SQL queries from LLMs
• Database Information Retrieval: Obtain a list of databases, tables, and table structures
• MCP Compliance: Complies with the Model Context Protocol, allowing integration with LLMs
• stdio Communication: Communicates with LLMs using standard input and output, without binding to a port
• Connection Profile Management: Manage multiple connection settings by profile name and switch between them
• Saving Connection Information: Save database connection information locally for reuse

Installation and Usage

Temporary Execution with NPX

bash
npx -y https://github.com/yuki777/mysql-mcp-server --host 127.0.0.1 --port 13306 --user root

Options

Saving and Reusing Connection Information

MySQL MCP Server saves the information of successfully connected databases as named profiles locally. This allows you to reuse the connection information by specifying the profile name on the next startup. The saved connection information is stored in the .mysql-mcp-connections.json file in the user's home directory.

Each connection profile includes:

• Profile name
• Hostname
• Port number
• Username
• Password
• Database name (if set)

You can manage multiple database connections by profile name and easily switch between them.

Using Configuration Files

You can also set up connection information using a configuration file (in JSON format):

json
{
"server": {
"port": 3000,
"host": "localhost"
},
"mysql": {
"host": "localhost",
"port": 13306,
"user": "root",
"password": "yourpassword",
"database": "mydb"
},
"debug": false,
"queryTimeout": 30000,
"maxResultSize": 1000
}

To use a configuration file:

bash
npx -y https://github.com/yuki777/mysql-mcp-server -c ./mysql-mcp-config.json

Communication Method

MySQL MCP Server operates in "stdio" mode, which complies with the Model Context Protocol (MCP). This allows it to communicate through standard input and output without binding to a specific port. The benefits include:

1. Avoiding Port Conflicts: Since no specific port is used, there are no port conflict issues.
2. Enhanced Security: Not using network communication reduces the risk of network-level attacks.
3. Simplified Inter-Process Communication: Simplifies communication with LLMs.

Notes- In stdio mode, messages are exchanged in JSON format.

• One JSON message must be sent per line.
• Error information and connection logs are output to standard error (stderr).

Provided MCP Tools

Database Connection Management

Connection Profile Management

SQL Query Operations

Connection Management Features

The MySQL MCP Server allows for the separation of server startup and database connections. This approach offers the following benefits:

1. Startup without Connection Information: The server can start even without database connection information.
2. Connection to Multiple Databases: After the server starts, it is possible to switch connections to different databases.
3. Simple Installation: Can be executed with just npx -y https://github.com/yuki777/mysql-mcp-server.

How to Use Connection Management

1. Start the server without automatic connection:
   bash
   npx -y https://github.com/yuki777/mysql-mcp-server

2. Connect to the database using the connection tool (specify and save the profile name):
   json
   {
   "type": "tool_call",
   "request_id": "req_1",
   "tool": "connect_database",
   "arguments": {
   "host": "localhost",
   "port": 3306,
   "user": "root",
   "password": "your_password",
   "database": "your_db",
   "profileName": "my-db"
   }
   }

3. Retrieve the list of profiles:
   json
   {
   "type": "tool_call",
   "request_id": "req_2",
   "tool": "list_profiles",
   "arguments": {}
   }

4. Connect by profile name:
   json
   {
   "type": "tool_call",
   "request_id": "req_3",
   "tool": "connect_by_profile",
   "arguments": {
   "profileName": "my-db"
   }
   }

5. Add a new profile (without connecting):
   json
   {
   "type": "tool_call",
   "request_id": "req_4",
   "tool": "add_profile",
   "arguments": {
   "profileName": "production-db",
   "host": "prod.example.com",
   "port": 3306,
   "user": "prod_user",
   "password": "prod_password",
   "database": "production"
   }
   }

6. Check the connection status:
   json
   {
   "type": "tool_call",
   "request_id": "req_5",
   "tool": "get_connection_status",
   "arguments": {}
   }

7. Disconnect from the database:
   json
   {
   "type": "tool_call",
   "request_id": "req_6",
   "tool": "disconnect_database",
   "arguments": {}
   }

Test ScriptThe repository includes a test script named test-connection-management.js. You can use this script to test the connection management features:

bash
node test-connection-management.js

Information for Developers

Setting Up the Development Environment

bash

Clone the repository

git clone [repository-url]
cd mysql-mcp-server

Install dependencies

npm install

Run in development mode

npm run dev

Building

bash
npm run build

License

ISC

Contributions

We welcome bug reports, feature requests, and pull requests.