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
# Pull the image
docker pull mangooer/mysql-mcp-server-sse:latest
# Run the container
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
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 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 | LOW,MEDIUM |
| ALLOW_SENSITIVE_INFO | Allow sensitive info (true/false) | false |
| SENSITIVE_INFO_FIELDS | Custom sensitive fields | (empty) |
| MAX_SQL_LENGTH | Max SQL length | 5000 |
| BLOCKED_PATTERNS | 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 | Log level | DEBUG |
> Note: Some cloud MySQL require `DB_AUTH_PLUGIN` to be set to `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 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**:
- ✅ 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. 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: 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. Use restricted mode or verify if system table access is actually needed.
### 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
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.
