MCP (Model Context Protocol) Multi-Endpoint Architecture

[renecannao]

MCP Multi-Endpoint Architecture

Overview

This issue documents the implementation of multiple dedicated MCP endpoints in ProxySQL, where each endpoint serves a specific purpose with its own tool handler. This architecture allows for:

• Specialized tools per endpoint (config, query, admin, cache, observe)
• Isolated resources (separate handlers can have separate connection pools)
• Independent authentication (per-endpoint Bearer tokens)
• Clear separation of concerns (each endpoint has a well-defined purpose)

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│ ProxySQL Process │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ MCP_Threads_Handler │ │
│ │ - Configuration variables (mcp-*) │ │
│ │ - Manages 5 dedicated tool handlers │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ ProxySQL_MCP_Server │ │
│ │ (Single HTTPS Server) │ │
│ │ │ │
│ │ Port: mcp-port (default 6071) │ │
│ │ SSL: Uses ProxySQL's certificates │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ┌──────────────┬──────────────┼──────────────┬──────────────┬─────────┐ │
│ ▼ ▼ ▼ ▼ ▼ ▼ │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌───┐│
│ │conf│ │obs │ │qry │ │adm │ │cach│ │cat│││
│ │TH │ │TH │ │TH │ │TH │ │TH │ │log│││
│ └─┬──┘ └─┬──┘ └─┬──┘ └─┬──┘ └─┬──┘ └─┬─┘│
│ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ Tools: Tools: Tools: Tools: Tools: │ │
│ - get_config - list_ - list_ - admin_ - get_ │ │
│ - set_config stats schemas - set_ cache │ │
│ - reload - show_ - list_ - reload - set_ │ │
│ - list_vars metrics tables - invalidate│ │
│ - get_status - clear │ │
│ - query - show - warm │ │
│ - explain processes - entries │ │
│ - sample │ │
│ - catalog_* - kill │ │
│ - flush │ │
└─────────────────────────────────────────────────────────────────────────────┘

TH = Tool Handler

Endpoint Specifications

/mcp/config - Configuration Endpoint

Purpose: Runtime configuration and management of ProxySQL

Authentication: mcp-config_endpoint_auth (Bearer token)

Tools:

Tool	Description	Status
get_config	Get current configuration values	✅ Implemented
set_config	Modify configuration values	✅ Implemented
reload_config	Reload configuration from disk/memory/runtime	⚠️ Stub
list_variables	List all available variables	✅ Implemented
get_status	Get server status information	✅ Implemented

---

/mcp/observe - Observability Endpoint

Purpose: Real-time metrics, statistics, and monitoring data

Authentication: mcp-observe_endpoint_auth (Bearer token)

Tools:

Tool	Description	Status
list_stats	List available statistics	❌ Stub
get_stats	Get specific statistics	❌ Stub
show_connections	Show active connections	❌ Stub
show_queries	Show query statistics	❌ Stub
get_health	Get health check information	❌ Stub
show_metrics	Show performance metrics	❌ Stub

---

/mcp/query - Query Endpoint

Purpose: Safe database exploration and query execution

Authentication: mcp-query_endpoint_auth (Bearer token)

Tools:

Tool	Description	Status
list_schemas	List databases	✅ Implemented
list_tables	List tables in schema	✅ Implemented
describe_table	Get table structure	✅ Implemented
get_constraints	Get foreign keys and constraints	✅ Implemented
table_profile	Get table statistics	✅ Implemented
column_profile	Get column statistics	✅ Implemented
sample_rows	Get sample data	✅ Implemented
sample_distinct	Sample distinct values	✅ Implemented
run_sql_readonly	Execute read-only SQL	✅ Implemented
explain_sql	Explain query execution plan	✅ Implemented
suggest_joins	Suggest table joins	✅ Implemented
find_reference_candidates	Find foreign key references	✅ Implemented
catalog_upsert	Store data in catalog	✅ Implemented
catalog_get	Retrieve from catalog	✅ Implemented
catalog_search	Search catalog	✅ Implemented
catalog_list	List catalog entries	✅ Implemented
catalog_merge	Merge catalog entries	✅ Implemented
catalog_delete	Delete from catalog	✅ Implemented

---

/mcp/admin - Administration Endpoint

Purpose: Administrative operations

Authentication: mcp-admin_endpoint_auth (Bearer token, most restrictive)

Tools:

Tool	Description	Status
admin_list_users	List MySQL users	❌ Stub
admin_show_processes	Show running processes	❌ Stub
admin_kill_query	Kill a running query	❌ Stub
admin_flush_cache	Flush various caches	❌ Stub
admin_reload	Reload users/servers configuration	❌ Stub

---

/mcp/cache - Cache Endpoint

Purpose: Query cache management

Authentication: mcp-cache_endpoint_auth (Bearer token)

Tools:

Tool	Description	Status
get_cache_stats	Get cache statistics	❌ Stub
invalidate_cache	Invalidate cache entries	❌ Stub
set_cache_ttl	Set cache TTL	❌ Stub
clear_cache	Clear all cache	❌ Stub
warm_cache	Warm up cache with queries	❌ Stub
get_cache_entries	List cached queries	❌ Stub

File Structure

include/
├── MCP_Tool_Handler.h # Base class for all tool handlers
├── Config_Tool_Handler.h # /mcp/config endpoint handler
├── Query_Tool_Handler.h # /mcp/query endpoint handler
├── Admin_Tool_Handler.h # /mcp/admin endpoint handler
├── Cache_Tool_Handler.h # /mcp/cache endpoint handler
├── Observe_Tool_Handler.h # /mcp/observe endpoint handler
├── MySQL_Tool_Handler.h # Legacy (wrapped by Query_Tool_Handler)
├── MCP_Thread.h # MCP_Threads_Handler (manages all handlers)
├── MCP_Endpoint.h # MCP_JSONRPC_Resource (HTTP endpoint)
└── ProxySQL_MCP_Server.hpp # HTTPS server

lib/
├── Config_Tool_Handler.cpp
├── Query_Tool_Handler.cpp
├── Admin_Tool_Handler.cpp
├── Cache_Tool_Handler.cpp
├── Observe_Tool_Handler.cpp
├── MySQL_Tool_Handler.cpp
├── MCP_Thread.cpp
├── MCP_Endpoint.cpp
└── ProxySQL_MCP_Server.cpp

doc/MCP/
├── Architecture.md # Detailed architecture documentation
├── VARIABLES.md # Configuration variables reference
└── README.md # Module overview

TODO List

Phase 1: Complete Core Implementation ✅

• Create MCP_Tool_Handler base class interface
• Create Config_Tool_Handler (functional)
• Create Query_Tool_Handler (wraps MySQL_Tool_Handler)
• Create Admin_Tool_Handler (stub)
• Create Cache_Tool_Handler (stub)
• Create Observe_Tool_Handler (stub)
• Update MCP_Threads_Handler to manage all 5 handlers
• Update ProxySQL_MCP_Server to pass correct handler to each endpoint
• Update MCP_JSONRPC_Resource to use tool handler base class
• Update lib/Makefile to include new source files
• Create Architecture.md documentation
• Verify compilation succeeds

Phase 2: Implement Per-Endpoint Authentication

• Implement authenticate_request() in MCP_Endpoint.cpp
• Use mcp-{endpoint}_endpoint_auth variables for validation
• Support Bearer token from Authorization header
• Return proper 401 Unauthorized on auth failure
• Document authentication in Architecture.md

Phase 3: Implement Admin Tool Handler

• admin_list_users - Query mysql_users table
• admin_show_processes - Query stats_mysql_connection_pool or similar
• admin_kill_query - Implement KILL QUERY functionality
• admin_flush_cache - Implement cache flush commands
• admin_reload - Implement LOAD USERS/SERVERS TO RUNTIME
• Add proper error handling and validation

Phase 4: Implement Cache Tool Handler

• get_cache_stats - Query cache statistics from stats_mysql_query_cache
• invalidate_cache - Implement cache invalidation
• set_cache_ttl - Configure cache TTL settings
• clear_cache - Clear all cache entries
• warm_cache - Execute queries to warm up cache
• get_cache_entries - List cached queries
• Add proper error handling and validation

Phase 5: Implement Observe Tool Handler

• list_stats - List all available ProxySQL statistics
• get_stats - Get specific statistic values
• show_connections - Query connection pool statistics
• show_queries - Query query statistics from stats_mysql_queries_digest
• get_health - Aggregate health check (backend status, etc.)
• show_metrics - Query Prometheus metrics
• Add proper error handling and validation

Phase 6: Complete Config Tool Handler

• reload_config - Implement proper Admin_FlushVariables integration
  • Support "disk" → LOAD MYSQL VARIABLES FROM DISK
  • Support "memory" → Not applicable
  • Support "runtime" → LOAD MYSQL VARIABLES TO RUNTIME
• Add proper error handling and validation
• Test with running ProxySQL instance

Phase 7: Testing & Validation

• Integration test each endpoint with curl/gh
• Test tool discovery via tools/list
• Test tool execution via tools/call
• Test authentication (when implemented)
• Test error handling for invalid requests
• Update test_mcp_tools.sh to use dynamic tool discovery
• Add integration tests for new endpoints

Phase 8: Documentation

• Update doc/MCP/Architecture.md with implementation details
• Add examples for each endpoint in Architecture.md
• Create end-user guide for using MCP endpoints
• Document tool schemas in detail
• Add troubleshooting section

Phase 9: Optional Enhancements

• Per-endpoint MySQL credentials (different users for different endpoints)
• Rate limiting per endpoint
• Request/response logging per endpoint
• Metrics for tool usage (which tools are called most)
• WebSocket support for real-time observability
• Add more tools to Query_Tool_Handler (e.g., describe_view)

Testing Commands

# Test each endpoint's tools
curl -k -X POST https://127.0.0.1:6071/mcp/config \
 -H "Content-Type: application/json" \
 -d '{"jsonrpc":"2.0","method":"tools/list","id":1}'

curl -k -X POST https://127.0.0.1:6071/mcp/query \
 -H "Content-Type: application/json" \
 -d '{"jsonrpc":"2.0","method":"tools/list","id":1}'

# Call a tool
curl -k -X POST https://127.0.0.1:6071/mcp/config \
 -H "Content-Type: application/json" \
 -d '{"jsonrpc":"2.0","method":"tools/call","params":{"name":"get_status","arguments":{}},"id":1}'

Related Files

• Implementation: include/MCP_*.h, lib/MCP_*.cpp
• Documentation: doc/MCP/Architecture.md
• Variables: doc/MCP/VARIABLES.md
• Test Script: scripts/mcp/test_mcp_tools.sh
• Configuration Script: scripts/mcp/configure_mcp.sh

Reference

• MCP Protocol Spec: https://modelcontextprotocol.io/
• Previous architecture discussion: See commit history and design documents