Content
# MySQL MCP Server
A secure and professional MySQL Model Context Protocol (MCP) server with clean architecture, designed for seamless integration with GitHub Copilot, Cline, and other AI coding assistants in VS Code.
## Features
- **Clean Architecture**: Follows Single Responsibility Principle for maintainability and scalability
- **Security First**: Built-in permission system, audit logging, and query validation
- **Connection Pooling**: Efficient MySQL connection management with connection pooling
- **Schema Inspection**: Tools for exploring database structure
- **Query Execution**: Secure SQL query execution with timeout controls
- **Audit Logging**: Comprehensive logging of all database operations
## Quick Start
### Installation
```bash
npm install
```
### Configuration
Copy the example configuration and update credentials:
```bash
cp .env.example .env
```
Then edit `.env` in the project root:
```env
# MySQL Connection
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database
# Security Settings
SECURITY_ENABLED=true
ALLOWED_OPERATIONS=SELECT,SHOW,DESCRIBE,EXPLAIN
MAX_QUERY_EXECUTION_TIME=30000
# Audit Settings
AUDIT_ENABLED=true
AUDIT_LOG_LEVEL=info
```
### Build
```bash
npm run build
```
### Run
```bash
npm start
```
## Integration with VS Code
### GitHub Copilot
Recommended for this repository: use the workspace MCP file at `.vscode/mcp.json`.
It is already included in this project and should look like:
```json
{
"servers": {
"mysql-local": {
"type": "stdio",
"command": "node",
"args": ["${workspaceFolder}/dist/index.js"],
"env": {
"MYSQL_HOST": "${env:MYSQL_HOST}",
"MYSQL_PORT": "${env:MYSQL_PORT}",
"MYSQL_USER": "${env:MYSQL_USER}",
"MYSQL_PASSWORD": "${env:MYSQL_PASSWORD}",
"MYSQL_DATABASE": "${env:MYSQL_DATABASE}"
}
}
}
}
```
Alternative: add to VS Code user settings (`settings.json`):
```json
{
"mcpServers": {
"mysql": {
"command": "node",
"args": ["/path/to/mysql-mcp/dist/index.js"],
"env": {
"MYSQL_HOST": "localhost",
"MYSQL_PORT": "3306",
"MYSQL_USER": "your_username",
"MYSQL_PASSWORD": "your_password",
"MYSQL_DATABASE": "your_database"
}
}
}
}
```
### Cline
Configure in Cline settings:
```json
{
"mcpServers": {
"mysql": {
"command": "node",
"args": ["/path/to/mysql-mcp/dist/index.js"],
"env": {
"MYSQL_HOST": "localhost",
"MYSQL_USER": "your_username",
"MYSQL_PASSWORD": "your_password",
"MYSQL_DATABASE": "your_database"
}
}
}
}
```
## Available Tools
### Query Tools
#### execute_query
Execute SQL queries against the database.
**Parameters:**
- `sql` (required): SQL query to execute
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
- `timeout` (optional): Query timeout in milliseconds
### Schema Tools
#### list_databases
List all available databases.
**Parameters:**
- `userId` (optional): User ID for permission checking
#### list_tables
List all tables in a database.
**Parameters:**
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
#### describe_table
Get the structure of a table.
**Parameters:**
- `table` (required): Table name
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
### Admin Tools
#### get_server_status
Get MySQL server status variables.
**Parameters:**
- `userId` (optional): User ID for permission checking
#### get_process_list
Get list of running MySQL processes.
**Parameters:**
- `userId` (optional): User ID for permission checking
#### kill_query
Kill a running MySQL process.
**Parameters:**
- `processId` (required): Process ID to kill
- `userId` (optional): User ID for permission checking
#### get_table_indexes
Get index information for a table.
**Parameters:**
- `table` (required): Table name
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
#### get_table_constraints
Get constraint information for a table.
**Parameters:**
- `table` (required): Table name
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
#### analyze_table
Analyze table statistics.
**Parameters:**
- `table` (required): Table name
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
#### optimize_table
Optimize a table.
**Parameters:**
- `table` (required): Table name
- `database` (optional): Database name
- `userId` (optional): User ID for permission checking
### RBAC Tools
All RBAC tools require authentication and API key permission `rbac_admin`.
#### Role and Permission Management
- `create_role(name, desc)`
- `delete_role(id)`
- `list_roles()`
- `create_permission(name, desc)`
- `list_permissions()`
- `assign_permission_to_role(roleId, permId)`
- `revoke_permission_from_role(roleId, permId)`
#### User Role Assignment
- `assign_role_to_user(userId, roleId)`
- `revoke_role_from_user(userId, roleId)`
- `list_user_roles(userId)`
#### Authorization Checks
- `check_user_permission(userId, permissionName)`
- `current_user_permissions()`
- `can_access(userId, resource, action, attributes?)`
#### RBAC Audit
- `audit_log(event, actor, target, details)`
- `query_audit_logs(filter)`
#### RBAC Database Operations
- `db_migrate_rbac()`
- `db_seed_rbac_defaults()`
- `db_check_integrity()`
## Available Resources
### mysql://status
Current database connection status and pool information.
### mysql://config
Server configuration (sanitized, no sensitive data).
### mysql://audit/statistics
Statistics about database operations.
### mysql://audit/logs
Recent database operation logs.
## Architecture
```
src/
├── config/ # Configuration management
├── mysql/ # MySQL connection management
├── security/ # Permission and audit logging
├── tools/ # MCP tools implementation
├── resources/ # MCP resources
├── types/ # TypeScript type definitions
└── index.ts # Main entry point
```
### Key Components
1. **ConfigManager**: Handles configuration loading and validation
2. **MySQLConnectionManager**: Manages connection pooling and query execution
3. **PermissionManager**: Handles permission checking and access control
4. **AuditLogger**: Logs all database operations for security
5. **QueryTool**: Executes SQL queries with security validation
6. **SchemaTool**: Inspects database schema
7. **DatabaseResource**: Provides MCP resources
## Security
- Permission-based access control
- Query timeout enforcement
- Operation whitelisting/blacklisting
- Audit logging of all operations
- Rate limiting support
See [docs/security.md](docs/security.md) for detailed security documentation.
## Documentation
- [Getting Started](docs/getting-started.md)
- [Configuration](docs/configuration.md)
- [Security](docs/security.md)
- [Tools Reference](docs/tools.md)
- [Resources Reference](docs/resources.md)
- [Integration Guide](docs/integration.md)
## Development
```bash
# Install dependencies
npm install
# Run in development mode
npm run dev
# Build
npm run build
# Lint
npm run lint
# Format
npm run format
```
## Requirements
- Node.js >= 18.0.0
- MySQL 5.7+ or MariaDB 10.3+
## License
MIT License - see [LICENSE](LICENSE) file for details.
## Contributing
Contributions are welcome! Please read our contributing guidelines before submitting pull requests.
