MySQL Data Provider for Model Context Protocol (MCP)

This module serves as an MCP endpoint engineered to interface directly with a MySQL relational database management system. It empowers autonomous agents to execute comprehensive data manipulation and query operations.

Core Capabilities

• SQL Command Execution: Supports the full spectrum of standard SQL operations against MySQL:
  • Data retrieval ($\text{SELECT}$)
  • Schema definition ($\text{CREATE TABLE}$)
  • Data insertion ($\text{INSERT INTO}$)
  • Data modification ($\text{UPDATE}$)
  • Data removal ($\text{DELETE FROM}$)
• Structured Output: All operational outcomes are standardized and delivered as JSON structures.
• Configurable Endpoints: Connection parameters for the database are externally configurable.
• Audit Trail: Comprehensive logging of every transaction, annotated with unique identifiers.

Prerequisites for Deployment

• Runtime environment: Node.js (version 14 or newer recommended)
• Target service: Operational MySQL server instance
• Development dependency: MCP Software Development Kit

Deployment Instructions

1. Acquire the source code repository (clone or download).
2. Resolve required library dependencies:

cd data-access-layer-mysql-mcp
npm install

1. Compile the necessary application artifacts:

npm run build

Configuration Parameters

The runtime behavior relies on environment variables to establish connectivity to MySQL:

• DB_HOST_ADDRESS: Hostname or IP of the MySQL instance (Default: 'localhost')
• DB_PORT_NUMBER: TCP port for MySQL communication (Default: 3306)
• DB_CREDENTIAL_USER: Authentication username (Default: 'mcp101')
• DB_CREDENTIAL_PASS: Corresponding password for the user (Default: '123qwe')
• DB_SCHEMA_NAME: The specific database schema to target (Default: 'mcpdb')

Initial Database Provisioning

1. Initialize the target schema:

CREATE DATABASE mcpdb;

1. Establish and authorize the dedicated service account:

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

1. Populate a sample relational entity for verification:

USE mcpdb;
CREATE TABLE test_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_users (name, email) VALUES
  ('John Doe', 'john@example.com'),
  ('Jane Smith', 'jane@example.com'),
  ('Bob Johnson', 'bob@example.com');

Integration into MCP Framework

Integrate this service connector into your primary MCP configuration file, adjusting paths as necessary.

VSCode Integration Context (Claude Extension)

Configuration File Location: ~/Library/Application Support/Code/User/globalStorage/saoudrizwan.claude-dev/settings/cline_mcp_settings.json

Ensure the command invocation points to the compiled JavaScript entry file and environment variables reflect your setup:

{
  "mcpServers": {
    "data-access-layer-mysql-mcp": {
      "autoApprove": [],
      "disabled": false,
      "timeout": 60,
      "command": "node",
      "args": [
        "/path/to/data-access-layer-mysql-mcp/build/index.js"
      ],
      "env": {
        "DB_HOST_ADDRESS": "localhost",
        "DB_PORT_NUMBER": "3306",
        "DB_CREDENTIAL_USER": "mcp101",
        "DB_CREDENTIAL_PASS": "123qwe",
        "DB_SCHEMA_NAME": "mcpdb"
      },
      "transportType": "stdio"
    }
  }
}

Claude Desktop Application Configuration

Configuration File Location: ~/Library/Application Support/Claude/claude_desktop_config.json

(Configuration structure mirrors the VSCode section above, using the service key data-access-layer-mysql-mcp.)

Operational Demonstration

Upon successful setup, an agent query such as, "Please furnish a roster of all records residing within the test_users table," will trigger the underlying tool execution for:

SELECT * FROM test_users

Exposed Utility Functions (Tools)

execute_data_read

Restricted to $\text{SELECT}$ operations. Retrieves data sets.

Parameters: - sql_command: The SQL query string for data selection.

Example Payload:

{
  "sql_command": "SELECT name, email FROM test_users WHERE id < 3"
}

define_table_structure

Used to instantiate new relational structures in the database.

Parameters: - sql_command: The complete $\text{CREATE TABLE}$ statement.

Example Payload:

{
  "sql_command": "CREATE TABLE inventory_items (item_id INT PRIMARY KEY, description VARCHAR(255), unit_cost NUMERIC(10,2))"
}

add_new_records

For populating entities with new data entries.

Parameters: - sql_command: The $\text{INSERT INTO}$ statement specifying target and values.

Example Payload:

{
  "sql_command": "INSERT INTO inventory_items (item_id, description, unit_cost) VALUES (101, 'Keyboard', 75.50), (102, 'Mouse', 25.00)"
}

modify_existing_records

Applies modifications to existing data rows.

Parameters: - sql_command: The $\text{UPDATE}$ statement detailing the changes and criteria.

Example Payload:

{
  "sql_command": "UPDATE inventory_items SET unit_cost = 69.99 WHERE description = 'Keyboard'"
}

discard_records

Removes specified data rows from a table.

Parameters: - sql_command: The $\text{DELETE FROM}$ statement defining the elimination criteria.

Example Payload:

{
  "sql_command": "DELETE FROM inventory_items WHERE item_id = 102"
}

Security Posture

• Utilize a database principal restricted solely to the needs of the MCP service.
• Employ read-only permissions if dynamic data modification is not a requirement.
• Database credentials must be managed via secure environment storage mechanisms.
• Every interaction is traceable via the mandated, unique transaction logging.