bhaveshrmahajan/mysql-mcp-server

MySQL MCP Server

A powerful Model Context Protocol (MCP) server that connects your AI assistant (like Claude Desktop) directly to your MySQL database. This server enables safe, controlled access to inspect schemas, query data, and even perform administrative tasks, all through natural language.

🚀 Features

This server implements a wide range of MCP capabilities to give you full visibility and control over your MySQL database.

🛠️ Tools

Table Management

• list-tables: List all tables in the database.
• describe-table: Get the schema (columns, types, keys) of a specific table.
• create-table: Create a new table using a SQL query.

Data Operations (CRUD)

• insert-row: Insert a new row into a table using JSON data.
• update-row: Update existing rows with a convenient JSON-based WHERE clause.
• delete-row: Delete rows with a safety-first JSON-based WHERE clause.

Query Execution

• execute-query: Execute read-only SQL queries (SELECT, SHOW, DESCRIBE). Safe for exploration.
• execute-write-query: Execute write SQL queries (INSERT, UPDATE, DELETE, ALTER, etc.). Use with caution.

Database Administration

• create-database: Create a new database.

Advanced Objects Inspection

• Stored Procedures: list-procedures, show-procedure (view CREATE statement).
• Functions: list-functions, show-function (view CREATE statement).
• Triggers: list-triggers, show-trigger.
• Views: list-views, show-view.

📦 Resources

Access database content directly as standard MCP resources:

• mysql://<table_name>/schema: View the full JSON schema of a table.
• mysql://<table_name>/data: View the first 100 rows of a table as JSON.

💬 Prompts

Pre-built templates to help the AI perform complex tasks:

• analyze-table: "Analyze the structure and sample data for table 'Users'..."
  • Automatically fetches schema and sample data to provide insights.
• optimize-query: "Analyze this query for performance..."
  • Helps you write faster, more efficient SQL.

📦 Installation

1. Clone the repository:

   git clone <repository-url>
   cd mysql-mcp-server
   

2. Install dependencies:

   npm install
   

3. Build the server:

   npm run build
   

⚙️ Configuration

1. Environment Variables

The server uses mysql2 and requires connection details. You can pass these via environment variables.

2. Claude Desktop Config

Add the following to your claude_desktop_config.json:

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/ABSOLUTE/PATH/TO/mysql-mcp-server/dist/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_USER": "root",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database",
        "DB_PORT": "3306"
      }
    }
  }
}

Note: Make sure to replace /ABSOLUTE/PATH/TO/... with the actual path to your project.

💡 Usage Examples

Once connected, you can ask Claude questions like:

• Exploration: "What tables are in my database?"
• Schema: "Show me the schema for the orders table."
• Data Analysis: "Select the top 5 customers who spent the most money."
• Modification: "Insert a new user named 'Alice' into the users table."
• Optimization: "Analyze this query and tell me why it's slow: SELECT * FROM logs"

🔒 Security Note

This server has powerful capabilities, including write access (execute-write-query, delete-row, etc.). Always ensure you are connecting to a database where you have appropriate permissions, and be careful when asking the AI to modify data.

📄 License

ISC