# 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.