Labsco
zhaoxin34 logo

MySQL Server

★ 1

from zhaoxin34

Provides read-only access to MySQL databases, allowing LLMs to inspect schemas and execute queries.

🔥🔥🔥✓ VerifiedFreeAdvanced setup
#automation#database#git#security#monitoring

MCP Server for MySQL based on NodeJS

smithery badge

Demo

A Model Context Protocol server that provides read-only access to MySQL databases. This server enables LLMs to inspect database schemas and execute read-only queries.

Components

Tools

  • mysql_query
    • Execute read-only SQL queries against the connected database
    • Input: sql (string): The SQL query to execute
    • All queries are executed within a READ ONLY transaction
    • Supports prepared statements for secure parameter handling
    • Configurable query timeouts and result pagination
    • Built-in query execution statistics

Resources

The server provides comprehensive database information:

  • Table Schemas
    • JSON schema information for each table
    • Column names and data types
    • Index information and constraints
    • Foreign key relationships
    • Table statistics and metrics
    • Automatically discovered from database metadata

Security Features

  • SQL injection prevention through prepared statements
  • Query whitelisting/blacklisting capabilities
  • Rate limiting for query execution
  • Query complexity analysis
  • Configurable connection encryption
  • Read-only transaction enforcement

Performance Optimizations

  • Optimized connection pooling
  • Query result caching
  • Large result set streaming
  • Query execution plan analysis
  • Configurable query timeouts

Monitoring and Debugging

  • Comprehensive query logging
  • Performance metrics collection
  • Error tracking and reporting
  • Health check endpoints
  • Query execution statistics

Environment Variables

Basic Connection

  • MYSQL_HOST: MySQL server host (default: "127.0.0.1")
  • MYSQL_PORT: MySQL server port (default: "3306")
  • MYSQL_USER: MySQL username (default: "root")
  • MYSQL_PASS: MySQL password
  • MYSQL_DB: Target database name

Performance Configuration

  • MYSQL_POOL_SIZE: Connection pool size (default: "10")
  • MYSQL_QUERY_TIMEOUT: Query timeout in milliseconds (default: "30000")
  • MYSQL_CACHE_TTL: Cache time-to-live in milliseconds (default: "60000")

Security Configuration

  • MYSQL_RATE_LIMIT: Maximum queries per minute (default: "100")
  • MYSQL_MAX_QUERY_COMPLEXITY: Maximum query complexity score (default: "1000")
  • MYSQL_SSL: Enable SSL/TLS encryption (default: "false")

Monitoring Configuration

  • MYSQL_ENABLE_LOGGING: Enable query logging (default: "false")
  • MYSQL_LOG_LEVEL: Logging level (default: "info")
  • MYSQL_METRICS_ENABLED: Enable performance metrics (default: "false")

Testing

Database Setup

Before running tests, you need to set up the test database and seed it with test data:

  1. Create Test Database and User

    -- Connect as root and create test database
CREATE DATABASE IF NOT EXISTS mcp_test;

-- Create test user with appropriate permissions
CREATE USER IF NOT EXISTS 'mcp_test'@'localhost' IDENTIFIED BY 'mcp_test_password';
GRANT ALL PRIVILEGES ON mcp_test.* TO 'mcp_test'@'localhost';
FLUSH PRIVILEGES;

  2. Run Database Setup Script

    # Run the database setup script
pnpm run setup:test:db

    This will create the necessary tables and seed data. The script is located in scripts/setup-test-db.ts

  3. Configure Test Environment Create a .env.test file in the project root:

    MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=mcp_test
MYSQL_PASS=mcp_test_password
MYSQL_DB=mcp_test

  4. Update package.json Scripts Add these scripts to your package.json:

    {
  "scripts": {
    "setup:test:db": "ts-node scripts/setup-test-db.ts",
    "pretest": "pnpm run setup:test:db",
    "test": "vitest run",
    "test:watch": "vitest",
    "test:coverage": "vitest run --coverage"
  }
}

Running Tests

The project includes a comprehensive test suite to ensure functionality and reliability:

# First-time setup
pnpm run setup:test:db

# Run all tests
pnpm test