Content
# MySQL Query Server
---
## 1. Project Introduction
This project is a MySQL query server based on the MCP framework, supporting real-time database operations via SSE protocol. It features comprehensive security, logging, configuration, and sensitive information protection mechanisms, suitable for secure MySQL data access in development, testing, and production environments.
---
## 2. Key Features
- Built on FastMCP framework, high-performance async
- Connection pool for high concurrency, with flexible parameter tuning
- SSE real-time push support
- Rich MySQL metadata & schema query APIs
- Automatic transaction management & rollback
- Multi-level SQL risk control & injection protection
- **Database Isolation Security**: Prevents cross-database access with 3-level access control
- Automatic and customizable sensitive info masking
- Flexible environment variable configuration
- Robust logging & error handling
- Docker support for quick deployment
---
## 3. Quick Start
### Docker Method
```bash
# 拉取镜像
docker pull mangooer/mysql-mcp-server-sse:latest
# 运行容器
docker run -d \
--name mysql-mcp-server-sse \
-e HOST=0.0.0.0 \
-e PORT=3000 \
-e MYSQL_HOST=your_mysql_host \
-e MYSQL_PORT=3306 \
-e MYSQL_USER=your_mysql_user \
-e MYSQL_PASSWORD=your_mysql_password \
-e MYSQL_DATABASE=your_database \
-p 3000:3000 \
mangooer/mysql-mcp-server-sse:latest
```
Windows PowerShell 格式：
```powershell
docker run -d `
--name mysql-mcp-server-sse `
-e HOST=0.0.0.0 `
-e PORT=3000 `
-e MYSQL_HOST=your_mysql_host `
-e MYSQL_PORT=3306 `
-e MYSQL_USER=your_mysql_user `
-e MYSQL_PASSWORD=your_mysql_password `
-e MYSQL_DATABASE=your_database `
-p 3000:3000 `
mangooer/mysql-mcp-server-sse:latest
```
### Source Code Method
#### Install Dependencies
```bash
pip install -r requirements.txt
```
#### Configure Environment Variables
Copy `.env.example` to `.env` and modify as needed.
#### Start the Server
```bash
python -m src.server
```
Default endpoint: http://127.0.0.1:3000/sse
---
## 4. Project Structure
```
.
├── src/
│ ├── server.py # Main server entry
│ ├── config.py # Config definitions
│ ├── validators.py # Parameter validation
│ ├── db/
│ │ └── mysql_operations.py # DB operations
│ ├── security/
│ │ ├── interceptor.py # SQL interception
│ │ ├── query_limiter.py # Risk control
│ │ └── sql_analyzer.py # SQL analysis
│ └── tools/
│ ├── mysql_tool.py # Basic query
│ ├── mysql_metadata_tool.py # Metadata query
│ ├── mysql_info_tool.py # Info query
│ ├── mysql_schema_tool.py # Schema query
│ └── metadata_base_tool.py # Tool base class
├── tests/ # Tests
├── .env.example # Env example
└── requirements.txt # Requirements
```
---
## 5. Environment Variables & Configuration
| 变量名 / Variable | 说明 / Description | 默认值 / Default |
|--------------------------|------------------------------------------------------|------------------|
| HOST | 服务器监听地址 / Server listen address | 127.0.0.1 |
| PORT | 服务器监听端口 / Server listen port | 3000 |
| MYSQL_HOST | MySQL服务器地址 / MySQL server host | localhost |
| MYSQL_PORT | MySQL服务器端口 / MySQL server port | 3306 |
| MYSQL_USER | MySQL用户名 / MySQL username | root |
| MYSQL_PASSWORD | MySQL密码 / MySQL password | (空/empty) |
| MYSQL_DATABASE | 要连接的数据库名 / Database name | (空/empty) |
| DB_CONNECTION_TIMEOUT | 连接超时时间(秒) / Connection timeout (seconds) | 5 |
| DB_AUTH_PLUGIN | 认证插件类型 / Auth plugin type | mysql_native_password |
| DB_POOL_ENABLED | 是否启用连接池 / Enable connection pool (true/false) | true |
| DB_POOL_MIN_SIZE | 连接池最小连接数 / Pool min size | 5 |
| DB_POOL_MAX_SIZE | 连接池最大连接数 / Pool max size | 20 |
| DB_POOL_RECYCLE | 连接回收时间(秒) / Pool recycle time (seconds) | 300 |
| DB_POOL_MAX_LIFETIME | 连接最大存活时间(秒, 0=不限制) / Max lifetime (sec) | 0 |
| DB_POOL_ACQUIRE_TIMEOUT | 获取连接超时时间(秒) / Acquire timeout (seconds) | 10.0 |
| ENV_TYPE | 环境类型(development/production) / Env type | development |
| ALLOWED_RISK_LEVELS | 允许的风险等级(逗号分隔) / Allowed risk levels | LOW,MEDIUM |
| ALLOW_SENSITIVE_INFO | 允许查询敏感字段 / Allow sensitive info (true/false) | false |
| SENSITIVE_INFO_FIELDS | 自定义敏感字段模式(逗号分隔) / Custom sensitive fields | (空/empty) |
| MAX_SQL_LENGTH | 最大SQL语句长度 / Max SQL length | 5000 |
| BLOCKED_PATTERNS | 阻止的SQL模式(逗号分隔) / Blocked SQL patterns | (空/empty) |
| ENABLE_QUERY_CHECK | 启用查询安全检查 / Enable query check (true/false) | true |
| **ENABLE_DATABASE_ISOLATION** | **启用数据库隔离 / Enable database isolation (true/false)** | **false** |
| **DATABASE_ACCESS_LEVEL** | **数据库访问级别 / Database access level (strict/restricted/permissive)** | **permissive** |
| LOG_LEVEL | 日志级别(DEBUG/INFO/...) / Log level | DEBUG |
> 注/Note: 部分云MySQL需指定`DB_AUTH_PLUGIN`为`mysql_native_password`。
### MySQL 8.0 Authentication Support
This system fully supports MySQL 8.0 authentication mechanisms. MySQL 8.0 uses `caching_sha2_password` by default for enhanced security.
#### Authentication Plugin Comparison
| 认证插件 / Plugin | 安全性 / Security | 兼容性 / Compatibility | 依赖要求 / Dependencies |
|------------------|-------------------|------------------------|------------------------|
| `mysql_native_password` | 中等 / Medium | 高 / High | 无 / None |
| `caching_sha2_password` | 高 / High | 中等 / Medium | cryptography |
#### Configuration Recommendations
**生产环境 / Production**（推荐 / Recommended）：
```ini
DB_AUTH_PLUGIN=caching_sha2_password
```
**开发环境 / Development**（简化配置 / Simplified）：
```ini
DB_AUTH_PLUGIN=mysql_native_password
```
#### Dependency Installation
When using `caching_sha2_password`, the `cryptography` package is required (already included in requirements.txt):
```bash
pip install cryptography
```
### Database Isolation Security
This system provides robust database isolation features to prevent cross-database access and ensure data security.
#### Access Levels
| 级别 / Level | 允许访问 / Allowed Access | 适用场景 / Use Case |
|-------------|---------------------------|-------------------|
| **strict** | 仅指定数据库 / Only specified database | 生产环境 / Production |
| **restricted** | 指定数据库 + 系统库 / Specified + system databases | 开发环境 / Development |
| **permissive** | 所有数据库 / All databases | 测试环境 / Testing |
#### Enable Database Isolation
```bash
# Docker 启用严格模式 / Docker with strict mode
docker run -d \
-e MYSQL_DATABASE=your_database \
-e ENABLE_DATABASE_ISOLATION=true \
-e DATABASE_ACCESS_LEVEL=strict \
mangooer/mysql-mcp-server-sse:latest
# 生产环境自动启用 / Auto-enable in production
docker run -d \
-e ENV_TYPE=production \
-e MYSQL_DATABASE=your_database \
mangooer/mysql-mcp-server-sse:latest
```
**安全效果 / Security Effects**：
- ✅ 阻止 `SHOW DATABASES` / Blocks `SHOW DATABASES`
- ✅ 阻止 `SELECT * FROM mysql.user` / Blocks `SELECT * FROM mysql.user`
- ✅ 阻止 `SHOW TABLES FROM other_db` / Blocks `SHOW TABLES FROM other_db`
- ✅ 允许当前数据库操作 / Allows current database operations
> 🔒 **重要**：生产环境(`ENV_TYPE=production`)会自动启用数据库隔离，使用 `restricted` 模式。
>
> 🔒 **Important**: Production environment (`ENV_TYPE=production`) automatically enables database isolation with `restricted` mode.
---
## 6. Automation & Resource Management Enhancements
### Automated Tool Registration
- All MySQL-related API tools are registered automatically:
- No need to manually maintain registration code in the main entry. To add or remove a tool, simply implement a `register_xxx_tool(s)` function in the `src/tools/` directory.
- The system scans and registers tools automatically at startup, greatly improving maintainability and extensibility.
- All MySQL-related API tools are registered automatically:
- No need to manually maintain registration code in the main entry. To add or remove a tool, simply implement a `register_xxx_tool(s)` function in the `src/tools/` directory.
- The system scans and registers tools automatically at startup, greatly improving maintainability and extensibility.
### Connection Pool Auto-Recycling & Resource Management
- The connection pool uses event loop isolation and auto-recycling:
- Each event loop has its own pool, supporting high concurrency and multi-environment deployment.
- Unused or invalid pools are automatically recycled every 5 minutes (by default), preventing resource leaks.
- When an event loop is closed, its pool is automatically closed to ensure complete resource release.
- Ready for multi-database/multi-tenant scenarios.
- All resource management operations are logged in detail for easy tracking and troubleshooting.
- The connection pool uses event loop isolation and auto-recycling:
- Each event loop has its own pool, supporting high concurrency and multi-environment deployment.
- Unused or invalid pools are automatically recycled every 5 minutes (by default), preventing resource leaks.
- When an event loop is closed, its pool is automatically closed to ensure complete resource release.
- Ready for multi-database/multi-tenant scenarios.
- All resource management operations are logged in detail for easy tracking and troubleshooting.
---
## 7. Security Mechanisms
- Multi-level SQL risk levels (LOW/MEDIUM/HIGH/CRITICAL)
- SQL injection & dangerous operation interception
- Mandatory WHERE clause check
- **Database Isolation Security**: 3-level access control (strict/restricted/permissive)
- **Cross-database Access Protection**: Blocks unauthorized database access
- Automatic sensitive info masking (customizable fields)
- Production allows only low-risk operations by default
- **Auto-enable database isolation in production**
- Multi-level SQL risk levels (LOW/MEDIUM/HIGH/CRITICAL)
- SQL injection & dangerous operation interception
- Mandatory WHERE clause check
- **Database Isolation Security**: 3-level access control (strict/restricted/permissive)
- **Cross-database Access Protection**: Blocks unauthorized database access
- Automatic sensitive info masking (customizable fields)
- Production allows only low-risk operations by default
- **Auto-enable database isolation in production**
---
## 8. Logging & Error Handling
- Configurable log level (LOG_LEVEL)
- Console & file log output
- Detailed running status & error logs
- Robust exception capture & transaction rollback
- Configurable log level (LOG_LEVEL)
- Console & file log output
- Detailed running status & error logs
- Robust exception capture & transaction rollback
---
## 9. FAQ
Q: Why does DELETE not work?
A: Check for WHERE clause. DELETE without WHERE is high risk (CRITICAL), must be allowed in ALLOWED_RISK_LEVELS.
Q: Why does DELETE not work?
A: Check for WHERE clause. DELETE without WHERE is high risk (CRITICAL), must be allowed in ALLOWED_RISK_LEVELS.
Q: How to customize sensitive fields?
A: Set SENSITIVE_INFO_FIELDS, e.g. SENSITIVE_INFO_FIELDS=password,token
Q: How to customize sensitive fields?
A: Set SENSITIVE_INFO_FIELDS, e.g. SENSITIVE_INFO_FIELDS=password,token
Q: How to enable database isolation?
A: Set ENABLE_DATABASE_ISOLATION=true and DATABASE_ACCESS_LEVEL=strict, or use ENV_TYPE=production for auto-enable.
Q: How to enable database isolation?
A: Set ENABLE_DATABASE_ISOLATION=true and DATABASE_ACCESS_LEVEL=strict, or use ENV_TYPE=production for auto-enable.
Q: Cannot query system tables after enabling database isolation?
A: strict mode blocks system table access. Use restricted mode or verify if system table access is actually needed.
Q: Cannot query system tables after enabling database isolation?
A: strict mode blocks system table access. Use restricted mode or verify if system table access is actually needed.
Q: limit parameter error?
A: limit must be a non-negative integer.
Q: limit parameter error?
A: limit must be a non-negative integer.
---
## 10. Contribution Guide
Contributions via Issue and Pull Request are welcome.
Contributions via Issue and Pull Request are welcome.
---
## 11. License
MIT License
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
