# MySQL MCP Server Pro Architecture
This document describes the overall architecture of the MySQL MCP Server Pro project.
## System Architecture Overview
```mermaid
graph TB
subgraph "Transport Layer"
Transport[Transport Modes<br/>STDIO/SSE/HTTP]
end
subgraph "Server Core"
Server[server.py<br/>MCP Server]
end
subgraph "Registry Layer"
Registry[Registries<br/>Tool/Prompt]
end
subgraph "Business Layer"
Tools[Handles<br/>10+ Tools]
Prompts[Prompts<br/>2 Templates]
Util[Utils<br/>SQL Executor]
end
subgraph "Infrastructure Layer"
Config[Config<br/>DB/OAuth/Event]
Pool[Connection Pool]
end
subgraph "Data Layer"
MySQL[(MySQL Database)]
end
Transport --> Server
Server --> Registry
Registry --> Tools
Registry --> Prompts
Tools --> Util
Prompts --> Tools
Util --> Pool
Pool --> MySQL
Server --> Config
Util --> Config
style Server fill:#4a90e2,stroke:#2c5aa0,color:#fff
style Registry fill:#7b68ee,stroke:#5a4fcf,color:#fff
style MySQL fill:#f39c12,stroke:#d68910,color:#fff
```
## Detailed Architecture Diagram
The following diagram shows the complete architecture with all components and their relationships:
```mermaid
graph TB
subgraph "Client Layer"
Client1[AI Clients<br/>Cursor/Claude Desktop]
Client2[Browser<br/>OAuth Login]
end
subgraph "Transport Layer"
STDIO[STDIO Mode]
SSE[SSE Mode<br/>Server-Sent Events]
HTTP[StreamableHttp Mode<br/>HTTP/1.1]
end
subgraph "Server Core"
Server[server.py<br/>MCP Server Core]
OAuth[OAuth2.0 Auth<br/>Optional]
end
subgraph "Registry Layer"
ToolReg[ToolRegistry<br/>Tool Registry]
PromptReg[PromptRegistry<br/>Prompt Registry]
end
subgraph "Tools Layer (Handles)"
BaseHandler[BaseHandler<br/>Tool Base Class]
ExecuteSQL[ExecuteSQL<br/>SQL Execution]
GetTableName[GetTableName<br/>Search Table by Description]
GetTableDesc[GetTableDesc<br/>Get Table Structure]
GetTableIndex[GetTableIndex<br/>Get Table Index]
GetTableLock[GetTableLock<br/>Table Lock Detection]
GetDBHealth[GetDBHealthRunning<br/>DB Health Check]
GetIndexUsage[GetDBHealthIndexUsage<br/>Index Usage Analysis]
OptimizeSQL[OptimizeSql<br/>SQL Optimization]
GetChinese[GetChineseInitials<br/>Chinese to Pinyin]
UsePrompt[UsePromptQueryTableData<br/>Prompt Query Tool]
end
subgraph "Prompts Layer"
BasePrompt[BasePrompt<br/>Prompt Base Class]
AnalysisPrompt[AnalysisMySqlIssues<br/>MySQL Issue Analysis]
QueryPrompt[QueryTableData<br/>Table Data Query]
end
subgraph "Utils Layer"
ExecuteUtil[ExecuteSqlUtil<br/>SQL Executor]
DBPool[DatabasePool<br/>Connection Pool]
end
subgraph "Config Layer"
DBConfig[dbconfig.py<br/>Database Config]
EventStore[event_store.py<br/>Event Store]
OAuthConfig[oauth/config.py<br/>OAuth Config]
end
subgraph "Data Layer"
MySQL[(MySQL Database)]
end
Client1 -->|MCP Protocol| STDIO
Client1 -->|HTTP| SSE
Client1 -->|HTTP| HTTP
Client2 -->|HTTP| OAuth
STDIO --> Server
SSE --> Server
HTTP --> OAuth
OAuth --> Server
Server --> ToolReg
Server --> PromptReg
ToolReg --> BaseHandler
BaseHandler --> ExecuteSQL
BaseHandler --> GetTableName
BaseHandler --> GetTableDesc
BaseHandler --> GetTableIndex
BaseHandler --> GetTableLock
BaseHandler --> GetDBHealth
BaseHandler --> GetIndexUsage
BaseHandler --> OptimizeSQL
BaseHandler --> GetChinese
BaseHandler --> UsePrompt
PromptReg --> BasePrompt
BasePrompt --> AnalysisPrompt
BasePrompt --> QueryPrompt
ExecuteSQL --> ExecuteUtil
GetTableName --> ExecuteUtil
GetTableDesc --> ExecuteUtil
GetTableIndex --> ExecuteUtil
GetTableLock --> ExecuteUtil
GetDBHealth --> ExecuteUtil
GetIndexUsage --> ExecuteUtil
OptimizeSQL --> ExecuteUtil
ExecuteUtil --> DBPool
DBPool --> MySQL
Server --> DBConfig
Server --> EventStore
OAuth --> OAuthConfig
ExecuteUtil --> DBConfig
style Server fill:#4a90e2,stroke:#2c5aa0,color:#fff
style ToolReg fill:#7b68ee,stroke:#5a4fcf,color:#fff
style PromptReg fill:#7b68ee,stroke:#5a4fcf,color:#fff
style MySQL fill:#f39c12,stroke:#d68910,color:#fff
```
## Detailed Architecture Layers
### 1. Client Layer
- **AI Clients**: Cursor, Claude Desktop, and other MCP protocol-compatible clients
- **Browser**: OAuth2.0 authentication login page
### 2. Transport Layer
Supports three MCP transport modes:
- **STDIO**: Standard input/output mode for command-line interaction
- **SSE**: Server-Sent Events mode with HTTP long connection
- **StreamableHttp**: HTTP/1.1 streaming transport mode (default)
### 3. Server Core Layer
- **server.py**: MCP Server core that handles client requests
- **OAuth2.0**: Optional authentication middleware supporting Bearer Token authentication
### 4. Registry Layer
- **ToolRegistry**: Tool registry that manages all tool instances
- **PromptRegistry**: Prompt registry that manages all prompt template instances
### 5. Tools Layer (Handles)
All tools inherit from `BaseHandler` and are automatically registered to `ToolRegistry`:
| Tool Name | Description |
|-----------|-------------|
| ExecuteSQL | SQL execution tool supporting multiple statements |
| GetTableName | Search table names by Chinese name or description |
| GetTableDesc | Get table structure information |
| GetTableIndex | Get table index information |
| GetTableLock | Detect table locks and row locks |
| GetDBHealthRunning | Database health status check |
| GetDBHealthIndexUsage | Index usage analysis |
| OptimizeSql | SQL performance optimization suggestions |
| GetChineseInitials | Convert Chinese fields to pinyin |
| UsePromptQueryTableData | Query table data using prompt templates |
### 6. Prompts Layer
All prompt templates inherit from `BasePrompt` and are automatically registered to `PromptRegistry`:
| Prompt Name | Description |
|-------------|-------------|
| AnalysisMySqlIssues | MySQL issue analysis prompt template |
| QueryTableData | Table data query prompt template |
### 7. Utils Layer
- **ExecuteSqlUtil**: SQL execution utility class responsible for permission checking, SQL execution, and result formatting
- **DatabasePool**: Database connection pool management (based on SQLAlchemy)
### 8. Configuration Layer
- **dbconfig.py**: Database configuration management with role-based access control (readonly/writer/admin)
- **event_store.py**: Event storage (for StreamableHttp mode)
- **oauth/config.py**: OAuth configuration management
### 9. Data Layer
- **MySQL Database**: Target database
## Data Flow Diagram
```mermaid
sequenceDiagram
participant Client as AI Client
participant Server as MCP Server
participant Registry as Tool Registry
participant Handler as Tool Handler
participant Util as ExecuteSqlUtil
participant Pool as Connection Pool
participant DB as MySQL Database
Client->>Server: Connection Request
Server->>Client: Connection Established
Client->>Server: list_tools()
Server->>Registry: get_all_tools()
Registry->>Server: Return Tool List
Server->>Client: Tool List
Client->>Server: call_tool(name, args)
Server->>Registry: get_tool(name)
Registry->>Server: Return Tool Instance
Server->>Handler: run_tool(args)
Handler->>Util: execute_sql(sql)
Util->>Util: Permission Check
Util->>Pool: Get Connection
Pool->>DB: Execute SQL
DB->>Pool: Return Results
Pool->>Util: Return Connection
Util->>Handler: Format Results
Handler->>Server: Return Results
Server->>Client: Return Results
```
## Tool Auto-Registration Mechanism
```mermaid
graph LR
A[Import Tool Class] --> B{Inherits BaseHandler?}
B -->|Yes| C[__init_subclass__ Triggered]
C --> D[Check name Attribute]
D -->|Exists| E[ToolRegistry.register]
E --> F[Create Tool Instance]
F --> G[Store in _tools Dict]
G --> H[Registration Complete]
D -->|Not Exists| I[Skip Registration]
```
## Permission Control Flow
```mermaid
graph TD
A[SQL Request] --> B[Parse SQL Type]
B --> C{Get Role Permissions}
C --> D[readonly: SELECT/SHOW/DESCRIBE/EXPLAIN]
C --> E[writer: +INSERT/UPDATE/DELETE]
C --> F[admin: +CREATE/ALTER/DROP/TRUNCATE]
D --> G{Permission Check}
E --> G
F --> G
G -->|Pass| H[Execute SQL]
G -->|Reject| I[Throw SQLPermissionError]
H --> J[Return Results]
```
## Deployment Architecture
```mermaid
graph TB
subgraph "Development Environment"
Dev1[uv run -m mysql_mcp_server_pro.server]
Dev2[mysql_mcp_server_pro --mode stdio]
end
subgraph "Production Environment"
Docker[Docker Container]
Dockerfile[Dockerfile]
Compose[docker-compose.yml]
end
subgraph "Client Configuration"
Config1[STDIO Config]
Config2[SSE Config<br/>http://localhost:9000/sse]
Config3[HTTP Config<br/>http://localhost:3000/mcp/]
end
Dev1 --> Config3
Dev2 --> Config1
Docker --> Config2
Docker --> Config3
Dockerfile --> Docker
Compose --> Docker
```
## Key Design Patterns
### 1. Registry Pattern
- `ToolRegistry`: Centralized management of all tool instances
- `PromptRegistry`: Centralized management of all prompt template instances
### 2. Template Method Pattern
- `BaseHandler`: Defines tool interface, subclasses implement specific logic
- `BasePrompt`: Defines prompt template interface, subclasses implement specific prompts
### 3. Singleton Pattern
- `ExecuteSqlUtil._connection_pool`: Database connection pool singleton
- `ToolRegistry._tools`: Tool registry singleton
### 4. Strategy Pattern
- Three transport modes: STDIO, SSE, StreamableHttp
- Three role permissions: readonly, writer, admin
## Extension Points
### Adding New Tools
1. Create a new tool class in the `handles/` directory
2. Inherit from `BaseHandler` and implement `get_tool_description()` and `run_tool()` methods
3. Import the new tool class in `handles/__init__.py`
4. The tool will be automatically registered to `ToolRegistry`
### Adding New Prompt Templates
1. Create a new prompt class in the `prompts/` directory
2. Inherit from `BasePrompt` and implement `get_prompt()` and `run_prompt()` methods
3. Import the new prompt class in `prompts/__init__.py`
4. The prompt will be automatically registered to `PromptRegistry`
## Summary
MySQL MCP Server Pro adopts a layered architecture design and implements a flexible extension mechanism through the registry pattern. Core features:
- ✅ **Modular Design**: Clear layer division with well-defined responsibilities
- ✅ **Auto-Registration Mechanism**: Tools and prompt templates are automatically registered, easy to extend
- ✅ **Multi-Transport Mode Support**: Supports STDIO, SSE, and StreamableHttp modes
- ✅ **Permission Control**: Role-based permission management system
- ✅ **Connection Pool Management**: Efficient database connection pool management
- ✅ **OAuth Authentication**: Optional OAuth2.0 authentication support
