PLAN.md•39.1 kB
# MySQL MCP Server with Web UI - Implementation Plan
## Project Overview
Build an enhanced MySQL MCP server with a React-based web UI for live configuration management. Key features include:
- Auto-discovery of databases from MySQL server connections
- Web UI for managing connections, databases, and permissions
- MCP tools for listing and switching databases dynamically
- Dual transport support (HTTP and stdio)
- Per-database permission control
- Simplified MCP client setup (only URL + token required)
---
## 🚀 Implementation Status
**Last Updated:** November 4, 2025
### ✅ Phase 1-6: Backend & Infrastructure (COMPLETED)
**Backend Core (100% Complete)**
- ✅ SQLite-based data storage with better-sqlite3
- ✅ Multi-API key system with named keys
- ✅ Request/Response logging system
- ✅ Master encryption key management
- ✅ AES-256-GCM password encryption
- ✅ MySQL connection pooling and management
- ✅ Database auto-discovery
- ✅ Permission validation system
- ✅ Query executor with transaction support
- ✅ MCP server with 3 tools (mysql_query, list_databases, switch_database)
- ✅ Complete REST API (30+ endpoints)
- ✅ Authentication middleware with API key support
- ✅ Automatic request logging middleware
- ✅ Dual transport support (stdio & HTTP)
- ✅ TypeScript compilation successful
- ✅ Server tested and fully operational
**Frontend Infrastructure (100% Complete)**
- ✅ Vite + React + TypeScript setup
- ✅ TailwindCSS configuration
- ✅ React Router setup
- ✅ React Query setup
- ✅ Component directory structure
- ✅ Build system configured
### ✅ Phase 7-15: Frontend UI (COMPLETED)
- ✅ Layout components (Header, Sidebar, Layout, LayoutWrapper)
- ✅ API key management UI (5 components)
- ✅ Request logs viewer UI (4 components)
- ✅ Usage statistics dashboard
- ✅ Connection management UI (4 components)
- ✅ Database management UI (3 components)
- ✅ Permissions panel (PermissionsModal)
- ✅ Query tester with SQL editor (Monaco Editor, 3 components)
- ✅ Settings page (2 components)
- ✅ Authentication flow (3 components)
- ✅ Common UI components (11 reusable components)
- ✅ API client with 30+ endpoints
- ✅ 6 custom React Query hooks
- ✅ 8 complete pages (Dashboard, Connections, Databases, Query, ApiKeys, Logs, Settings, Auth)
- ✅ Protected routes and auth context
- ✅ Client build successful (~3,500 lines of code)
### 🚧 Phase 16-20: Testing & Polish (IN PROGRESS)
- [ ] Integration testing
- [ ] End-to-end testing
- [✅] Client build and production setup
- [ ] Performance optimization
- [ ] Main README documentation update
- [ ] Production deployment guides
**Current Status:** Both backend v2.0 and frontend are fully implemented and functional! Server has been built and tested. Client has been built with all features including v2.0 API key management and request logging. Ready for testing and documentation phase.
---
## 🔄 New Features (v2.0) - ✅ COMPLETED
### SQLite-Based Storage (✅ Implemented)
- ✅ **Replaced JSON config** with SQLite database for better data management
- ✅ **Multi-API Key Support**: Generate and manage multiple named API keys
- ✅ **Request/Response Logging**: Track all API requests with timestamps and API key attribution
- ✅ **Better Performance**: SQLite provides faster queries and better concurrency
- ✅ **Data Integrity**: ACID compliance and referential integrity with foreign key constraints
- ✅ **Master Encryption Key**: Centrally managed encryption key stored in settings table
### Database Schema
```sql
-- API Keys Table
CREATE TABLE api_keys (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
key TEXT UNIQUE NOT NULL,
created_at INTEGER NOT NULL,
last_used_at INTEGER,
is_active INTEGER DEFAULT 1
);
-- Connections Table
CREATE TABLE connections (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
host TEXT NOT NULL,
port INTEGER NOT NULL,
user TEXT NOT NULL,
password TEXT NOT NULL, -- Encrypted
is_active INTEGER DEFAULT 0,
created_at INTEGER NOT NULL
);
-- Databases Table
CREATE TABLE databases (
id TEXT PRIMARY KEY,
connection_id TEXT NOT NULL,
name TEXT NOT NULL,
is_active INTEGER DEFAULT 0,
select_perm INTEGER DEFAULT 1,
insert_perm INTEGER DEFAULT 0,
update_perm INTEGER DEFAULT 0,
delete_perm INTEGER DEFAULT 0,
create_perm INTEGER DEFAULT 0,
alter_perm INTEGER DEFAULT 0,
drop_perm INTEGER DEFAULT 0,
truncate_perm INTEGER DEFAULT 0,
FOREIGN KEY (connection_id) REFERENCES connections(id) ON DELETE CASCADE
);
-- Request Logs Table
CREATE TABLE request_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
api_key_id TEXT NOT NULL,
endpoint TEXT NOT NULL,
method TEXT NOT NULL,
request_body TEXT,
response_body TEXT,
status_code INTEGER,
duration_ms INTEGER,
timestamp INTEGER NOT NULL,
FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE CASCADE
);
-- Settings Table
CREATE TABLE settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
```
### New API Endpoints
**API Key Management:**
- `GET /api/keys` - List all API keys
- `POST /api/keys` - Generate new API key
- `PUT /api/keys/:id` - Update API key name
- `DELETE /api/keys/:id` - Revoke API key
- `GET /api/keys/:id/logs` - Get request logs for specific key
**Request Logs:**
- `GET /api/logs` - Get all request logs (with pagination)
- `GET /api/logs/stats` - Get usage statistics
- `DELETE /api/logs` - Clear old logs
---
## Architecture
### High-Level Architecture
```
┌─────────────────────────────────────────────────────────────┐
│ MCP Client (Claude) │
│ (HTTP URL or Node Command + Token) │
└────────────────────────┬────────────────────────────────────┘
│
│ MCP Protocol
│
┌────────────────────────▼────────────────────────────────────┐
│ MySQL MCP WebUI Server │
│ │
│ ┌─────────────────┐ ┌──────────────────┐ │
│ │ MCP Handler │ │ Web UI (React) │ │
│ │ - list_databases│ │ - Connections │ │
│ │ - switch_database│ │ - Databases │ │
│ │ - mysql_query │ │ - Permissions │ │
│ └────────┬────────┘ └────────┬─────────┘ │
│ │ │ │
│ │ ┌────────────────▼──────────────┐ │
│ │ │ REST API │ │
│ │ │ - Connection CRUD │ │
│ │ │ - Database discovery │ │
│ │ │ - Permission management │ │
│ │ └────────────────┬──────────────┘ │
│ │ │ │
│ │ ┌────────────────▼──────────────┐ │
│ └───────►│ Connection Manager │ │
│ │ - Pool management │ │
│ │ - Active connection tracking │ │
│ └────────────────┬──────────────┘ │
│ │ │
│ ┌────────────────▼──────────────┐ │
│ │ Query Executor │ │
│ │ - Permission validation │ │
│ │ - Transaction management │ │
│ └────────────────┬──────────────┘ │
│ │ │
│ ┌────────────────▼──────────────┐ │
│ │ Config Manager │ │
│ │ - JSON persistence │ │
│ │ - Password encryption │ │
│ └───────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
│
│ MySQL Protocol
│
┌────────────────▼──────────────┐
│ MySQL Server(s) │
│ - Database 1 │
│ - Database 2 │
│ - Database N │
└───────────────────────────────┘
```
### Data Flow
**1. Database Discovery Flow:**
```
User (Web UI) → Add Connection → Test Connection →
Backend connects to MySQL → SHOW DATABASES →
Filter system DBs → Return list →
User saves → Databases added with default permissions
```
**2. Query Execution Flow:**
```
MCP Client → mysql_query tool → Parse SQL →
Extract query type → Get active connection + database →
Check permissions → Execute query → Return results
```
**3. Database Switching Flow:**
```
MCP Client → switch_database tool →
Update active database in config →
Persist to JSON → Return success
```
---
## Project Structure
```
mysql-mcp-webui/
├── PLAN.md # This file
├── TODO.md # Task checklist
├── README.md # User documentation
├── MIGRATION.md # v2.0 migration notes
├── package.json # Root workspace config
├── data/
│ └── database.db # SQLite database (v2.0)
├── server/
│ ├── package.json
│ ├── tsconfig.json
│ ├── src/
│ │ ├── index.ts # Main entry point (updated for v2.0)
│ │ ├── http-server.ts # Express app setup (updated for v2.0)
│ │ ├── mcp/
│ │ │ ├── server.ts # MCP server factory
│ │ │ ├── handlers.ts # MCP request handlers (updated for v2.0)
│ │ │ └── tools.ts # MCP tool definitions
│ │ ├── api/
│ │ │ ├── routes/
│ │ │ │ ├── connections.ts # Updated for v2.0
│ │ │ │ ├── databases.ts # Updated for v2.0
│ │ │ │ ├── query.ts
│ │ │ │ ├── settings.ts # Updated for v2.0
│ │ │ │ ├── api-keys.ts # NEW in v2.0
│ │ │ │ └── logs.ts # NEW in v2.0
│ │ │ └── middleware/
│ │ │ ├── auth.ts # Updated for API keys (v2.0)
│ │ │ └── logging.ts # NEW in v2.0
│ │ ├── db/
│ │ │ ├── connection-manager.ts # Updated for v2.0
│ │ │ ├── query-executor.ts # Updated for v2.0
│ │ │ ├── discovery.ts
│ │ │ ├── permissions.ts
│ │ │ ├── database-manager.ts # NEW in v2.0 (replaces ConfigManager)
│ │ │ └── schema.ts # NEW in v2.0
│ │ ├── config/
│ │ │ ├── crypto.ts # Password encryption
│ │ │ └── master-key.ts # NEW in v2.0
│ │ └── types/
│ │ └── index.ts
│ └── dist/ # Compiled output
└── client/
├── package.json
├── tsconfig.json
├── vite.config.ts
├── index.html
├── src/
│ ├── main.tsx
│ ├── App.tsx
│ ├── components/
│ │ ├── Layout/
│ │ │ ├── Header.tsx
│ │ │ ├── Sidebar.tsx
│ │ │ └── Layout.tsx
│ │ ├── Connections/
│ │ │ ├── ConnectionList.tsx
│ │ │ ├── ConnectionCard.tsx
│ │ │ ├── AddConnectionModal.tsx
│ │ │ └── EditConnectionModal.tsx
│ │ ├── Databases/
│ │ │ ├── DatabaseList.tsx
│ │ │ └── DatabaseCard.tsx
│ │ ├── Permissions/
│ │ │ ├── PermissionsPanel.tsx
│ │ │ └── PermissionToggle.tsx
│ │ ├── Query/
│ │ │ ├── QueryTester.tsx
│ │ │ ├── SqlEditor.tsx
│ │ │ └── ResultsTable.tsx
│ │ ├── ApiKeys/ # NEW in v2.0
│ │ │ ├── ApiKeyList.tsx
│ │ │ ├── ApiKeyCard.tsx
│ │ │ ├── CreateKeyModal.tsx
│ │ │ └── KeyDetailsModal.tsx
│ │ ├── Logs/ # NEW in v2.0
│ │ │ ├── LogsViewer.tsx
│ │ │ ├── LogsTable.tsx
│ │ │ ├── LogDetailsModal.tsx
│ │ │ └── UsageStats.tsx
│ │ ├── Settings/
│ │ │ ├── Settings.tsx
│ │ │ ├── ApiKeysSection.tsx # NEW in v2.0
│ │ │ └── McpConfigSnippet.tsx
│ │ ├── Common/
│ │ │ ├── Button.tsx
│ │ │ ├── Input.tsx
│ │ │ ├── Modal.tsx
│ │ │ ├── Toggle.tsx
│ │ │ ├── CodeBlock.tsx
│ │ │ └── Table.tsx # NEW in v2.0
│ │ └── Auth/
│ │ ├── AuthProvider.tsx
│ │ └── AuthModal.tsx
│ ├── api/
│ │ └── client.ts
│ ├── hooks/
│ │ ├── useConnections.ts
│ │ ├── useDatabases.ts
│ │ ├── useActiveState.ts
│ │ ├── useApiKeys.ts # NEW in v2.0
│ │ └── useLogs.ts # NEW in v2.0
│ └── lib/
│ └── utils.ts
└── public/ # Build output
```
---
## Data Storage (v2.0)
### SQLite Database Structure
All configuration and operational data is stored in `data/database.db` (SQLite). The database contains:
**Tables:**
- `api_keys` - Multiple named API keys for authentication
- `connections` - MySQL server connection configurations
- `databases` - Database permissions and metadata
- `request_logs` - Automatic logging of all API requests
- `settings` - Server settings (transport, port, master key)
### Legacy config.json Structure (v1.0 - Deprecated)
**Note:** v2.0 uses SQLite instead of JSON. This section is for reference only.
```json
{
"serverToken": "generated-secure-token-64-chars", # Replaced by api_keys table
"transport": "stdio", # Now in settings table
"httpPort": 3000, # Now in settings table
"connections": {
"conn_123abc": {
"id": "conn_123abc",
"name": "Local MySQL Server",
"host": "localhost",
"port": 3306,
"user": "root",
"password": "{\"encrypted\":\"...\",\"iv\":\"...\",\"authTag\":\"...\"}",
"isActive": true,
"databases": {
"myapp": {
"name": "myapp",
"permissions": {
"select": true,
"insert": false,
"update": false,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
}
},
"testdb": {
"name": "testdb",
"permissions": {
"select": true,
"insert": true,
"update": true,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
}
}
},
"activeDatabase": "myapp"
},
"conn_456def": {
"id": "conn_456def",
"name": "Production Server",
"host": "prod.example.com",
"port": 3306,
"user": "readonly",
"password": "{...encrypted...}",
"isActive": false,
"databases": {
"production": {
"name": "production",
"permissions": {
"select": true,
"insert": false,
"update": false,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
}
}
},
"activeDatabase": "production"
}
},
"activeConnection": "conn_123abc"
}
```
---
## MCP Tools
### Tool 1: mysql_query
Execute SQL query against the active database.
**Input Schema:**
```typescript
{
sql: string // SQL query to execute
}
```
**Example:**
```json
{
"name": "mysql_query",
"arguments": {
"sql": "SELECT * FROM users LIMIT 10"
}
}
```
**Response:**
```json
{
"success": true,
"data": [
{ "id": 1, "name": "John", "email": "john@example.com" },
{ "id": 2, "name": "Jane", "email": "jane@example.com" }
],
"rowCount": 2,
"executionTime": "45ms",
"fields": ["id", "name", "email"]
}
```
### Tool 2: list_databases
List all available databases from the active MySQL connection.
**Input Schema:**
```typescript
{
include_metadata?: boolean // Include table count and size
}
```
**Example:**
```json
{
"name": "list_databases",
"arguments": {
"include_metadata": true
}
}
```
**Response:**
```json
{
"connection": "Local MySQL Server",
"databases": [
{
"name": "myapp",
"isActive": true,
"permissions": {
"select": true,
"insert": false,
"update": false,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
},
"tableCount": 15,
"size": "125.5 MB"
},
{
"name": "testdb",
"isActive": false,
"permissions": { ... },
"tableCount": 8,
"size": "42.3 MB"
}
]
}
```
### Tool 3: switch_database
Switch to a different database in the active connection.
**Input Schema:**
```typescript
{
database: string // Name of database to switch to
}
```
**Example:**
```json
{
"name": "switch_database",
"arguments": {
"database": "testdb"
}
}
```
**Response:**
```json
{
"success": true,
"message": "Switched to database: testdb",
"previousDatabase": "myapp",
"activeDatabase": "testdb",
"permissions": {
"select": true,
"insert": true,
"update": true,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
}
}
```
---
## REST API Endpoints
### Authentication (v2.0 Updated)
All endpoints except `/api/health` require Bearer token authentication using API keys.
**Header:**
```
Authorization: Bearer <api-key>
```
**Note:** In v2.0, API keys are managed via the `/api/keys` endpoints. The server auto-generates a default key on first startup.
### Connection Management
#### GET /api/connections
List all MySQL server connections.
**Response:**
```json
{
"success": true,
"data": [
{
"id": "conn_123abc",
"name": "Local MySQL Server",
"host": "localhost",
"port": 3306,
"user": "root",
"isActive": true,
"databaseCount": 2,
"activeDatabase": "myapp"
}
]
}
```
#### POST /api/connections
Add new MySQL server connection.
**Request:**
```json
{
"name": "Production Server",
"host": "prod.example.com",
"port": 3306,
"user": "readonly",
"password": "secretpassword"
}
```
**Response:**
```json
{
"success": true,
"data": {
"id": "conn_456def",
"message": "Connection added successfully"
}
}
```
#### POST /api/connections/:id/test
Test connection and discover databases.
**Response:**
```json
{
"success": true,
"data": {
"connected": true,
"databases": ["myapp", "testdb", "analytics"],
"latency": "12ms"
}
}
```
#### POST /api/connections/:id/activate
Switch to this connection.
**Response:**
```json
{
"success": true,
"data": {
"message": "Switched to connection: Production Server",
"activeConnection": "conn_456def",
"activeDatabase": "production"
}
}
```
#### POST /api/connections/:id/discover
Re-discover databases from MySQL server.
**Response:**
```json
{
"success": true,
"data": {
"discovered": ["myapp", "testdb", "newdb"],
"added": ["newdb"],
"existing": ["myapp", "testdb"]
}
}
```
#### PUT /api/connections/:id
Update connection details.
**Request:**
```json
{
"name": "Updated Name",
"host": "newhost.com",
"port": 3307,
"user": "newuser",
"password": "newpassword"
}
```
#### DELETE /api/connections/:id
Remove connection.
**Response:**
```json
{
"success": true,
"data": {
"message": "Connection deleted"
}
}
```
### Database Management
#### GET /api/connections/:id/databases
List databases for a connection.
**Response:**
```json
{
"success": true,
"data": [
{
"name": "myapp",
"isActive": true,
"permissions": { ... }
}
]
}
```
#### POST /api/connections/:connId/databases/:dbName/activate
Switch active database.
**Response:**
```json
{
"success": true,
"data": {
"message": "Switched to database: testdb",
"activeDatabase": "testdb"
}
}
```
#### PUT /api/connections/:connId/databases/:dbName/permissions
Update database permissions.
**Request:**
```json
{
"permissions": {
"select": true,
"insert": true,
"update": false,
"delete": false,
"alter": false,
"drop": false,
"create": false,
"truncate": false
}
}
```
### Query Execution
#### POST /api/query
Execute SQL query against active database.
**Request:**
```json
{
"sql": "SELECT * FROM users LIMIT 10"
}
```
**Response:**
```json
{
"success": true,
"data": {
"rows": [ ... ],
"fields": [ ... ],
"rowCount": 10,
"executionTime": "23ms"
}
}
```
### API Key Management (NEW in v2.0)
#### GET /api/keys
List all API keys.
**Response:**
```json
{
"success": true,
"data": [
{
"id": "key_123abc",
"name": "Production Key",
"keyPreview": "a1b2c3d4...xyz890",
"created_at": 1699564800000,
"last_used_at": 1699651200000,
"is_active": true
}
]
}
```
#### POST /api/keys
Create new API key.
**Request:**
```json
{
"name": "My New Key"
}
```
**Response:**
```json
{
"success": true,
"data": {
"id": "key_456def",
"name": "My New Key",
"key": "full-api-key-string-here",
"created_at": 1699651200000,
"message": "API key created successfully. Please save this key, it will not be shown again."
}
}
```
#### PUT /api/keys/:id
Update API key name.
**Request:**
```json
{
"name": "Updated Key Name"
}
```
#### DELETE /api/keys/:id
Revoke/delete API key.
**Response:**
```json
{
"success": true,
"data": {
"message": "API key revoked successfully"
}
}
```
#### GET /api/keys/:id/logs
Get request logs for specific API key.
**Response:**
```json
{
"success": true,
"data": [
{
"id": 1,
"endpoint": "/api/connections",
"method": "GET",
"status_code": 200,
"duration_ms": 45,
"timestamp": 1699651200000
}
]
}
```
### Request Logs (NEW in v2.0)
#### GET /api/logs
Get all request logs with pagination.
**Query Parameters:**
- `limit` (default: 100) - Number of logs to return
- `offset` (default: 0) - Pagination offset
- `apiKeyId` (optional) - Filter by API key ID
**Response:**
```json
{
"success": true,
"data": [
{
"id": 1,
"api_key_id": "key_123abc",
"endpoint": "/api/connections",
"method": "GET",
"request_body": null,
"response_body": "{\"success\":true,\"data\":[]}",
"status_code": 200,
"duration_ms": 45,
"timestamp": 1699651200000
}
],
"pagination": {
"limit": 100,
"offset": 0,
"count": 1
}
}
```
#### GET /api/logs/stats
Get usage statistics.
**Response:**
```json
{
"success": true,
"data": {
"totalRequests": 1234,
"byApiKey": [
{
"api_key_id": "key_123abc",
"count": 800
},
{
"api_key_id": "key_456def",
"count": 434
}
],
"byEndpoint": [
{
"endpoint": "/api/query",
"count": 500
},
{
"endpoint": "/api/connections",
"count": 300
}
]
}
}
```
#### DELETE /api/logs
Clear old logs.
**Query Parameters:**
- `days` (default: 30) - Delete logs older than this many days
**Response:**
```json
{
"success": true,
"data": {
"deleted": 450,
"message": "Deleted 450 logs older than 30 days"
}
}
```
### Settings
#### GET /api/settings
Get server settings.
**Response (v2.0):**
```json
{
"success": true,
"data": {
"transport": "http",
"httpPort": 3000,
"nodeVersion": "v22.20.0"
}
}
```
**Note:** `serverToken` field removed in v2.0. Use API key management endpoints instead.
#### GET /api/active
Get current active state.
**Response:**
```json
{
"success": true,
"data": {
"connectionId": "conn_123abc",
"connectionName": "Local MySQL Server",
"database": "myapp",
"permissions": { ... }
}
}
```
#### GET /api/health
Health check endpoint (public, no auth required).
**Response:**
```json
{
"status": "healthy",
"uptime": 3600,
"activeConnection": "Local MySQL Server",
"activeDatabase": "myapp"
}
```
---
## Security
### Authentication
- Auto-generated 64-character secure token on first run
- Token verification for all API and MCP requests
- Constant-time comparison to prevent timing attacks
### Password Encryption
- AES-256-GCM encryption for database passwords
- Unique IV (Initialization Vector) for each encryption
- Authentication tag for integrity verification
- Key derived from server token using scrypt
### Permission Validation
- Per-database permission checking
- Query parsing before execution
- Transaction-based execution (rollback on error)
- Read-only transactions for SELECT queries
### SQL Injection Prevention
- Parameterized queries using mysql2 prepared statements
- Query parsing and validation
- No dynamic SQL construction
---
## Transport Modes
### Stdio Transport (Node Command)
**Use Case:** Direct execution via node command in MCP client config.
**MCP Client Config:**
```json
{
"mcpServers": {
"mysql-webui": {
"command": "node",
"args": [
"/path/to/mysql-mcp-webui/server/dist/index.js"
],
"env": {
"AUTH_TOKEN": "your-token-here",
"TRANSPORT": "stdio"
}
}
}
}
```
**Characteristics:**
- Direct stdin/stdout communication
- No network required
- Faster for local usage
- Web UI still accessible via separate HTTP server
### HTTP Transport (URL)
**Use Case:** Remote MCP server or when running as a service.
**MCP Client Config:**
```json
{
"mcpServers": {
"mysql-webui": {
"url": "http://localhost:9274/mcp",
"headers": {
"Authorization": "Bearer your-token-here"
}
}
}
}
```
**Characteristics:**
- Network-based communication
- Can be accessed remotely
- Single server for both MCP and Web UI
- Suitable for team usage
---
## Build & Deployment
### Development Mode
```bash
# Install dependencies
npm install
# Run both server and client in dev mode
npm run dev
# Server runs on http://localhost:9274
# Client dev server runs on http://localhost:5173 with API proxy
```
### Production Build
```bash
# Build client and server
npm run build
# This will:
# 1. Build React app to server/public/
# 2. Compile TypeScript server to server/dist/
```
### Running in Production
**HTTP Mode:**
```bash
TRANSPORT=http NODE_ENV=production node server/dist/index.js
```
**Stdio Mode:**
```bash
TRANSPORT=stdio NODE_ENV=production node server/dist/index.js
```
### Global Installation
```bash
# Install as global npm package
npm run install:global
# Now callable as:
mysql-mcp-webui
```
### PM2 Deployment
```bash
# Start as daemon
pm2 start server/dist/index.js --name mysql-mcp-webui \
-e TRANSPORT=http \
-e NODE_ENV=production
# Auto-restart on system reboot
pm2 startup
pm2 save
```
### Docker Deployment
```dockerfile
FROM node:20-alpine
WORKDIR /app
COPY . .
RUN npm install && npm run build
ENV TRANSPORT=http NODE_ENV=production
EXPOSE 3000
CMD ["node", "server/dist/index.js"]
```
---
## User Workflows
### Initial Setup Workflow
1. User installs package: `npm install -g mysql-mcp-webui`
2. Starts server: `mysql-mcp-webui` (or via PM2)
3. Opens web UI: `http://localhost:9274`
4. Enters authentication token (shown in console or Settings page)
5. Adds first MySQL connection with host/port/user/password
6. Clicks "Test Connection" to verify and discover databases
7. Saves connection (databases auto-added with SELECT-only permissions)
8. Configures permissions per database as needed
9. Copies token and MCP config from Settings page
10. Adds to Claude Desktop config
11. Restarts Claude Desktop
12. Ready to use!
### Daily Usage Workflow
**Via Web UI:**
1. Open `http://localhost:9274`
2. View active connection and database in header
3. Switch databases or connections as needed
4. Adjust permissions on the fly
5. Test queries in Query Tester
6. No restart required for any changes
**Via MCP (Claude):**
1. User: "List all databases"
2. Claude calls `list_databases` tool
3. User: "Switch to testdb"
4. Claude calls `switch_database` tool
5. User: "Show me all users"
6. Claude calls `mysql_query` with SELECT query
7. Results returned based on current permissions
### Adding New Database Server
1. Click "Add Connection" in Connections page
2. Enter name, host, port, user, password
3. Click "Test Connection" to verify
4. Review discovered databases
5. Click "Save"
6. Databases appear with default permissions (SELECT only)
7. Go to Permissions page to configure each database
8. Click "Activate" to make this the active connection
---
## Technology Stack
### Backend
- **Runtime:** Node.js 20+
- **Language:** TypeScript 5.x
- **MCP SDK:** @modelcontextprotocol/sdk ^1.15.1
- **Web Framework:** Express 5.x
- **Database Client:** mysql2 ^3.x (with Promise support)
- **SQL Parser:** node-sql-parser ^5.x
- **Validation:** Zod ^3.x
- **Encryption:** Node.js crypto module
### Frontend
- **Framework:** React 18
- **Language:** TypeScript 5.x
- **Build Tool:** Vite 5.x
- **Styling:** TailwindCSS 3.x
- **State Management:** @tanstack/react-query ^5.x
- **HTTP Client:** axios ^1.x
- **Icons:** lucide-react
- **Code Editor:** @monaco-editor/react or CodeMirror
### Development Tools
- **Package Manager:** npm
- **TypeScript Compiler:** tsc
- **Dev Server:** tsx (for server), vite (for client)
- **Process Manager:** concurrently (dev), PM2 (production)
- **Linter:** ESLint 9.x
- **Formatter:** Prettier
---
## Key Features Summary
### Core Features
- ✅ Web UI for configuration management
- ✅ Auto-discovery of databases from MySQL servers
- ✅ Per-database permission control (8 operations)
- ✅ Live database switching (no restart required)
- ✅ Dual transport support (stdio and HTTP)
- ✅ Three MCP tools (query, list, switch)
- ✅ Encrypted password storage
- ✅ Token-based authentication
- ✅ Query tester with SQL editor
- ✅ Real-time connection status
- ✅ Multiple MySQL server support
- ✅ Active connection/database tracking
### Security Features
- ✅ AES-256-GCM password encryption
- ✅ Secure token generation
- ✅ Permission validation before query execution
- ✅ Transaction-based query execution
- ✅ Read-only transactions for SELECT
- ✅ SQL injection prevention via parameterized queries
- ✅ Constant-time token comparison
### UX Features
- ✅ One-click database switching
- ✅ Visual permission toggles
- ✅ Connection testing before save
- ✅ Database auto-discovery
- ✅ Copy-paste MCP config snippets
- ✅ Real-time status indicators
- ✅ Error messages and validation
- ✅ Loading states and feedback
---
## Success Metrics
### Functional Requirements
- [ ] User can add MySQL server connection via web UI
- [ ] System auto-discovers databases from MySQL server
- [ ] User can configure 8 permissions per database
- [ ] User can switch active database from web UI
- [ ] MCP client can list databases via tool
- [ ] MCP client can switch databases via tool
- [ ] MCP client can query with permission enforcement
- [ ] Works in both HTTP and stdio modes
- [ ] Only requires token in MCP client config
- [ ] No restart needed for permission changes
- [ ] Passwords are encrypted in config file
- [ ] Multiple connections can be managed
### Performance Requirements
- [ ] Query execution < 100ms for simple queries
- [ ] Database discovery < 2s for typical servers
- [ ] UI responds < 200ms to user actions
- [ ] Connection switching < 1s
- [ ] Web UI loads in < 2s
### Security Requirements
- [ ] All passwords encrypted at rest
- [ ] Token required for all protected endpoints
- [ ] Permissions validated before query execution
- [ ] SQL injection prevented
- [ ] No sensitive data in logs
---
## Future Enhancements (Post-MVP)
### Advanced Features
- Query history tracking
- Favorite queries
- Query execution statistics
- Connection pooling configuration
- SSL/TLS support for MySQL connections
- Read replicas support
- Query timeout configuration
- Result set pagination
- Export results (CSV, JSON)
### Management Features
- User management (multiple users with different tokens)
- Audit logging
- Connection groups/folders
- Database backup triggers
- Schema visualization
- Table browser in web UI
- Database comparison tool
### Integration Features
- Webhook notifications
- Slack/Discord integration
- Metrics and monitoring
- Grafana dashboard
- Prometheus metrics export
- Health check endpoints for monitoring
---
## References
### Documentation
- [MCP SDK Documentation](https://modelcontextprotocol.io)
- [mysql2 Documentation](https://github.com/sidorares/node-mysql2)
- [node-sql-parser Documentation](https://github.com/taozhi8833998/node-sql-parser)
- [React Documentation](https://react.dev)
- [TailwindCSS Documentation](https://tailwindcss.com)
### Original Codebase
- Location: `/Users/yash/Codes/MySQLMCP/mcp-server-mysql-main/`
- Key files for reference:
- `index.ts` - MCP server implementation
- `src/db/index.ts` - Database connection and query execution
- `src/db/permissions.ts` - Permission checking logic
- `src/db/utils.ts` - SQL parsing utilities
---
## Timeline
### ✅ Week 1: Backend Foundation (Days 1-7) - COMPLETED
- ✅ Project setup and structure
- ✅ Configuration manager with JSON persistence
- ✅ Password encryption/decryption
- ✅ Connection manager with pooling
- ✅ Database discovery service
- ✅ Basic query executor
### ✅ Week 2: Backend API & MCP (Days 8-14) - COMPLETED
- ✅ REST API routes for connections
- ✅ REST API routes for databases
- ✅ Authentication middleware
- ✅ MCP server factory with dual transport
- ✅ Three MCP tools implementation
- ✅ Permission validation logic
### ✅ Week 3: Frontend Core (Days 15-21) - COMPLETED
- ✅ React app setup with Vite
- ✅ Authentication flow (AuthContext, AuthModal, ProtectedRoute)
- ✅ Connection management UI (4 components)
- ✅ Database list UI (3 components)
- ✅ API client with React Query (30+ endpoints)
- ✅ Full routing and layout (Header, Sidebar, Layout)
### ✅ Week 4: Frontend Features (Days 22-28) - COMPLETED
- ✅ Database selector and switching
- ✅ Permissions management panel (PermissionsModal)
- ✅ Query tester with SQL editor (Monaco Editor)
- ✅ Settings page with API key display
- ✅ MCP config snippet generator
- ✅ Status indicators and notifications
- ✅ API Key management UI (v2.0)
- ✅ Request logs viewer (v2.0)
### 🚧 Week 5: Polish & Deployment (Days 29-35) - IN PROGRESS
- ✅ Error handling and validation
- ✅ Loading states and UX polish
- ✅ Build configuration
- ✅ Client README (304 lines)
- [ ] Main README update
- [ ] End-to-end testing
- [ ] Deployment scripts and guides
---
**Plan Version:** 2.1
**Last Updated:** 2025-11-04
**Status:** Backend v2.0 & Frontend v2.0 COMPLETE - Testing & Docs Remaining
**Achievement Summary:**
- ✅ 23 TypeScript backend modules implemented (5 new in v2.0)
- ✅ SQLite database with 5 tables fully operational
- ✅ Multi-API key system implemented and tested
- ✅ Automatic request/response logging functional
- ✅ Server compiles successfully and tested with real requests
- ✅ All core functionality operational
- ✅ MCP tools ready for Claude integration
- ✅ REST API fully implemented (30+ endpoints)
- ✅ **Frontend fully implemented with 45+ components**
- ✅ **8 complete pages with full functionality**
- ✅ **~3,500 lines of production-ready React/TS code**
- ✅ **API client with all endpoints integrated**
- ✅ **6 custom React Query hooks**
- ✅ **11 reusable common components**
- ✅ **Client built and production-ready**
**v2.0 Changes:**
- ✅ Migrated from JSON config to SQLite database
- ✅ Replaced ConfigManager with DatabaseManager (600+ lines)
- ✅ Implemented multi-API key authentication
- ✅ Added comprehensive request/response logging
- ✅ Created 6 new API endpoints for keys and logs
- ✅ Updated 8 existing files for SQLite integration
- ✅ Built complete web UI with all v2.0 features
- ✅ All systems tested and operational