Read-Only MySQL MCP Server

A secure, read-only Model Context Protocol server for MySQL databases. This server enables AI assistants to safely explore and analyze MySQL databases without any risk of data modification.

🔒 Security-First Design

This server implements database-level read-only protection using MySQL's native transaction capabilities, following the same pattern as the official PostgreSQL MCP server from Anthropic.

How Read-Only Protection Works

// Every query is executed within a read-only transaction
await connection.query('SET TRANSACTION READ ONLY');
await connection.query('START TRANSACTION');
const result = await connection.query(userSQL);  // Any SQL allowed
await connection.query('ROLLBACK'); // Always rollback

Why this approach is bulletproof:

• 🛡️ MySQL enforces the restriction - impossible to bypass at application level
• 🔓 Full SQL flexibility - SELECT, SHOW, DESCRIBE, EXPLAIN all work perfectly
• 🚫 Zero write risk - INSERT, UPDATE, DELETE automatically rejected by database
• 🔄 Automatic cleanup - Each query is isolated in its own transaction

🚀 Installation

# Clone and install
git clone <your-repo>
cd mysql-mcp
npm install
npm run build

Quick Setup for Claude Code Users

🚀 Super Quick Setup (NPX - Recommended):

claude mcp add mysql-query-server \
  -e MYSQL_HOST=your_host \
  -e MYSQL_PORT=3306 \
  -e MYSQL_USER=your_user \
  -e MYSQL_PASSWORD=your_password \
  -e MYSQL_DATABASE=your_database \
  -- npx @makeappeasy/[email protected]

Example with real values:

claude mcp add mysql-query-server \
  -e MYSQL_HOST=34.93.229.229 \
  -e MYSQL_PORT=3306 \
  -e MYSQL_USER=root \
  -e MYSQL_PASSWORD=Letitgo@98 \
  -e MYSQL_DATABASE=information_schema \
  -- npx @makeappeasy/[email protected]

📦 Local Development Setup:

1. Clone and build (one-time setup):

   git clone <your-repo>
   cd mysql-mcp
   npm install && npm run build

2. Add to Claude Code (replace with your details):

   claude mcp add mysql-query-server \
     -e MYSQL_HOST=your_host \
     -e MYSQL_PORT=3306 \
     -e MYSQL_USER=your_user \
     -e MYSQL_PASSWORD=your_password \
     -e MYSQL_DATABASE=your_database \
     -- node /full/path/to/mysql-mcp/build/index.js

✅ Ready to use - Your AI assistant can now safely explore your database!

⚙️ Configuration

Option 1: Claude Code CLI with NPX (Recommended)

The easiest way to add this server to Claude Code using the published npm package:

claude mcp add query-protocol-server npx @makeappeasy/query-protocol-server --env MYSQL_HOST=localhost --env MYSQL_PORT=3306 --env MYSQL_USER=root --env MYSQL_PASSWORD=your_password --env MYSQL_DATABASE=your_database

Example with sample values:

claude mcp add query-protocol-server npx @makeappeasy/query-protocol-server --env MYSQL_HOST=192.168.1.100 --env MYSQL_PORT=3306 --env MYSQL_USER=dbuser --env MYSQL_PASSWORD=mypassword123 --env MYSQL_DATABASE=analytics_db

Using MySQL URL format:

claude mcp add query-protocol-server npx @makeappeasy/query-protocol-server mysql://dbuser:[email protected]:3306/analytics_db

Option 2: Claude Code CLI with Local Build

If you prefer to use your local build:

claude mcp add query-protocol-server node /path/to/mysql-mcp/build/index.js --env MYSQL_HOST=localhost --env MYSQL_PORT=3306 --env MYSQL_USER=root --env MYSQL_PASSWORD=your_password --env MYSQL_DATABASE=your_database

Option 3: Manual Configuration - NPX with JSON

{
  "mcpServers": {
    "query-protocol-server": {
      "command": "npx",
      "args": ["@makeappeasy/query-protocol-server", "mysql://user:password@localhost:3306/database"]
    }
  }
}

Option 4: Manual Configuration - Local Build

{
  "mcpServers": {
    "mysql-readonly": {
      "command": "node",
      "args": ["/path/to/mysql-mcp/build/index.js", "mysql://user:password@localhost:3306/database"]
    }
  }
}

Option 5: Manual Configuration - Environment Variables

{
  "mcpServers": {
    "query-protocol-server": {
      "command": "npx",
      "args": ["@makeappeasy/query-protocol-server"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_user", 
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "MYSQL_PORT": "3306"
      }
    }
  }
}

Option 6: Local Build with Environment Variables

{
  "mcpServers": {
    "mysql-readonly": {
      "command": "node",
      "args": ["/path/to/mysql-mcp/build/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_user", 
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database",
        "MYSQL_PORT": "3306"
      }
    }
  }
}

Option 7: Direct Execution

# Using NPX with MySQL URL
npx @makeappeasy/query-protocol-server mysql://user:password@localhost:3306/database

# Using NPX with environment variables
MYSQL_HOST=localhost MYSQL_USER=root MYSQL_PASSWORD=secret MYSQL_DATABASE=mydb npx @makeappeasy/query-protocol-server

# Using local build
node build/index.js mysql://user:password@localhost:3306/database

🛠️ Available Tools

1. connect_db

Establish or change database connection during runtime.

{
  "tool": "connect_db",
  "arguments": {
    "host": "localhost",
    "user": "readonly_user",
    "password": "secure_password", 
    "database": "analytics_db",
    "port": 3306
  }
}

2. query

Execute any read-only SQL query with full flexibility.

{
  "tool": "query",
  "arguments": {
    "sql": "SELECT users.name, COUNT(orders.id) as order_count FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.created_at >= ? GROUP BY users.id ORDER BY order_count DESC LIMIT 10",
    "params": ["2024-01-01"]
  }
}

Supported query types:

• ✅ SELECT - Data retrieval with joins, aggregations, subqueries
• ✅ SHOW - Database/table/column information
• ✅ DESCRIBE / DESC - Table structure
• ✅ EXPLAIN - Query execution plans
• ✅ ANALYZE TABLE - Table statistics
• ❌ INSERT, UPDATE, DELETE - Blocked by database
• ❌ DROP, CREATE, ALTER - Blocked by database
• ❌ GRANT, REVOKE, SET - Blocked by database

3. list_tables

Get all tables in the current database.

{
  "tool": "list_tables",
  "arguments": {}
}

4. describe_table

Get detailed table structure and column information.

{
  "tool": "describe_table", 
  "arguments": {
    "table": "users"
  }
}

💡 Perfect for Data Analysis

This server is specifically designed for AI-powered data analysis scenarios:

Complex Analytics Queries

-- Customer cohort analysis
SELECT 
  DATE_FORMAT(signup_date, '%Y-%m') as cohort_month,
  COUNT(DISTINCT user_id) as cohort_size,
  COUNT(DISTINCT CASE WHEN first_purchase_date IS NOT NULL THEN user_id END) as converted_users,
  ROUND(COUNT(DISTINCT CASE WHEN first_purchase_date IS NOT NULL THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) as conversion_rate
FROM user_analytics 
WHERE signup_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(signup_date, '%Y-%m')
ORDER BY cohort_month;

Business Intelligence Queries

-- Revenue analysis with trending
SELECT 
  product_category,
  SUM(CASE WHEN order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN revenue END) as last_30_days,
  SUM(CASE WHEN order_date >= DATE_SUB(NOW(), INTERVAL 60 DAY) AND order_date < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN revenue END) as prev_30_days,
  ROUND((SUM(CASE WHEN order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN revenue END) - 
         SUM(CASE WHEN order_date >= DATE_SUB(NOW(), INTERVAL 60 DAY) AND order_date < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN revenue END)) * 100.0 / 
         NULLIF(SUM(CASE WHEN order_date >= DATE_SUB(NOW(), INTERVAL 60 DAY) AND order_date < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN revenue END), 0), 2) as growth_rate
FROM order_analytics
GROUP BY product_category
HAVING last_30_days > 0
ORDER BY growth_rate DESC;

🔐 Security Features

Database-Level Protection

• MySQL Transaction Read-Only: Uses SET TRANSACTION READ ONLY for bulletproof security
• Automatic Rollback: Every query is rolled back, ensuring no persistent changes
• Isolation: Each query runs in its own isolated transaction

Application-Level Security

• Prepared Statements: All parameterized queries use MySQL prepared statements
• Connection Management: Proper connection lifecycle with automatic cleanup
• Error Handling: Sanitized error messages prevent information disclosure

Zero-Risk Architecture

• No Write Tools: Complete removal of any write-capable functionality
• Database Enforcement: Security is enforced by MySQL itself, not application logic
• Impossible Bypass: No way to circumvent read-only restrictions

🎯 Use Cases

✅ Ideal For

• Data exploration by AI assistants
• Business intelligence and analytics
• Database schema investigation
• Performance analysis with EXPLAIN queries
• Production database access for read-only operations
• Multi-user environments with sensitive data

❌ Not Suitable For

• Data modification or ETL operations
• Database administration tasks
• Schema changes or migrations
• User management or permissions

🏗️ Architecture

Built on modern, professional foundations:

• TypeScript - Full type safety and excellent developer experience
• ESModules - Modern JavaScript module system
• MCP SDK - Official Model Context Protocol implementation
• MySQL2 - Mature, performant MySQL driver with prepared statement support
• Professional Error Handling - Comprehensive error management and reporting

🚀 Development

# Development mode with auto-rebuild
npm run watch

# Build for production
npm run build

# Start the server
npm start

🔧 Troubleshooting

Claude Code Setup Issues

Path Problems:

# Get absolute path first
pwd
# Output: /Users/username/mysql-mcp

# Then use full path in claude command
claude mcp add mysql-readonly-server node /Users/username/mysql-mcp/build/index.js --env MYSQL_HOST=...

Connection Testing:

# Test connection with NPX
npx @makeappeasy/query-protocol-server mysql://user:pass@host:port/database
# Should show: "MySQL MCP server running on stdio"

# Test connection with local build
node build/index.js mysql://user:pass@host:port/database

Common Claude Code Commands:

# List configured servers
claude mcp list

# Remove server if needed
claude mcp remove query-protocol-server

# Re-add with NPX (recommended)
claude mcp add query-protocol-server npx @makeappeasy/query-protocol-server --env MYSQL_HOST=...

# Re-add with local build
claude mcp add query-protocol-server node /path/to/build/index.js --env MYSQL_HOST=...

📄 License

MIT - See LICENSE file for details.

🔒 Security First • 📊 Analytics Ready • 🛠️ Production Tested