Dpflucas Mysql MCP Server

🚀 MySQL Database Access MCP Server

This MCP server provides read-only access to MySQL databases. It allows you to:

• List available databases
• List tables in a specified database
• Describe table schemas
• Execute read-only SQL queries

✨ Features

• Read-only access: Only SELECT, SHOW, DESCRIBE, and EXPLAIN statements are allowed.
• Query validation: Prevents SQL injection and blocks any data modification attempts.
• Query timeout: Prevents long-running queries from consuming resources.
• Row limit: Prevents excessive data from being returned.

📦 Installation

Option 1: Install via NPM (Recommended)

# Install globally
npm install -g mysql-mcp-server

# Or install locally in your project
npm install mysql-mcp-server

Option 2: Build from Source

# Clone the repository
git clone https://github.com/dpflucas/mysql-mcp-server.git
cd mysql-mcp-server

# Install dependencies and build
npm install
npm run build

3. Configure Environment Variables

The server requires the following environment variables:

• MYSQL_HOST: Database server hostname
• MYSQL_PORT: Database server port (default: 3306)
• MYSQL_USER: Database username
• MYSQL_PASSWORD: Database password
• MYSQL_DATABASE: Default database name (optional)

4. Add to MCP Settings

Add the following configuration to your MCP settings file:

If you installed via NPM (Option 1):

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "${MYSQL_HOST}",
        "MYSQL_PORT": "${MYSQL_PORT}",
        "MYSQL_USER": "${MYSQL_USER}",
        "MYSQL_PASSWORD": "${MYSQL_PASSWORD}",
        "MYSQL_DATABASE": "${MYSQL_DATABASE}"
      }
    }
  }
}

If you built from source (Option 2):

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["dist/index.js"],
      "env": {
        "MYSQL_HOST": "${MYSQL_HOST}",
        "MYSQL_PORT": "${MYSQL_PORT}",
        "MYSQL_USER": "${MYSQL_USER}",
        "MYSQL_PASSWORD": "${MYSQL_PASSWORD}",
        "MYSQL_DATABASE": "${MYSQL_DATABASE}"
      }
    }
  }
}

💻 Usage Examples

mysql-cli

• Function: Query the database through a command-line interface.
• Parameters:
  • --host or -H: Specify the MySQL host, default is localhost.
  • --port or -P: Specify the MySQL port, default is 3306.
  • --user or -u: Specify the MySQL username.
  • --password or -p: Specify the MySQL password.
  • --database or -D: Specify the default database name.

mysql-rest

• Function: Query the database through a REST API.
• Configuration parameters:
  • port: Specify the port the server listens on, default is 3000.
  • host: Specify the MySQL host, default is localhost.
  • user: Specify the MySQL username.
  • password: Specify the MySQL password.
  • database: Specify the default database name.

mysql-dump

• Function: Export database table structures and data.
• Usage examples:

  # Export all table structures
  ./mysql-dump.sh --host=localhost --port=3306 --user=root --password=admin
  
  # Export data from a specific database
  ./mysql-dump.sh --database=mydb > mydb_dump.sql
  

📚 Documentation

Testing Guide

Preparation

1. Create a test database mcp_test_db and ensure the test user has appropriate permissions.
2. Ensure all environment variables are set correctly.

Running Tests

Unit Tests

Run the following command to execute unit tests:

npm test

Tool Tests

Run the following command to test tool functionality:

npm run test:tools

Troubleshooting

If you encounter issues:

1. Check the server logs for error information.
2. Verify MySQL credentials and connection details.
3. Ensure the MySQL user has appropriate permissions.
4. Confirm that the query is read-only and in the correct format.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.