MySQL MCP Server

MySQL MCP Server

A comprehensive MySQL Model Context Protocol (MCP) server that provides database operations for Cursor IDE.

Features

• ✅ Connection Management: Connect to any MySQL database
• ✅ Schema Operations: List databases, tables, describe table structures
• ✅ Data Operations: SELECT, INSERT, UPDATE, DELETE operations
• ✅ Advanced Features: View indexes, foreign keys, execute custom queries
• ✅ Safety: Built-in query limits and error handling
• 🔒 Production Security: Automatic PII protection and audit logging (production environment only)

Prerequisites

Before setting up this MySQL MCP server, ensure you have:

1. Node.js (v16 or higher) - Required to run the MCP server
2. MySQL Database - Either local or remote MySQL server access
3. Cursor IDE - For MCP integration

📁 Repository Note: This repository follows Node.js best practices by excluding node_modules/ and dist/ folders. You'll generate these locally after cloning.

Quick Setup

🚀 First Time Setup (After Cloning)

Important: This repository does not include node_modules/ or dist/ folders to keep it lightweight. You'll need to generate them:

1. Clone the repository:

   git clone https://github.com/binoy154/mysql-mcp.git
   cd mysql-mcp

⚠️ Important Path Considerations:

• Note the absolute path where you clone this repository (e.g., C:/Projects/mysql-mcp or E:/mysql-mcp)
• You'll need this exact path for Cursor MCP configuration later
• The built files will be at YOUR_PATH/dist/index.js

1. Install dependencies:

   npm install

This will create the node_modules/ folder with all required packages.

1. Build the project:

   npm run build

This will create the dist/ folder with compiled JavaScript files.

1. Set up environment variables (optional):

   cp .env.example .env
   # Edit .env with your MySQL credentials

Note: You can also configure credentials directly in Cursor MCP settings.

1. Configure Cursor MCP:

🔧 IMPORTANT: Update the file path below with your actual clone location!

A single MCP server instance can switch between local, staging, preproduction and production environments at runtime. The local environment allows full read/write access, while staging, preproduction, and production are configured as read-only for safety. Supply their credentials through environment variables:

   {
     "mcpServers": {
       "mysql": {
         "command": "node",
         "args": ["YOUR_ABSOLUTE_PATH/mysql-mcp/dist/index.js"],
         "env": {
           "MYSQL_LOCAL_HOST": "localhost",
           "MYSQL_LOCAL_PORT": "3306",
           "MYSQL_LOCAL_USER": "root",
           "MYSQL_LOCAL_PASSWORD": "localPassword",
           "MYSQL_LOCAL_DATABASE": "localDatabase",

           "MYSQL_STAGING_HOST": "staging.db.example.com",
           "MYSQL_STAGING_PORT": "3306",
           "MYSQL_STAGING_USER": "staging_user",
           "MYSQL_STAGING_PASSWORD": "staging_pw",
           "MYSQL_STAGING_DATABASE": "staging_db",

           "MYSQL_PREPRODUCTION_HOST": "preprod-db.example.com",
           "MYSQL_PREPRODUCTION_PORT": "3306",
           "MYSQL_PREPRODUCTION_USER": "preprod_user",
           "MYSQL_PREPRODUCTION_PASSWORD": "preprod_pw",
           "MYSQL_PREPRODUCTION_DATABASE": "preprod_db"
         }
       }
     }
   }

📝 Path Examples - Replace YOUR_ABSOLUTE_PATH/mysql-mcp with:

• Windows: "C:/Projects/mysql-mcp/dist/index.js"
• Windows (E drive): "E:/mysql-mcp/dist/index.js"
• Mac/Linux: "/home/username/mysql-mcp/dist/index.js"

⚠️ Critical: Use forward slashes (/) even on Windows, and ensure the path points to where you actually cloned the repository.

Database Safety: The production credentials should point to a read-only slave or replica to guarantee safety. Additionally, staging and preproduction environments are enforced as read-only at the code level to prevent accidental data modifications.

Available Tools

Connection Tools

• connect_database: Connect with custom credentials
• list_databases: Show all available databases
• use_database: Switch to a specific database
• switch_environment: Switch the active DB environment (local, staging, preproduction, production)

Schema Tools

• list_tables: List all tables in current database
• describe_table: Get detailed table schema
• get_table_indexes: View table indexes
• get_foreign_keys: View foreign key relationships

Data Operations

• select_query: Execute SELECT queries with automatic LIMIT
• insert_data: Insert new records
• update_data: Update existing records
• delete_data: Delete records
• execute_query: Execute any SQL query (use carefully)

Write operations & permission levels

| Tool | Local | Staging | Pre-production | Production | |------|-------|---------|----------------|------------| | insert_data, update_data, delete_data, execute_query | ✅ Immediate | ❌ READ-ONLY | ❌ READ-ONLY | ❌ READ-ONLY |

Note: Only the local environment allows write operations. All other environments (staging, preproduction, production) are configured as read-only for data safety and integrity.

Example (local only):

{
  "name": "update_data",
  "arguments": {
    "table": "users",
    "data": { "status": "active" },
    "where": "id = 42"
  }
}

Usage Examples

Connect to Database

Use the connect_database tool with:
- host: localhost
- port: 3306
- user: myuser
- password: mypassword
- database: myapp_db

Query Data

Use select_query with query: "SELECT * FROM users WHERE active = 1"

Insert Data

Use insert_data with:
- table: "users"
- data: {"name": "John Doe", "email": "john@example.com"}

Security Notes

1. Store database credentials securely using environment variables
2. SELECT queries automatically include LIMIT 100 unless specified
3. The server uses parameterized queries for data operations
4. Ensure your MySQL user has appropriate permissions

🔒 Production Security Features

Automatic PII Protection (Production Environment Only):

• Sensitive Data Masking: Columns containing PII (SIN, birth dates, credit cards, etc.) are automatically masked
• Audit Logging: All production database access is logged for compliance
• Zero Impact on Development: Local, staging, and preproduction environments work exactly as before
• Smart Detection: Uses pattern matching to identify sensitive columns (sin, ssn, birth_date, etc.)

Environment Behavior:

• Local/Staging/Preproduction: Full data access (no filtering)
• Production: Automatic sensitive data masking + audit logging

Example Output in Production: ``json { "id": 1, "name": "John Doe", "email": "jo**@example.com", "sin": "128", "birth_date": "19--**" } ``

Security Status Messages:

• 🔓 DEVELOPMENT MODE: No data filtering (full access)
• 🔒 PRODUCTION MODE: Sensitive data protection active

Deployment to Another Computer

❌ What Won't Work

Simply copying the project folder to another computer will not work without proper setup.

✅ Required Steps for New Installation

1. Install Prerequisites:

• Node.js (v16 or higher)
• Access to a MySQL database

1. Clone or Copy Project Files:

   git clone https://github.com/binoy154/mysql-mcp.git
   cd mysql-mcp

• Note the absolute path where you cloned/copied the project
• You'll need this exact path for Cursor MCP configuration

1. Install Dependencies:

   npm install

1. Build the Project:

   npm run build

1. Update Cursor Configuration:

• Update the file path in your Cursor MCP settings to match the new location
• Example: Change "E:/mySqlMcp/dist/index.js" to the correct path on the new computer

1. Configure Database Credentials:

• Update environment variables or Cursor MCP configuration with the correct database connection details for the new environment

📁 What's Included vs. What You Need to Build

✅ Included in this repository:

• Source code (src/ folder)
• Configuration files (package.json, tsconfig.json, README.md)
• Documentation files
• .gitignore (protects against accidentally committing large folders)

🔨 What you need to build after cloning:

• node_modules/ folder → Run npm install
• dist/ folder → Run npm run build

⚙️ What you need to customize:

• Database credentials (host, port, username, password)
• File paths in Cursor MCP configuration
• Environment-specific settings

💡 Why this approach?

• Keeps repository lightweight (MB instead of GB)
• Avoids platform-specific dependency issues
• Follows Node.js best practices

Troubleshooting

Setup Issues

• "Cannot find module" errors: Run npm install to install dependencies
• "Cannot find dist/index.js": Run npm run build to compile TypeScript
• Missing node_modules/ folder: This is normal! Run npm install to create it
• Missing dist/ folder: This is normal! Run npm run build to create it

Connection Issues

• Verify MySQL server is running
• Check host, port, username, and password
• Ensure user has necessary database permissions

Permission Errors

Grant appropriate MySQL privileges: ``sql GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'your_user'@'localhost'; FLUSH PRIVILEGES; ``

Deployment Issues

• Ensure Node.js is installed on the target computer
• Verify the file path in Cursor MCP configuration is correct
• Check that npm install and npm run build completed successfully
• Confirm database connectivity from the new computer