MCP HubMCP Hub
benborla

mcp-server-mysql

by: benborla

A Model Context Protocol server that provides read-only access to MySQL databases. This server enables LLMs to inspect database schemas and execute read-only queries.

244created 09/12/2024
Visit
MySQL
LLM

📌Overview

Purpose: To provide a Model Context Protocol (MCP) server that allows read-only access to MySQL databases for executing queries and inspecting database schemas.

Overview: The MCP Server for MySQL, built on NodeJS, facilitates safe and efficient read-only interactions with MySQL databases. It leverages the Model Context Protocol to enable Language Learning Models (LLMs) to query and understand database structures without altering data.

Key Features:

  • Read-Only SQL Execution: Execute secure read-only SQL queries using prepared statements, ensuring the integrity and safety of the database.

  • Comprehensive Schema Information: Automatically retrieves and provides detailed schema information for tables, including column data types and relationships, directly from the database metadata.

  • Advanced Security Measures: Implements SQL injection prevention, query whitelisting/blacklisting, and robust rate limiting to safeguard database interactions.

  • Performance Optimizations: Features include connection pooling, query caching, and large result set streaming to enhance execution speed and efficiency.

  • Monitoring and Debugging Tools: Includes extensive logging, performance metrics, and error tracking to facilitate system oversight and troubleshooting.

MCP Server for MySQL based on NodeJS

A Model Context Protocol server that provides access to MySQL databases. This server enables LLMs to inspect database schemas and execute SQL queries.

Table of Contents

  • Requirements
  • Installation
    • Claude Desktop
    • Cursor
    • Using Smithery
    • Using MCP Get
    • Using NPM/PNPM
    • Running from Local Repository
  • Components
  • Configuration
  • Environment Variables
  • Multi-DB Mode
  • Schema-Specific Permissions
  • Testing
  • Troubleshooting
  • Contributing
  • License

Requirements

  • Node.js v18 or higher
  • MySQL 5.7 or higher (MySQL 8.0+ recommended)
  • MySQL user with appropriate permissions for needed operations
  • For write operations: MySQL user with INSERT, UPDATE, and/or DELETE privileges

Installation

Claude Desktop

Add to your claude_desktop_config.json file (usually in user directory):

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "@benborla29/mcp-server-mysql"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "your_password",
        "MYSQL_DB": "your_database",
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false",
        "PATH": "/path/to/node/bin:/usr/bin:/bin",
        "NODE_PATH": "/path/to/node/lib/node_modules"
      }
    }
  }
}

Replace paths and credentials appropriately.

Cursor

Install with:

npx mcprunner MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASS=root MYSQL_DB=demostore ALLOW_INSERT_OPERATION=true ALLOW_UPDATE_OPERATION=true ALLOW_DELETE_OPERATION=false -- npx -y @benborla29/mcp-server-mysql

Alternatively, use this mcp.json for Cursor v0.47+:

{
  "mcpServers": {
    "MySQL": {
      "command": "npx",
      "args": [
        "mcprunner",
        "MYSQL_HOST=127.0.0.1",
        "MYSQL_PORT=3306",
        "MYSQL_USER=root",
        "MYSQL_PASS=root",
        "MYSQL_DB=demostore",
        "ALLOW_INSERT_OPERATION=true",
        "ALLOW_UPDATE_OPERATION=true",
        "ALLOW_DELETE_OPERATION=false",
        "--",
        "npx",
        "-y",
        "@benborla29/mcp-server-mysql"
      ]
    }
  }
}

Using Smithery

Install using Smithery CLI:

npx -y @smithery/cli@latest install @benborla29/mcp-server-mysql --client claude

You will be prompted for your MySQL connection details and write operation permissions. Write operations are disabled by default for security.

Using MCP Get

Install via MCP Get:

npx @michaellatman/mcp-get@latest install @benborla29/mcp-server-mysql

Using NPM/PNPM

Manual install:

# npm
npm install -g @benborla29/mcp-server-mysql

# pnpm
pnpm add -g @benborla29/mcp-server-mysql

Configure your LLM application accordingly.

Running from Local Repository

  1. Clone the repo:
git clone https://github.com/benborla/mcp-server-mysql.git
cd mcp-server-mysql
  1. Install dependencies:
npm install
# or
pnpm install
  1. Build the project:
npm run build
# or
pnpm run build
  1. Configure Claude Desktop with absolute paths in claude_desktop_config.json:
{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "/path/to/node",
      "args": [
        "/full/path/to/mcp-server-mysql/dist/index.js"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "your_password",
        "MYSQL_DB": "your_database",
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false",
        "PATH": "/path/to/node/bin:/usr/bin:/bin",
        "NODE_PATH": "/path/to/node/lib/node_modules"
      }
    }
  }
}
  1. Test the server:
node dist/index.js

If successful, the server is ready for use.

Components

Tools

  • mysql_query
    • Execute SQL queries against the database
    • Input: sql (string)
    • Read-only by default; write operations require explicit enabling
    • Supports transactions, prepared statements, query timeouts, pagination
    • Built-in query execution statistics

Resources

Provides comprehensive database info including:

  • Table schemas with columns, data types, indexes, constraints, foreign keys, and statistics
  • Metadata automatically discovered from the database

Security Features

  • SQL injection prevention via prepared statements
  • Query whitelist/blacklist
  • Rate limiting
  • Query complexity analysis
  • Connection encryption options
  • Enforcement of read-only transactions

Performance Optimizations

  • Connection pooling
  • Query result caching
  • Streaming for large result sets
  • Query execution plan analysis
  • Configurable timeouts

Monitoring and Debugging

  • Query logging
  • Performance metrics
  • Error tracking
  • Health checks
  • Query execution statistics

Configuration

Automatic Configuration with Smithery

If using Smithery, configuration is automatic. Modify with:

smithery configure @benborla29/mcp-server-mysql

You can update connection details, SSL, and write permissions.

Advanced Configuration Options

Example configuration:

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "/path/to/npx",
      "args": [
        "-y",
        "@benborla29/mcp-server-mysql"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "",
        "MYSQL_DB": "db_name",
        "PATH": "/path/to/node/bin:/usr/bin:/bin",
        "MYSQL_POOL_SIZE": "10",
        "MYSQL_QUERY_TIMEOUT": "30000",
        "MYSQL_CACHE_TTL": "60000",
        "MYSQL_RATE_LIMIT": "100",
        "MYSQL_MAX_QUERY_COMPLEXITY": "1000",
        "MYSQL_SSL": "true",
        "MYSQL_ENABLE_LOGGING": "true",
        "MYSQL_LOG_LEVEL": "info",
        "MYSQL_METRICS_ENABLED": "true",
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false"
      }
    }
  }
}

Environment Variables

Basic Connection

  • MYSQL_HOST (default: "127.0.0.1")
  • MYSQL_PORT (default: "3306")
  • MYSQL_USER (default: "root")
  • MYSQL_PASS
  • MYSQL_DB (leave empty for multi-DB mode)

Performance Configuration

  • MYSQL_POOL_SIZE (default: "10")
  • MYSQL_QUERY_TIMEOUT in ms (default: "30000")
  • MYSQL_CACHE_TTL in ms (default: "60000")

Security Configuration

  • MYSQL_RATE_LIMIT (default: "100")
  • MYSQL_MAX_QUERY_COMPLEXITY (default: "1000")
  • MYSQL_SSL (default: "false")
  • ALLOW_INSERT_OPERATION (default: "false")
  • ALLOW_UPDATE_OPERATION (default: "false")
  • ALLOW_DELETE_OPERATION (default: "false")
  • ALLOW_DDL_OPERATION (default: "false")
  • SCHEMA_INSERT_PERMISSIONS
  • SCHEMA_UPDATE_PERMISSIONS
  • SCHEMA_DELETE_PERMISSIONS
  • SCHEMA_DDL_PERMISSIONS
  • MULTI_DB_WRITE_MODE (default: "false")

Monitoring Configuration

  • MYSQL_ENABLE_LOGGING (default: "false")
  • MYSQL_LOG_LEVEL (default: "info")
  • MYSQL_METRICS_ENABLED (default: "false")

Multi-DB Mode

MCP-Server-MySQL supports multiple databases when MYSQL_DB is empty. The LLM can query any accessible database.

Enabling Multi-DB Mode

Leave MYSQL_DB empty. Use schema-qualified queries, e.g.:

SELECT * FROM database_name.table_name;

USE database_name;
SELECT * FROM table_name;

Schema-Specific Permissions

Control database operations per schema:

SCHEMA_INSERT_PERMISSIONS=development:true,test:true,production:false
SCHEMA_UPDATE_PERMISSIONS=development:true,test:true,production:false
SCHEMA_DELETE_PERMISSIONS=development:false,test:true,production:false
SCHEMA_DDL_PERMISSIONS=development:false,test:true,production:false

Testing

Database Setup

  1. Create test database and user:
CREATE DATABASE IF NOT EXISTS mcp_test;

CREATE USER IF NOT EXISTS 'mcp_test'@'localhost' IDENTIFIED BY 'mcp_test_password';
GRANT ALL PRIVILEGES ON mcp_test.* TO 'mcp_test'@'localhost';
FLUSH PRIVILEGES;
  1. Run setup script:
pnpm run setup:test:db
  1. Create .env.test file:
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=mcp_test
MYSQL_PASS=mcp_test_password
MYSQL_DB=mcp_test
  1. Update package.json scripts:
{
  "scripts": {
    "setup:test:db": "ts-node scripts/setup-test-db.ts",
    "pretest": "pnpm run setup:test:db",
    "test": "vitest run",
    "test:watch": "vitest",
    "test:coverage": "vitest run --coverage"
  }
}

Running Tests

Set up the test DB and run tests:

pnpm run setup:test:db
pnpm test

Troubleshooting

Common Issues

  1. Connection Issues

    • Verify MySQL accessibility, credentials, permissions
    • Check SSL/TLS configuration
    • Try connecting with a MySQL client

  2. Performance Issues

    • Adjust connection pool, query timeout, caching
    • Check query complexity
    • Monitor server usage

  3. Security

    • Review rate limiting, whitelist/blacklist, SSL/TLS
    • Verify user privileges

  4. Path Resolution

If you get "Could not connect to MCP server mcp-server-mysql", explicitly set the PATH in your configuration:

{
  "env": {
    "PATH": "/path/to/node/bin:/usr/bin:/bin"
  }
}

Get paths via:

echo "$(which node)/../"
echo "$(which node)/../../lib/node_modules"
  1. Claude Desktop Issues
  • Check logs at ~/Library/Logs/Claude/mcp-server-mcp_server_mysql.log
  • Use absolute paths for Node binary and server script
  • Use explicit environment variables if .env not loaded properly
  • Test server manually via CLI
  • Enable write operations by setting:
"env": {
  "ALLOW_INSERT_OPERATION": "true",
  "ALLOW_UPDATE_OPERATION": "true",
  "ALLOW_DELETE_OPERATION": "true"
}

Ensure MySQL user permissions match.

  1. Authentication Issues

For MySQL 8.0+, ensure caching_sha2_password plugin is supported or use legacy:

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
  1. Module Not Found Error

If you get Error [ERR_MODULE_NOT_FOUND]: Cannot find package 'dotenv', try:

npx -y -p @benborla29/mcp-server-mysql -p dotenv mcp-server-mysql

Contributing

Contributions are welcome! Submit a Pull Request to https://github.com/benborla/mcp-server-mysql

Development Setup

  1. Clone the repo
  2. Install dependencies: pnpm install
  3. Build the project: pnpm run build
  4. Run tests: pnpm test

Project Roadmap

Planned features include:

  • Enhanced query capabilities with prepared statements
  • Advanced security features
  • Performance optimizations
  • Comprehensive monitoring
  • Expanded schema information

Check GitHub issues or open a new one to discuss ideas.

Submitting Changes

  1. Fork the repository
  2. Create a feature branch: git checkout -b feature/your-feature-name
  3. Commit your changes: git commit -am 'Add some feature'
  4. Push to the branch: git push origin feature/your-feature-name
  5. Submit a pull request

License

This MCP server is licensed under the MIT License. See the LICENSE file for details.