README - SuMiaoALi_mysql-mcp-server by mcp-mirror

MySQL MCP Server

A Model Context Protocol (MCP) server that provides secure MySQL database operations for AI assistants and other MCP clients.

Features

🛠️ Tools

execute_query - Execute safe SQL queries (SELECT, INSERT, SHOW, DESCRIBE)
get_table_info - Get detailed table structure information
insert_data - Convenient data insertion with automatic parameterization
list_tables - List all tables in the database

📊 Resources

Database Info (mysql://database/info) - Database metadata and table list
Table Structure (mysql://table/{table_name}) - Detailed table schema information

🔒 Security Features

Limited Operations: Only allows safe operations (SELECT, INSERT, SHOW, DESCRIBE)
SQL Injection Protection: Uses parameterized queries
Automatic LIMIT: Adds LIMIT clauses to SELECT queries to prevent large data dumps
Connection Pooling: Efficient database connection management

Installation

npm install mysql-mcp-server

Configuration

The server can be configured using environment variables:

Variable	Default	Description
`MYSQL_HOST`	`localhost`	MySQL server hostname
`MYSQL_PORT`	`3306`	MySQL server port
`MYSQL_USER`	`root`	MySQL username
`MYSQL_PASSWORD`	(empty)	MySQL password
`MYSQL_DB`	`mysql`	Default database name
`MYSQL_TIMEZONE`	`+00:00`	MySQL timezone

Usage with MCP Clients

Claude Desktop

Add to your Claude Desktop configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "your-mysql-host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your-username",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DB": "your-database"
      }
    }
  }
}

Other MCP Clients

For other MCP clients, use the server with stdio transport:

npx mysql-mcp-server

Development

Prerequisites

Node.js 18+
TypeScript
Access to a MySQL database

Setup

Clone the repository:

git clone https://github.com/your-username/mysql-mcp-server.git
cd mysql-mcp-server

Install dependencies:

npm install

Set up environment variables:

cp .env.example .env
# Edit .env with your database configuration

Build the server:

npm run build

Development Commands

# Build the server
npm run build

# Watch mode for development
npm run watch

# Debug with MCP Inspector
npm run inspector

Testing

You can test the server using the MCP Inspector:

npm run inspector

This will start the inspector and provide a URL to test the server in your browser.

Example Usage

Once connected through an MCP client, you can:

List all tables:
```
Use the list_tables tool
```

Get table structure:

Use get_table_info with table_name: "users"

Query data:

Use execute_query with sql: "SELECT * FROM users LIMIT 10"

Insert data:

Use insert_data with table_name: "users" and data: {"name": "John", "email": "john@example.com"}

Security Considerations

This server is designed with security in mind:

Read-mostly operations: Only SELECT, INSERT, SHOW, and DESCRIBE operations are allowed
No destructive operations: UPDATE and DELETE are explicitly forbidden
Parameterized queries: All user inputs are properly escaped
Connection limits: Uses connection pooling to prevent resource exhaustion
Query limits: Automatic LIMIT clauses prevent large data dumps

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License - see LICENSE file for details.