mysql-mcp-server-copilot

Lakshya-Saini/mysql-mcp-server-copilot

3.2

If you are the rightful owner of mysql-mcp-server-copilot and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

A secure and configurable MySQL MCP server that enables AI agents to interact with MySQL databases safely and efficiently.

Tools
9
Resources
0
Prompts
0

MySQL MCP Server

A secure and configurable MySQL MCP (Model Context Protocol) server that enables AI agents to interact with MySQL databases safely and efficiently.

Features

  • 🔒 Secure by Default: Only fetch operations are enabled by default
  • ⚙️ Configurable Operations: Enable create, update, delete operations through configuration
  • 📝 Comprehensive Logging: Detailed logging for debugging and monitoring
  • 📦 NPM Package: Ready to use with VS Code GitHub Copilot and other MCP clients
  • 🛡️ Security Standards: Input validation, SQL injection prevention, connection security
  • 📊 Agent-Friendly Responses: Structured responses optimized for tabular data display
  • 🔌 Connection Pooling: Efficient database connection management
  • ✅ TypeScript: Full TypeScript support with strict type checking

Installation & Setup

1. VS Code with GitHub Copilot

The easiest way to use this MySQL MCP server is with VS Code and GitHub Copilot:

  1. Install VS Code and the GitHub Copilot extension

  2. Install the package globally:

    npm install -g @lakshya-mcp/mysql-mcp-server-copilot

  3. Configure MCP settings by creating/editing your mcp-config.json:

    {
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@lakshya-mcp/mysql-mcp-server-copilot"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database",
        "DB_SSL": "true",
        "ENABLE_CREATE": "false",
        "LOG_LEVEL": "info"
      }
    }
  }
}

  4. Restart VS Code and start asking Copilot about your database!

2. Claude Desktop

For Claude Desktop users:

  1. Install the package:

    npm install -g @lakshya-mcp/mysql-mcp-server-copilot

  2. Add to Claude Desktop config (claude_desktop_config.json):

    {
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@lakshya-mcp/mysql-mcp-server-copilot"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

  3. Restart Claude Desktop to load the MCP server

3. Manual/Any MCP Client

For any MCP-compatible client:

  1. Install globally or locally:

    # Global installation
npm install -g @lakshya-mcp/mysql-mcp-server-copilot

# Local installation
npm install @lakshya-mcp/mysql-mcp-server-copilot

  2. Run directly:

    npx @lakshya-mcp/mysql-mcp-server-copilot

  3. Configure your MCP client to connect to the server using stdio transport

4. Programmatic Usage (NPM)

For programmatic integration in your Node.js applications:

npm install @lakshya-mcp/mysql-mcp-server-copilot

import { MySQLMCPServer } from "@lakshya-mcp/mysql-mcp-server-copilot";

const server = new MySQLMCPServer({
  host: "localhost",
  port: 3306,
  user: "username",
  password: "password",
  database: "mydb",
  features: {
    create: false,
    update: false,
    delete: false,
    schema: false,
  },
});

await server.start();

5. Local Development

To run from the local repository:

  1. Clone the repository:

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

  2. Install dependencies:

    npm install

  3. Build the project:

    npm run build

  4. Set up environment:

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

  5. Run locally:

    npm start

  6. For development with auto-rebuild:

    npm run dev

Configuration

Create a .env file or set environment variables:

# Database Configuration (Required)
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

# Security Configuration
DB_SSL=true
DB_CONNECTION_LIMIT=10
DB_TIMEOUT=30000

# Feature Configuration (Optional - defaults to false except fetch)
ENABLE_CREATE=false
ENABLE_UPDATE=false
ENABLE_DELETE=false
ENABLE_SCHEMA_OPERATIONS=false

# Logging Configuration
LOG_LEVEL=info
LOG_FILE=./logs/mysql-mcp-server.log

VS Code GitHub Copilot Integration

Add to your MCP settings (mcp-config.json):

{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@lakshya-mcp/mysql-mcp-server-copilot"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database",
        "ENABLE_CREATE": "false",
        "LOG_LEVEL": "info"
      }
    }
  }
}
Usage Examples

Ask GitHub Copilot natural language questions:

  • Basic Queries: "Show me all users from the database", "What's the structure of the users table?"
  • Data Analysis: "Show me the top 10 customers by order value", "What's the average age of our users?"
  • Schema Exploration: "Describe the structure of the orders table", "What are all the tables in this database?"
Security Best Practices

  1. Use Read-Only by Default:

    ENABLE_CREATE=false
ENABLE_UPDATE=false
ENABLE_DELETE=false
ENABLE_SCHEMA_OPERATIONS=false

  2. Create Dedicated Database User:

    -- Read-only user
CREATE USER 'copilot_readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON your_database.* TO 'copilot_readonly'@'%';
FLUSH PRIVILEGES;

  3. Enable SSL/TLS:

    DB_SSL=true
DB_CONNECTION_LIMIT=5

Programmatically:

import { MySQLMCPServer } from "@lakshya-mcp/mysql-mcp-server-copilot";

const server = new MySQLMCPServer({
  host: "localhost",
  port: 3306,
  user: "username",
  password: "password",
  database: "mydb",
  features: {
    create: false,
    update: false,
    delete: false,
    schema: false,
  },
});

await server.start();

Available Tools

Always Available:

  • mysql_query - Execute SELECT queries safely
  • mysql_describe_table - Get table structure
  • mysql_list_tables - List all tables in database
  • mysql_show_databases - List available databases

Configurable Tools:

  • mysql_insert - Insert data (requires ENABLE_CREATE=true)
  • mysql_update - Update data (requires ENABLE_UPDATE=true)
  • mysql_delete - Delete data (requires ENABLE_DELETE=true)
  • mysql_create_table - Create tables (requires ENABLE_SCHEMA_OPERATIONS=true)
  • mysql_drop_table - Drop tables (requires ENABLE_SCHEMA_OPERATIONS=true)

Security Features

  • SQL injection prevention through parameterized queries
  • Input validation using Joi schemas
  • Connection pooling with limits
  • SSL/TLS support
  • Query timeout protection
  • Feature-based access control

Troubleshooting

Connection Issues

  1. Check MySQL Server Status:

    # Windows
Get-Service MySQL*

# Linux
systemctl status mysql

# macOS
brew services list mysql

  2. Verify Credentials:

    mysql -h localhost -u your_username -p your_database

  3. Check Firewall: Ensure port 3306 is accessible

Permission Issues

  1. Check User Permissions:

    SHOW GRANTS FOR 'your_username'@'%';

  2. Verify Database Access:

    USE your_database;
SHOW TABLES;

Debug Mode

Enable detailed logging:

LOG_LEVEL=debug

Check logs:

tail -f logs/mysql-mcp-server.log

Advanced Configuration

Multiple Database Connections

Configure separate instances for different databases:

{
  "mcpServers": {
    "mysql-production": {
      "command": "npx",
      "args": ["@lakshya-mcp/mysql-mcp-server-copilot"],
      "env": {
        "DB_HOST": "prod-server",
        "DB_NAME": "production_db",
        "ENABLE_CREATE": "false"
      }
    },
    "mysql-development": {
      "command": "npx",
      "args": ["@lakshya-mcp/mysql-mcp-server-copilot"],
      "env": {
        "DB_HOST": "localhost",
        "DB_NAME": "dev_db",
        "ENABLE_CREATE": "true"
      }
    }
  }
}

Environment-Specific Settings

Development:

DB_SSL=false
ENABLE_CREATE=true
ENABLE_UPDATE=true
ENABLE_DELETE=true
ENABLE_SCHEMA_OPERATIONS=true
LOG_LEVEL=debug

Production:

DB_SSL=true
ENABLE_CREATE=false
ENABLE_UPDATE=false
ENABLE_DELETE=false
ENABLE_SCHEMA_OPERATIONS=false
LOG_LEVEL=info

Contributing

  1. Fork the repository
  2. Create a feature branch
  3. Make your changes
  4. Add tests
  5. Submit a pull request

License

MIT License - see LICENSE file for details.