Content
# MySQL查询服务器 / MySQL Query Server
---
## 1. 项目简介 / Project Introduction
本项目是基于MCP框架的MySQL查询服务器，支持通过SSE协议进行实时数据库操作，具备完善的安全、日志、配置和敏感信息保护机制，适用于开发、测试和生产环境下的安全MySQL数据访问。
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
```
# Pulling the Image
docker pull mangooer/mysql-mcp-server-sse:latest
# Run the Container
```bash
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 Format:
```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
复制`.env.example`为`.env`，并根据实际情况修改。
Copy `.env.example` to `.env` and modify as needed.
#### 启动服务 / Start the Server
```bash
python -m src.server
```
默认监听：http://127.0.0.1:3000/sse
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 / SQL拦截
│ │ ├── query_limiter.py # Risk control / 风险控制
│ │ └── sql_analyzer.py # SQL analysis / SQL分析
│ └── 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 server host | localhost |
| MYSQL_PORT | MySQL server port | 3306 |
| MYSQL_USER | MySQL username | root |
| MYSQL_PASSWORD | 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 | Max lifetime (sec) | 0 |
| DB_POOL_ACQUIRE_TIMEOUT | Acquire timeout (seconds) | 10.0 |
| ENV_TYPE | Env type (development/production) | development |
| ALLOWED_RISK_LEVELS | Allowed risk levels (comma-separated) | LOW,MEDIUM |
| ALLOW_SENSITIVE_INFO | Allow sensitive info (true/false) | false |
| SENSITIVE_INFO_FIELDS | Custom sensitive fields (comma-separated) | (empty) |
| MAX_SQL_LENGTH | Max SQL length | 5000 |
| BLOCKED_PATTERNS | Blocked SQL patterns (comma-separated) | (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 | Log level (DEBUG/INFO/...) | DEBUG |
> Note: Some cloud MySQL requires specifying `DB_AUTH_PLUGIN` as `mysql_native_password`.
### MySQL 8.0 认证支持 / MySQL 8.0 Authentication Support
本系统完全支持 MySQL 8.0 的认证机制。MySQL 8.0 默认使用 `caching_sha2_password` 认证插件，提供更高的安全性。
This system fully supports MySQL 8.0 authentication mechanisms. MySQL 8.0 uses the `caching_sha2_password` authentication plugin by default, providing 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 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
```bash
docker run -d \
-e ENV_TYPE=production \
-e MYSQL_DATABASE=your_database \
mangooer/mysql-mcp-server-sse:latest
```
**Security Effects**:
- ✅ Blocks `SHOW DATABASES`
- ✅ Blocks `SELECT * FROM mysql.user`
- ✅ Blocks `SHOW TABLES FROM other_db`
- ✅ Allows current database operations
> 🔒 **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.
### 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.
## 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**
## 8. Logging & Error Handling
- Configurable log level (LOG_LEVEL)
- Console & file log output
- Detailed running status & error logs
- Robust exception capture & transaction rollback
## 9. Frequently Asked Questions / FAQ
### Q: Why does DELETE not work?
A: Check for WHERE clause. DELETE without WHERE is high risk, must be allowed in ALLOWED_RISK_LEVELS for CRITICAL.
### 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: Cannot query system tables after enabling database isolation?
A: strict mode blocks system table access. You can switch to restricted mode or check if system table access is actually necessary.
### Q: limit参数报错？
A: limit必须为非负整数。
### Q: limit parameter error?
A: limit must be a non-negative integer.
## 10. Contribution Guide
Contributions via Issue and Pull Request are welcome.
## 11. 许可证 / License
MIT License
This software is provided "as is", without any form of express or implied warranty, including but not limited to the warranties of merchantability, fitness for a particular purpose, and non-infringement. 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.
