MCP Server MySQL

A Model Context Protocol (MCP) server that provides MySQL database operations. This server enables LLMs to interact with MySQL databases through a standardized protocol.

Features

• Database Management: Create, drop, list, and switch databases
• Table Operations: Create, alter, drop, describe, and list tables
• Data Queries: Execute SELECT queries and retrieve results
• Data Modification: Execute INSERT, UPDATE, DELETE statements
• Index Management: Create and drop indexes

Installation

npm install
npm run build

Configuration

Set environment variables for database connection:

export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=root
export MYSQL_PASSWORD=your-password
export MYSQL_DATABASE=your-database  # optional

Safety and Permission Controls

The server defaults to read-only mode. Enable write or schema operations explicitly:

export MYSQL_MODE=readonly       # readonly | data-write | schema-write | admin
export MYSQL_ALLOW_DDL=false     # Set to "true" to enable schema/admin tools
export MYSQL_MAX_ROWS=100        # Optional cap for query result rows

Legacy data-write controls are still supported and are enforced when MYSQL_MODE allows data writes:

export MYSQL_ALLOW_INSERT=true   # Set to "false" to disable INSERT
export MYSQL_ALLOW_UPDATE=true   # Set to "false" to disable UPDATE
export MYSQL_ALLOW_DELETE=false  # Set to "false" to disable DELETE

Or use the connect tool at runtime to specify connection parameters.

High-risk DDL tools require a confirm parameter that matches the target name:

• alter_table: confirm must match the table name
• drop_table: confirm must match the table name
• drop_database: confirm must match the database name
• drop_index: confirm must match the index name

For AI-assisted development, prefer least-privilege MySQL users. Use a read-only database account for exploration and enable MYSQL_MODE=data-write, MYSQL_MODE=schema-write, or MYSQL_MODE=admin only for trusted workflows.

Supported AI Applications

This MCP server can be used with any application that supports the Model Context Protocol (MCP):

• Claude Desktop - Anthropic's official desktop application
• Claude Code - Anthropic's official CLI tool for developers
• Cline - AI coding assistant VS Code extension
• Zed - High-performance code editor with built-in AI
• Any MCP-compatible application - MCP is an open protocol developed by Anthropic

Usage with Claude Desktop

Using npx (Recommended)

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["-y", "@nilsir/mcp-server-mysql"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DATABASE": "your-database",
        "MYSQL_MODE": "readonly",
        "MYSQL_ALLOW_DDL": "false",
        "MYSQL_ALLOW_INSERT": "true",
        "MYSQL_ALLOW_UPDATE": "true",
        "MYSQL_ALLOW_DELETE": "false",
        "MYSQL_MAX_ROWS": "100"
      }
    }
  }
}

Using Local Installation

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-server-mysql/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DATABASE": "your-database",
        "MYSQL_MODE": "readonly",
        "MYSQL_ALLOW_DDL": "false",
        "MYSQL_ALLOW_INSERT": "true",
        "MYSQL_ALLOW_UPDATE": "true",
        "MYSQL_ALLOW_DELETE": "false",
        "MYSQL_MAX_ROWS": "100"
      }
    }
  }
}

Available Tools

| Tool | Description | |------|-------------| | connect | Connect to a MySQL database | | query | Execute SELECT queries | | explain_query | Run EXPLAIN for a single SELECT query | | execute | Execute INSERT/UPDATE/DELETE queries when policy allows | | dry_run_execute | Execute INSERT/UPDATE/DELETE in a transaction and roll it back | | list_databases | List all databases | | list_tables | List tables in a database | | describe_table | Get table structure | | inspect_schema | Inspect tables, columns, and indexes for AI context | | find_tables | Find tables by table or column name | | sample_rows | Read a capped sample of rows from a table | | create_table | Create a new table | | alter_table | Modify table structure | | drop_table | Drop a table | | create_database | Create a new database | | drop_database | Drop a database | | use_database | Switch to a database | | create_index | Create an index | | drop_index | Drop an index |

Examples

Query data

Use the query tool with sql: "SELECT * FROM users WHERE active = ?"
and params: [true]

Analyze a SELECT query plan

Use the explain_query tool with:
- sql: "SELECT * FROM users WHERE email = ?"
- params: ["[email protected]"]
- format: "traditional"

explain_query only accepts one SELECT statement. Use format: "json" when an AI-readable JSON plan is more useful.

Create a table

Use the create_table tool with:
- table: "users"
- columns: [
    {"name": "id", "type": "INT", "primaryKey": true, "autoIncrement": true},
    {"name": "email", "type": "VARCHAR(255)", "nullable": false},
    {"name": "created_at", "type": "TIMESTAMP", "default": "CURRENT_TIMESTAMP"}
  ]

Inspect schema for AI context

Use the inspect_schema tool with:
- database: "nilsir"
- includeColumns: true
- includeIndexes: true

Find likely tables or columns

Use the find_tables tool with:
- database: "nilsir"
- term: "user"

Sample rows safely

Use the sample_rows tool with:
- database: "nilsir"
- table: "users"
- limit: 5

Sampling is read-only and capped at 50 rows.

Insert data

Use the execute tool with sql: "INSERT INTO users (email) VALUES (?)"
and params: ["[email protected]"]

Requires MYSQL_MODE=data-write or higher.

Dry-run a data change

Use the dry_run_execute tool with sql: "UPDATE users SET active = ? WHERE id = ?"
and params: [false, 123]

The statement runs inside a transaction and is rolled back before returning.

Drop a table

Use the drop_table tool with:
- table: "users"
- confirm: "users"

Requires MYSQL_MODE=schema-write or MYSQL_MODE=admin and MYSQL_ALLOW_DDL=true.

Development

npm install
npm run build
npm test

Integration coverage for dry-run rollback is opt-in because it needs a real test database:

MYSQL_INTEGRATION_TEST=true npm test

License

MIT