MCP Server for MySQL based on NodeJS

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.

Installation

Using Smithery

The easiest way to install and configure this MCP server is through Smithery:

# Install the MCP server
npx -y @smithery/cli@latest install @benborla29/mcp-server-mysql --client claude

During configuration, you'll be prompted to enter your MySQL connection details. Smithery will automatically:

• Set up the correct environment variables
• Configure your LLM application to use the MCP server
• Test the connection to your MySQL database
• Provide helpful troubleshooting if needed

Using MCP Get

You can also install this package using MCP Get:

npx @michaellatman/mcp-get@latest install @benborla29/mcp-server-mysql

MCP Get provides a centralized registry of MCP servers and simplifies the installation process.

Using NPM/PNPM

For manual installation:

# Using npm
npm install -g @benborla29/mcp-server-mysql

# Using pnpm
pnpm add -g @benborla29/mcp-server-mysql

After manual installation, you'll need to configure your LLM application to use the MCP server (see Configuration section below).

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

Configuration

Automatic Configuration with Smithery

If you installed using Smithery, your configuration is already set up. You can view or modify it with:

smithery configure @benborla29/mcp-server-mysql

Manual Configuration for Claude Desktop App

To manually configure the MCP server for Claude Desktop App, add the following to your `claude_desktop_config.json` file (typically located in your user directory):

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "@benborla29/mcp-server-mysql"
      ],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "",
        "MYSQL_DB": "db_name"
      }
    }
  }
}

Replace `db_name` with your database name or leave it blank to access all databases.

Advanced Configuration Options

For more control over the MCP server's behavior, you can use these advanced configuration options:

{
  "mcpServers": {
    "mcp_server_mysql": {
      "command": "/path/to/npx/binary/npx",
      "args": [
        "-y",
        "@benborla29/mcp-server-mysql"
      ],
      "env": {
        // Basic connection settings
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "",
        "MYSQL_DB": "db_name",
        "PATH": "/path/to/node/bin:/usr/bin:/bin",
        
        // Performance settings
        "MYSQL_POOL_SIZE": "10",
        "MYSQL_QUERY_TIMEOUT": "30000",
        "MYSQL_CACHE_TTL": "60000",
        
        // Security settings
        "MYSQL_RATE_LIMIT": "100",
        "MYSQL_MAX_QUERY_COMPLEXITY": "1000",
        "MYSQL_SSL": "true",
        
        // Monitoring settings
        "MYSQL_ENABLE_LOGGING": "true",
        "MYSQL_LOG_LEVEL": "info",
        "MYSQL_METRICS_ENABLED": "true"
      }
    }
  }
}

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

Troubleshooting

Using Smithery for Troubleshooting

If you installed with Smithery, you can use its built-in diagnostics:

# Check the status of your MCP server
smithery status @benborla29/mcp-server-mysql

# Run diagnostics
smithery diagnose @benborla29/mcp-server-mysql

# View logs
smithery logs @benborla29/mcp-server-mysql

Using MCP Get for Troubleshooting

If you installed with MCP Get:

# Check the status
mcp-get status @benborla29/mcp-server-mysql

# View logs
mcp-get logs @benborla29/mcp-server-mysql

Common Issues

1. Connection Issues

2. Performance Issues

3. Security Restrictions

4. Path Resolution

If you encounter an error "Could not connect to MCP server mcp-server-mysql", explicitly set the path of all required binaries:

{
  "env": {
    "PATH": "/path/to/node/bin:/usr/bin:/bin"
  }
}

5. Authentication Issues

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request to 
https://github.com/benborla/mcp-server-mysql

### Development Setup

1. Clone the repository
2. Install dependencies: `pnpm install`
3. Build the project: `pnpm run build`
4. Run tests: `pnpm test`

### Project Roadmap

We're actively working on enhancing this MCP server. Check our [CHANGELOG.md](./CHANGELOG.md) for details on planned features, including:

- Enhanced query capabilities with prepared statements
- Advanced security features
- Performance optimizations
- Comprehensive monitoring
- Expanded schema information

If you'd like to contribute to any of these areas, please check the issues on GitHub or open a new one to discuss your ideas.

### Submitting Changes

1. Fork the repository
2. Create a feature branch: `git checkout -b feature/your-feature-name`
3. Commit your changes: `git commit -am 'Add some feature'`
4. Push to the branch: `git push origin feature/your-feature-name`
5. Submit a pull request

## License

This MCP server is licensed under the MIT License. See the LICENSE file for details.