GitHub - benborla/mcp-server-mysql: 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. (original) (raw)

MCP Server for MySQL based on NodeJS

smithery badge

Demo

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

Table of Contents

Requirements

Installation

There are several ways to install and configure the MCP server:

Claude Desktop

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": "your_password", "MYSQL_DB": "your_database", "ALLOW_INSERT_OPERATION": "false", "ALLOW_UPDATE_OPERATION": "false", "ALLOW_DELETE_OPERATION": "false", "PATH": "/Users/atlasborla/Library/Application Support/Herd/config/nvm/versions/node/v22.9.0/bin:/usr/bin:/bin", // <--- Important to add the following, run in your terminal echo "$(which node)/../" to get the path "NODE_PATH": "/Users/atlasborla/Library/Application Support/Herd/config/nvm/versions/node/v22.9.0/lib/node_modules" // <--- Important to add the following, run in your terminal echo "$(which node)/../../lib/node_modules" } } } }

Cursor

For Cursor IDE, you can install this MCP server with the following command in your project:

npx mcprunner -- MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASS=root MYSQL_DB=demostore ALLOW_INSERT_OPERATION=true ALLOW_UPDATE_OPERATION=true ALLOW_DELETE_OPERATION=false -- npx -y @benborla29/mcp-server-mysql

Don't forget to replace the env values on that command. If you have the latest version (for v0.47 and above) of Cursor, just copy and paste the config below:

mcp.json

{ "mcpServers": { "MySQL": { "command": "npx", "args": [ "mcprunner", "--", "MYSQL_HOST=127.0.0.1", "MYSQL_PORT=3306", "MYSQL_USER=root", "MYSQL_PASS=root", "MYSQL_DB=demostore", "ALLOW_INSERT_OPERATION=true", "ALLOW_UPDATE_OPERATION=true", "ALLOW_DELETE_OPERATION=false", "--", "npx", "-y", "@benborla29/mcp-server-mysql" ] } } }

Using Smithery

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

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:

The installation will ask for the following connection details:

For security reasons, write operations are disabled by default. Enable them only if you need Claude to modify your database data.

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).

Running from Local Repository

If you want to clone and run this MCP server directly from the source code, follow these steps:

  1. Clone the repository
    git clone https://github.com/benborla/mcp-server-mysql.git
    cd mcp-server-mysql
  2. Install dependencies
    npm install

or

pnpm install 3. Build the project
npm run build

or

pnpm run build 4. Configure Claude Desktop
Add the following to your Claude Desktop configuration file (claude_desktop_config.json):
{
"mcpServers": {
"mcp_server_mysql": {
"command": "/path/to/node",
"args": [
"/full/path/to/mcp-server-mysql/dist/index.js"
],
"env": {
"MYSQL_HOST": "127.0.0.1",
"MYSQL_PORT": "3306",
"MYSQL_USER": "root",
"MYSQL_PASS": "your_password",
"MYSQL_DB": "your_database",
"ALLOW_INSERT_OPERATION": "false",
"ALLOW_UPDATE_OPERATION": "false",
"ALLOW_DELETE_OPERATION": "false",
"PATH": "/Users/atlasborla/Library/Application Support/Herd/config/nvm/versions/node/v22.9.0/bin:/usr/bin:/bin", // <--- Important to add the following, run in your terminal echo "$(which node)/../" to get the path
"NODE_PATH": "/Users/atlasborla/Library/Application Support/Herd/config/nvm/versions/node/v22.9.0/lib/node_modules" // <--- Important to add the following, run in your terminal echo "$(which node)/../../lib/node_modules"
}
}
}
}
Replace:

  1. Test the server

Run the server directly to test

node dist/index.js
If it connects to MySQL successfully, you're ready to use it with Claude Desktop.

Components

Tools

Resources

The server provides comprehensive database information:

Security Features

Performance Optimizations

Monitoring and Debugging

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

When reconfiguring, you can update any of the MySQL connection details as well as the write operation settings:

For security reasons, all write operations are disabled by default. Only enable these settings if you specifically need Claude to modify your database data.

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",
    
    // Write operation flags
    "ALLOW_INSERT_OPERATION": "false",
    "ALLOW_UPDATE_OPERATION": "false",
    "ALLOW_DELETE_OPERATION": "false"
  }
}

} }

Environment Variables

Basic Connection

Performance Configuration

Security Configuration

Monitoring Configuration

Multi-DB Mode

MCP-Server-MySQL supports connecting to multiple databases when no specific database is set. This allows the LLM to query any database the MySQL user has access to. For full details, see README-MULTI-DB.md.

Enabling Multi-DB Mode

To enable multi-DB mode, simply leave the MYSQL_DB environment variable empty. In multi-DB mode, queries require schema qualification:

-- Use fully qualified table names SELECT * FROM database_name.table_name;

-- Or use USE statements to switch between databases USE database_name; SELECT * FROM table_name;

Schema-Specific Permissions

For fine-grained control over database operations, MCP-Server-MySQL now supports schema-specific permissions. This allows different databases to have different levels of access (read-only, read-write, etc.).

Configuration Example

SCHEMA_INSERT_PERMISSIONS=development:true,test:true,production:false
SCHEMA_UPDATE_PERMISSIONS=development:true,test:true,production:false
SCHEMA_DELETE_PERMISSIONS=development:false,test:true,production:false
SCHEMA_DDL_PERMISSIONS=development:false,test:true,production:false

For complete details and security recommendations, see README-MULTI-DB.md.

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 EnvironmentCreate a .env.test file in the project root (if not existing):
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 ScriptsAdd 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

Running evals

The evals package loads an mcp client that then runs the index.ts file, so there is no need to rebuild between tests. You can load environment variables by prefixing the npx command. Full documentation can be found here.

OPENAI_API_KEY=your-key npx mcp-eval evals.ts index.ts

Troubleshooting

Common Issues

  1. Connection Issues
    • Verify MySQL server is running and accessible
    • Check credentials and permissions
    • Ensure SSL/TLS configuration is correct if enabled
    • Try connecting with a MySQL client to confirm access
  2. Performance Issues
    • Adjust connection pool size
    • Configure query timeout values
    • Enable query caching if needed
    • Check query complexity settings
    • Monitor server resource usage
  3. Security Restrictions
    • Review rate limiting configuration
    • Check query whitelist/blacklist settings
    • Verify SSL/TLS settings
    • Ensure the user has appropriate MySQL permissions
  4. Path ResolutionIf 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" } }

_Where can I find my node bin path_Run the following command to get it:

For PATH

For NODE_PATH

echo "$(which node)/../../lib/node_modules"

  1. Claude Desktop Specific Issues
    • If you see "Server disconnected" logs in Claude Desktop, check the logs at ~/Library/Logs/Claude/mcp-server-mcp_server_mysql.log
    • Ensure you're using the absolute path to both the Node binary and the server script
    • Check if your .env file is being properly loaded; use explicit environment variables in the configuration
    • Try running the server directly from the command line to see if there are connection issues
    • If you need write operations (INSERT, UPDATE, DELETE), set the appropriate flags to "true" in your configuration:
      "env": {
      "ALLOW_INSERT_OPERATION": "true", // Enable INSERT operations
      "ALLOW_UPDATE_OPERATION": "true", // Enable UPDATE operations
      "ALLOW_DELETE_OPERATION": "true" // Enable DELETE operations
      }
    • Ensure your MySQL user has the appropriate permissions for the operations you're enabling
    • For direct execution configuration, use:
      {
      "mcpServers": {
      "mcp_server_mysql": {
      "command": "/full/path/to/node",
      "args": [
      "/full/path/to/mcp-server-mysql/dist/index.js"
      ],
      "env": {
      "MYSQL_HOST": "127.0.0.1",
      "MYSQL_PORT": "3306",
      "MYSQL_USER": "root",
      "MYSQL_PASS": "your_password",
      "MYSQL_DB": "your_database"
      }
      }
      }
      }
  2. Authentication Issues
    • For MySQL 8.0+, ensure the server supports the caching_sha2_password authentication plugin
    • Check if your MySQL user is configured with the correct authentication method
    • Try creating a user with legacy authentication if needed:
      CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
      @lizhuangs
  3. I am encountering Error [ERR_MODULE_NOT_FOUND]: Cannot find package 'dotenv' imported from error try this workaround:

npx -y -p @benborla29/mcp-server-mysql -p dotenv mcp-server-mysql

Thanks to @lizhuangs

Contributing

Contributions are welcome! Please feel free to submit a Pull Request tohttps://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 for details on planned features, including:

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.