MCP Data Conduit for MySQL (NodeJS Backend)

This implementation serves as a specialized Model Context Protocol (MCP) gateway, connecting Large Language Models (LLMs) to backend MySQL data stores. It permits schema introspection and secure execution of data manipulation instructions.

Table of Contents

• Prerequisites
• Deployment Options
• Claude Desktop Setup
• Cursor IDE Integration
• Smithery Automation
• MCP Get Utility
• Manual Node Package Installation
• Local Source Execution
• Feature Set
• Runtime Configuration
• Environment Variable Reference
• Multi-Database Operation
• Granular Access Controls
• Validation Procedures
• Issue Resolution
• Contribution Guidelines
• Licensing

Prerequisites

• Runtime Environment: Node.js version 18 or later.
• Database System: MySQL Server version 5.7 minimum (8.0+ advised).
• Credentials: A dedicated MySQL account with sufficient privileges for intended operations.
• Write Access Requirement: Account must possess explicit INSERT, UPDATE, and/or DELETE rights if modification is required.

Deployment Options

Claude Desktop Setup

Configure your claude_desktop_config.json file with the following server definition:

{ "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/your/node/bin:/usr/bin:/bin", "NODE_PATH": "/path/to/your/node/modules" } } } }

Cursor IDE Integration

Use mcprunner for command-line deployment within Cursor projects:

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

For newer Cursor versions (v0.47+), utilize a dedicated mcp.json configuration:

mcp.json

{ "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" ] } } }

Smithery Automation

The streamlined deployment method via Smithery:

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

Smithery manages environment variable setup, LLM linkage, connectivity validation, and initial configuration of write permissions.

MCP Get Utility

Installation via the centralized registry utility:

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

Manual Node Package Installation

Direct installation via package managers requires subsequent manual configuration:

bash

npm

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

pnpm

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

Local Source Execution

For development or direct execution from source:

1. Source Checkout bash git clone https://github.com/benborla/mcp-server-mysql.git cd mcp-server-mysql

2. Dependency Installation & Compilation bash npm install # or pnpm install npm run build # or pnpm run build

3. Configuration Example (for Claude Desktop)

Use absolute paths for command and args:

{ "mcpServers": { "mcp_server_mysql": { "command": "/path/to/node", "args": [ "/full/path/to/mcp-server-mysql/dist/index.js" ], "env": { / ... connection env vars ... / } } } }

1. Testing Local Run bash node dist/index.js

Feature Set

Tools

• mysql_query
  • Function: Executes provided SQL strings against the connected MySQL instance.
  • Input: sql (String) - The command to run.
  • Security: Defaults to read-only enforcement. Write operations (INSERT, UPDATE, DELETE) require explicit global or schema-level enablement.
  • Reliability: Operations are wrapped in ACID-compliant transactions. Utilizes parameterized statements to negate SQL injection risks.
  • Controls: Supports configurable execution limits (timeouts) and result set pagination.

Resources

The service exposes comprehensive metadata regarding the underlying database structure:

• Relational Schemas: Full JSON description of tables, including column definitions, data types, indexing strategies, primary/foreign key constraints, and derived table statistics.

Security Protocols

• Protection against injection attacks via mandatory prepared statements.
• Optional query filtering lists (whitelisting/blacklisting).
• Request throttling mechanisms (rate limiting).
• Analysis of query structural complexity.
• Support for TLS/SSL connection encryption.
• Default configuration mandates read-only access.

Optimization Strategies

• Efficient connection pooling management.
• In-memory caching for frequently accessed metadata and query results.
• Streaming support for large result payloads.
• Tools for examining query execution plans.

Diagnostics & Auditing

• Detailed logging of all executed commands.
• Collection of operational performance metrics.
• Error reporting infrastructure.
• System health check endpoints.

Runtime Configuration

Smithery Configuration Management

If Smithery was used for setup, modifications can be made via:

bash smithery configure @benborla29/mcp-server-mysql

Configuration updates cover connection parameters and write access toggles (INSERT, UPDATE, DELETE).

Advanced JSON Overrides

For granular control, modify the hosting application's configuration file:

{ "mcpServers": { "mcp_server_mysql": { "command": "/path/to/npx/binary/npx", "args": [ "-y", "@benborla29/mcp-server-mysql" ], "env": { // Connection "MYSQL_HOST": "127.0.0.1", "MYSQL_USER": "root", "MYSQL_PASS": "", "MYSQL_DB": "db_name",

    // Performance
    "MYSQL_POOL_SIZE": "10",
    "MYSQL_QUERY_TIMEOUT": "30000",

    // Security / Write Access
    "ALLOW_INSERT_OPERATION": "false",
    "ALLOW_DDL_OPERATION": "false"
  }
}

} }

Environment Variable Reference

Core Connection Details

• MYSQL_HOST: Network address (Default: "127.0.0.1")
• MYSQL_PORT: TCP port (Default: "3306")
• MYSQL_USER: Authentication principal (Default: "root")
• MYSQL_PASS: Secret credential
• MYSQL_DB: Specific database target (Empty for Multi-DB Mode)

Performance Tuning

• MYSQL_POOL_SIZE: Maximum concurrent connections (Default: "10")
• MYSQL_QUERY_TIMEOUT: Command execution deadline in milliseconds (Default: "30000")

Security Controls

• MYSQL_RATE_LIMIT: Max queries permitted per minute (Default: "100")
• MYSQL_SSL: Boolean flag for encrypted transport (Default: "false")
• Write Operation Flags:
  • ALLOW_INSERT_OPERATION: Permit data insertion (Default: "false")
  • ALLOW_UPDATE_OPERATION: Permit data modification (Default: "false")
  • ALLOW_DELETE_OPERATION: Permit data removal (Default: "false")
  • ALLOW_DDL_OPERATION: Permit structural changes (Default: "false")
• Schema Overrides: SCHEMA_INSERT_PERMISSIONS, SCHEMA_UPDATE_PERMISSIONS, etc., allow fine-grained control mapped by database name.

Multi-Database Operation

When MYSQL_DB is omitted, the server operates in a context where the LLM can target any accessible schema. This requires explicit schema qualification in SQL statements:

sql -- Targetting a specific database explicitly SELECT * FROM my_schema.user_table;

-- Or changing context mid-session USE another_schema; SELECT count(*) FROM settings;

Schema-Specific Permissions

Fine-grained permission mapping via comma-separated lists:

SCHEMA_INSERT_PERMISSIONS=db_staging:true,db_prod:false SCHEMA_UPDATE_PERMISSIONS=db_staging:true,db_prod:false SCHEMA_DELETE_PERMISSIONS=db_staging:false,db_prod:true

Validation Procedures

Test Environment Configuration

1. Database Setup: Ensure a dedicated test instance (mcp_test) and user exist with necessary rights. sql 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;

2. Seeding: Execute the provided setup script. bash pnpm run setup:test:db

3. Environment File: Create .env.test with connection details (e.g., pointing to the test user).

Execution

Run the automated validation suite:

bash pnpm test

Issue Resolution

Connection Failures

• Confirm MySQL service status.
• Validate host, port, and authentication credentials.
• Check network reachability and firewall rules.

Path Resolution Errors (Common in Hosted Environments)

If the runner cannot locate Node.js, explicitly define system binaries in the configuration's env block:

• Finding Node PATH: Run echo "$(which node)/../" in your terminal.

Authentication Errors (MySQL 8.0+)

If connection fails due to authentication plugin issues, the MySQL user might require the legacy password method:

sql CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Module Not Found (dotenv error)

If module resolution fails, execute the server using explicit package loading:

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

Contribution Guidelines

We welcome external contributions via Pull Requests submitted to https://github.com/benborla/mcp-server-mysql

Development Workflow

1. Fork and clone the repository.
2. Install dependencies (pnpm install).
3. Build artifacts (pnpm run build).
4. Execute tests (pnpm test).

Roadmap Focus Areas

Development efforts are directed toward expanding transactional safety, increasing security controls, and improving performance diagnostics. Review CHANGELOG.md for current priorities.

Licensing

Distributed under the MIT License. See LICENSE file for specifics.

== WIKIPEDIA CONTEXT: HTTP Request Mechanism (For reference on client-side communication patterns) ==

XMLHttpRequest (XHR) is a JavaScript API facilitating HTTP communication between a client application (like a web browser) and a remote server after initial page load. XHR is foundational to Asynchronous JavaScript and XML (Ajax) programming paradigms, moving beyond traditional full-page reloads mandated by prior hyperlink/form interactions.

Historical Context

Conceptualized around 2000 by Microsoft Outlook developers and first shipped in IE 5 (1999), initially using proprietary ActiveXObject syntax. Modern browsers adopted the standardized XMLHttpRequest identifier by 2006, with its specification later managed by the W3C and subsequently by WHATWG, evolving to support advanced features like progress monitoring and cross-origin resource sharing (CORS) in Level 2 specifications.

Basic Operational Flow

The typical client-side invocation involves these sequential steps:

1. Instantiation of the XMLHttpRequest object.
2. Invocation of the .open() method to define the request type (GET/POST, etc.), target URI, and operational mode (synchronous/asynchronous).
3. (For async) Registration of an event handler to monitor state transitions.
4. Initiation of the network transfer via .send(data).
5. Event listener processes the response upon reaching state 4 ("done"), accessing raw data in .responseText or structured data via related properties.

Advanced controls permit setting custom HTTP headers, uploading request bodies, and parsing incoming data streams.