# MCP Server for MySQL based on NodeJS
[](https://smithery.ai/server/@zhengyun1008/mcp-server-mysql)
A Model Context Protocol server that provides access to MySQL databases with fine-grained access control. Supports **multiple databases**, **access modes**, and **table-level permissions**.
## Features
- **Multi-database support**: Connect to multiple MySQL databases simultaneously
- **Access control**: `readonly`, `readwrite`, `full` modes per database
- **Table-level permissions**: Whitelist/blacklist with wildcards and regex
- **Read-only transaction protection**: Database-level safety for readonly mode
- **Backward compatible**: Works with single database environment variables
## Components
### Tools
| Tool | Description |
|------|-------------|
| `mysql_query` | Read-only SELECT queries (always available) |
| `mysql_execute` | Write operations (only visible when writable databases exist) |
### Resources
- **mysql://connections**: List of database connections with access modes
- **mysql://{db}/tables/{table}/schema**: Table schema
### Prompts
- **mysql_usage_guide**: Dynamic guide based on configured databases
## Configuration
### Config File
Create `~/.mcp_mysql/databases.json`:
```json
{
"databases": [
{
"name": "prod",
"host": "prod-db.example.com",
"port": 3306,
"user": "readonly_user",
"password": "***",
"database": "production",
"accessMode": "readonly"
},
{
"name": "dev",
"host": "localhost",
"port": 3306,
"user": "dev_user",
"password": "***",
"database": "development",
"accessMode": "readwrite",
"allowedTables": ["log_*", "tmp_*", "/^test_.*/i"]
}
]
}
```
### Access Modes
| Mode | SELECT | INSERT/UPDATE/DELETE | DDL |
|------|--------|---------------------|-----|
| `readonly` (default) | ✅ | ❌ | ❌ |
| `readwrite` | ✅ | ✅ | ❌ |
| `full` | ✅ | ✅ | ✅ |
### Table Patterns
| Format | Example | Matches |
|--------|---------|---------|
| Exact | `log_table` | Only `log_table` |
| Wildcard | `log_*` | `log_` prefix |
| Regex | `/^test_\d+$/i` | `test_` + digits |
### Environment Variables
| Variable | Description |
|----------|-------------|
| `MYSQL_CONFIG_PATH` | Custom config file path |
| `MYSQL_HOST` | Legacy single-database host |
| `MYSQL_PORT` | Legacy single-database port |
| `MYSQL_USER` | Legacy single-database user |
| `MYSQL_PASS` | Legacy single-database password |
| `MYSQL_DB` | Legacy single-database name |
## Usage with Claude Desktop
### Multi-database configuration (Recommended)
First, create `~/.mcp_mysql/databases.json` with your database configurations.
Then add to your `claude_desktop_config.json`:
```json
{
"mcpServers": {
"mcp_server_mysql": {
"command": "npx",
"args": [
"-y",
"@zhengyun1008/mcp-server-mysql"
]
}
}
}
```
### Single database (Legacy, backward compatible)
```json
{
"mcpServers": {
"mcp_server_mysql": {
"command": "npx",
"args": [
"-y",
"@zhengyun1008/mcp-server-mysql"
],
"env": {
"MYSQL_HOST": "127.0.0.1",
"MYSQL_PORT": "3306",
"MYSQL_USER": "root",
"MYSQL_PASS": "",
"MYSQL_DB": "db_name"
}
}
}
}
```
## Usage Examples
```
User: "查询 prod 库中的用户表"
→ mysql_query(database="prod", sql="SELECT * FROM users")
User: "在 dev 库的 log_test 表插入一条记录"
→ mysql_execute(database="dev", sql="INSERT INTO log_test ...", confirm=true)
```
## Security
- **readonly mode**: Uses `SET SESSION TRANSACTION READ ONLY` for database-level protection
- **SQL validation**: Validates statement type before execution
- **Table validation**: Checks whitelist/blacklist before write operations
- **Confirmation required**: Write operations require `confirm=true`
## Troubleshooting
If you encounter an error "Could not connect to MCP server mcp-server-mysql", you may need to explicitly
set the path of all required binaries such as the configuration below:
```json
{
"mcpServers": {
"mcp_server_mysql": {
"command": "/path/to/npx/binary/npx",
"args": [
"-y",
"@zhengyun1008/mcp-server-mysql"
],
"env": {
"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"
}
}
}
}
```
## License
This MCP server is licensed under the MIT License. This means you are free to use, modify, and distribute the software, subject to the terms and conditions of the MIT License. For more details, please see the LICENSE file in the project repository.
