MCP MySQL Server

A Model Context Protocol (MCP) server for MySQL database operations with built-in safety features and operation resistance. Perfect for use with Claude Code, Codex CLI, and other MCP-compatible AI assistants.

Features

• Safe MySQL Operations: Execute SELECT queries freely while controlling destructive operations
• Operation Resistance: Configurable INSERT, UPDATE, and DELETE restrictions
• Schema Protection: DROP, TRUNCATE, and ALTER operations are always blocked
• SQL Injection Prevention: Uses prepared statements with parameter binding
• Multiple Tools: Query execution, table listing, schema inspection, and database info
• Connection Pooling: Efficient connection management for better performance
• Detailed Error Reporting: Clear error messages with SQL codes

Quick Start

Option 1: Automated Setup (Easiest)

# One command - does everything!
./setup.sh

The script will:

• Auto-detect Bun or npm
• Install dependencies
• Create and optionally configure .env
• Build the project
• Test database connection

Option 2: Manual Setup

Using Bun (Faster):

bun install
cp .env.example .env && nano .env
bun run build

Using npm:

npm install
cp .env.example .env && nano .env
npm run build

Integration Guides

• Claude Code Setup - Complete guide for Claude Code integration
• Codex CLI Setup - Command-line usage and automation
• Usage Examples - Comprehensive query examples and patterns

Configuration

Environment Variables

Create a .env file (use .env.example as a template):

# MySQL Connection
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASS=your_password
MYSQL_DB=your_database

# Operation Permissions (true/false)
ALLOW_INSERT_OPERATION=false
ALLOW_UPDATE_OPERATION=false
ALLOW_DELETE_OPERATION=false

MCP Client Configuration

For Claude Code, add to your ~/.config/claude-code/.mcp.json:

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-mysql-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASS": "your_password",
        "MYSQL_DB": "your_database",
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false"
      }
    }
  }
}

Need more examples? See docs/mcp-config-examples.md for:

• Multiple databases
• Remote/Docker configurations
• Development vs production setups
• Platform-specific paths

See the integration guides for detailed setup instructions.

Safety Features

Always Blocked Operations

These operations are always blocked regardless of configuration:

• DROP - Dropping tables/databases
• TRUNCATE - Truncating tables
• ALTER - Altering table structure

Configurable Operations

Control these operations via environment variables:

• INSERT - Controlled by ALLOW_INSERT_OPERATION
• UPDATE - Controlled by ALLOW_UPDATE_OPERATION
• DELETE - Controlled by ALLOW_DELETE_OPERATION

Always Allowed Operations

• SELECT - Reading data is always permitted

Available Tools

1. mysql_query

Execute SQL queries with safety restrictions.

Parameters:

• query (string, required): The SQL query to execute
• params (array, optional): Parameters for prepared statement

Example:

{
  "query": "SELECT * FROM users WHERE id = ?",
  "params": [123]
}

Response:

{
  "success": true,
  "rows": [...],
  "rowCount": 5,
  "fields": [...]
}

2. mysql_list_tables

List all tables in the current database.

Example Response:

{
  "success": true,
  "tables": [
    { "Tables_in_db": "users" },
    { "Tables_in_db": "products" }
  ]
}

3. mysql_describe_table

Get the structure/schema of a specific table.

Parameters:

• table (string, required): Table name

Example Response:

{
  "success": true,
  "table": "users",
  "columns": [
    {
      "Field": "id",
      "Type": "int",
      "Null": "NO",
      "Key": "PRI",
      "Default": null,
      "Extra": "auto_increment"
    }
  ]
}

4. mysql_get_database_info

Get information about the database connection and permissions.

Example Response:

{
  "success": true,
  "connection": {
    "host": "localhost",
    "port": 3306,
    "user": "tommygun",
    "database": "db"
  },
  "permissions": {
    "INSERT": false,
    "UPDATE": false,
    "DELETE": false,
    "SELECT": true,
    "DROP": false,
    "TRUNCATE": false,
    "ALTER": false
  }
}

Usage Examples

For comprehensive examples, see examples/usage-examples.md.

Quick Examples

Safe SELECT Query:

Get the first 10 users from the database

Parameterized Query (Prevents SQL Injection):

Find products where category is 'electronics' and price is less than 1000

Data Analysis:

Show me monthly revenue for the last 6 months

Schema Exploration:

What tables are in my database?
Show me the structure of the users table

Blocked Operations

When operations are disabled, you'll receive clear error messages:

{
  "error": "INSERT operations are disabled. Set ALLOW_INSERT_OPERATION=true to enable.",
  "blocked": true
}

Enabling Write Operations

To enable write operations, update your .env:

# Enable INSERT operations
ALLOW_INSERT_OPERATION=true

# Enable UPDATE operations
ALLOW_UPDATE_OPERATION=true

# Enable DELETE operations (use with caution!)
ALLOW_DELETE_OPERATION=true

After changing permissions, restart the MCP server.

Security Best Practices

1. Default to Read-Only: Keep write operations disabled unless specifically needed
2. Use Prepared Statements: Always use the params array for user input
3. Validate Table Names: The server validates table names in describe operations
4. Schema Protection: DROP/TRUNCATE/ALTER are permanently blocked
5. Connection Pooling: Uses connection pooling for better performance and resource management

Error Handling

The server provides detailed error responses:

{
  "error": "Table 'db.nonexistent' doesn't exist",
  "code": "ER_NO_SUCH_TABLE",
  "sqlState": "42S02"
}

Testing and Verification

Quick Tests

Once connected to your MCP client, try these commands:

1. Check Connection:

   Show me the MySQL database information
   

2. List Tables:

   What tables are in my database?
   

3. Test Safety Features:

   Try to insert a test record into any table
   

   (Should be blocked with a clear error message)

4. Query Data:

   Show me sample data from the users table
   

Verification Checklist

• [ ] Server shows as connected in MCP client
• [ ] Database connection info displays correctly
• [ ] Table listing works
• [ ] SELECT queries execute successfully
• [ ] INSERT/UPDATE/DELETE are blocked (default)
• [ ] Clear error messages for blocked operations

For detailed testing examples, see examples/usage-examples.md.

Development

# Build the project
npm run build

# Run the server directly (for testing)
npm start

# Build and run
npm run dev

# Watch mode (if needed)
npm run build -- --watch

Project Structure

mcp-mysql-server/
├── src/
│   └── index.ts          # Main server implementation
├── dist/                 # Compiled JavaScript output
├── examples/             # Integration guides and examples
│   ├── claude-code-setup.md
│   ├── codex-cli-setup.md
│   └── usage-examples.md
├── .env.example          # Environment template
├── package.json
├── tsconfig.json
└── README.md

Contributing

See CONTRIBUTING.md for development guidelines.

Troubleshooting

Connection Issues

• Verify MySQL is running: mysql -u username -p
• Check host/port configuration
• Verify user credentials
• Ensure database exists

Permission Errors

• Check MySQL user has required permissions
• Verify ALLOW_*_OPERATION settings match your needs

Tool Not Found

• Ensure the server is properly configured in .mcp.json
• Verify the build succeeded: ls -la dist/index.js
• Check MCP client can find the server

Resources

• Quick Start Guide - Get started in 5 minutes
• Setup Checklist - Step-by-step setup guide
• Documentation Index - All documentation organized
• MCP Protocol Documentation
• Integration Guides - Claude Code & Codex CLI
• Usage Examples
• Contributing Guidelines
• Security Policy
• Project Improvements - What's new

Support

• Issues: Report bugs or request features via GitHub issues
• Questions: Check the examples directory for common use cases
• Security: For security concerns, please email the maintainers

Changelog

Version 1.0.0

• Initial release
• Basic MySQL operations (SELECT, INSERT, UPDATE, DELETE)
• Safety features and operation resistance
• Prepared statements for SQL injection prevention
• Connection pooling
• Comprehensive documentation

License

MIT - see LICENSE file for details