Michael7736 Mysql MCP Server

🚀 MySQL MCP Server

This is a Model Context Protocol (MCP) server that provides access to MySQL databases. It allows agents to execute SQL queries against MySQL databases.

✨ Features

• Execute SQL queries on MySQL databases:
  • Read data (SELECT statements)
  • Create tables (CREATE TABLE statements)
  • Insert data (INSERT INTO statements)
  • Update data (UPDATE statements)
  • Delete data (DELETE FROM statements)
• Return query results in JSON format
• Configurable database connection settings
• Transaction logs with unique identifiers

📦 Prerequisites

• Node.js (v14 or higher)
• MySQL server
• MCP SDK

📦 Installation

1. Clone or download this repository.
2. Install the dependencies:

cd mysql-mcp-server
npm install

3. Build the server:

npm run build

🛠️ Configuration

The MySQL MCP server is configured using the following environment variables:

• MYSQL_HOST: MySQL server hostname (default: 'localhost')
• MYSQL_PORT: MySQL server port (default: 3306)
• MYSQL_USER: MySQL username (default: 'mcp101')
• MYSQL_PASSWORD: MySQL password (default: '123qwe')
• MYSQL_DATABASE: MySQL database name (default: 'mcpdb')

🛢️ Database Setup

1. Create a MySQL database:

CREATE DATABASE mcpdb;

2. Create a MySQL user with access to the database:

CREATE USER 'mcp101'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mcpdb.* TO 'mcp101'@'localhost';
FLUSH PRIVILEGES;

3. Create a test table:

USE mcpdb;
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

🛠️ MCP Configuration

Configure the MySQL MCP server in different environments:

• VS Code: Add the following to settings.json:

{
    "mcp.servers": [
        {
            "name": "mysql-mcp-server",
            "type": "mysql",
            "host": "localhost",
            "port": 3306,
            "database": "mcpdb",
            "user": "mcp101"
        }
    ]
}

💻 Usage Examples

Call the tool using the following syntax in a conversation:

{
    "tools": {
        "mysql-mcp-server": {
            "function": "executeQuery",
            "args": {
                "query": "SELECT * FROM products LIMIT 5;"
            }
        }
    }
}

Basic Usage

Execute Query

Used to execute arbitrary SQL queries. Parameters

• query: The SQL query string to execute. Example

{
    "query": "SELECT * FROM products LIMIT 5;"
}

Create Table

Used to create a new database table. Parameters

• query: A CREATE TABLE statement. Example

{
    "query": "CREATE TABLE users (id INT PRIMARY KEY, username VARCHAR(255), email VARCHAR(255));"
}

Insert Data

Used to insert new records into a table. Parameters

• query: An INSERT INTO statement. Example

{
    "query": "INSERT INTO products (name, price) VALUES ('Laptop', 999.99), ('Smartphone', 499.99);"
}

Update Data

Used to update records in a table. Parameters

• query: An UPDATE statement. Example

{
    "query": "UPDATE products SET price = 899.99 WHERE name = 'Laptop';"
}

Delete Data

Used to delete records from a table. Parameters

• query: A DELETE FROM statement. Example

{
    "query": "DELETE FROM products WHERE name = 'Smartphone';"
}

⚠️ Security Considerations

• Use a dedicated MySQL user with appropriate privileges.
• Consider using read-only privileges if only querying data is required.
• Store sensitive information, such as database credentials, securely.
• All operations are accompanied by a unique transaction ID for auditing purposes.