scripts/mcp/README.md
This directory contains scripts to test the ProxySQL MCP (Model Context Protocol) module with target-profile routing and exploration tools across MySQL and PostgreSQL backends.
Note: parts of this README still show legacy single-MySQL examples (
mcp-mysql_*). The current MCP routing model uses target/auth profiles (runtime_mcp_target_profiles,runtime_mcp_auth_profiles) andtarget_idin query/discovery/catalog/llm workflows.
MCP (Model Context Protocol) is a JSON-RPC 2.0 protocol that allows AI/LLM applications to:
┌─────────────────────────────────────────────────────────────────────┐
│ ProxySQL MCP Module │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ ProxySQL Admin Interface (Port 6032) │ │
│ │ Configure: mcp-enabled, mcp-port, MCP profiles, etc. │ │
│ └──────────────────────────┬──────────────────────────────────┘ │
│ │ │
│ ┌──────────────────────────▼──────────────────────────────────┐ │
│ │ MCP HTTPS Server (Port 6071) │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ /config │ │ /query │ │ /admin │ │ │
│ │ │ endpoint │ │ endpoint │ │ endpoint │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ /observe │ │ /cache │ │ /ai │ │ │
│ │ │ endpoint │ │ endpoint │ │ endpoint │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │ │ │ │
│ │ ┌─────────────┐ │ │
│ │ │ /rag │ │ │
│ │ │ endpoint │ │ │
│ │ └─────────────┘ │ │
│ └──────────────────────────────────────────────────────────────┘ │
│ │ │ │ │ │ │ │
│ ┌─────────▼─────────▼────────▼────────▼────────▼────────▼─────────┐│
│ │ Dedicated Tool Handlers ││
│ │ ┌─────────────┐┌─────────────┐┌─────────────┐┌─────────────┐ ││
│ │ │ Config_TH ││ Query_TH ││ Admin_TH ││ Cache_TH │ ││
│ │ │ ││ ││ ││ │ ││
│ │ │ get_config ││ list_schemas││ admin_list_ ││ get_cache_ │ ││
│ │ │ set_config ││ list_tables ││ users ││ stats │ ││
│ │ │ reload ││ describe_ ││ admin_kill_ ││ invalidate │ ││
│ │ └─────────────┘│ table ││ query ││ set_cache_ │ ││
│ │ │ sample_rows ││ ... ││ ttl │ ││
│ │ │ run_sql_ ││ ││ ... │ ││
│ │ │ readonly ││ ││ │ ││
│ │ │ catalog_ ││ ││ │ ││
│ │ │ upsert ││ ││ │ ││
│ │ │ discovery. ││ ││ │ ││
│ │ │ run_static ││ ││ │ ││
│ │ │ llm.* ││ ││ │ ││
│ │ │ agent.* ││ ││ │ ││
│ │ └─────────────┘└─────────────┘└─────────────┘ ││
│ │ ┌─────────────┐ ││
│ │ │ Observe_TH │ ││
│ │ │ │ ││
│ │ │ list_stats │ ││
│ │ │ get_stats │ ││
│ │ │ show_ │ ││
│ │ │ connections │ ││
│ │ │ ... │ ││
│ │ └─────────────┘ ││
│ │ ┌─────────────┐ ││
│ │ │ AI_TH │ ││
│ │ │ │ ││
│ │ │ llm.query │ ││
│ │ │ llm.analyze │ ││
│ │ │ anomaly. │ ││
│ │ │ detect │ ││
│ │ │ ... │ ││
│ │ └─────────────┘ ││
│ │ ┌─────────────┐ ││
│ │ │ RAG_TH │ ││
│ │ │ │ ││
│ │ │ rag.search_ │ ││
│ │ │ fts │ ││
│ │ │ rag.search_ │ ││
│ │ │ vector │ ││
│ │ │ rag.search_ │ ││
│ │ │ hybrid │ ││
│ │ │ rag.get_ │ ││
│ │ │ chunks │ ││
│ │ │ rag.get_ │ ││
│ │ │ docs │ ││
│ │ │ rag.fetch_ │ ││
│ │ │ from_source │ ││
│ │ │ rag.admin. │ ││
│ │ │ stats │ ││
│ │ └─────────────┘ ││
│ └──────────────────────────────────────────────────────────────────┘│
│ │ │ │ │ │ │ │
│ ┌─────────▼─────────▼────────▼────────▼────────▼────────▼─────────┐│
│ │ MySQL Connection Pools ││
│ │ ┌─────────────┐┌─────────────┐┌─────────────┐┌─────────────┐ ││
│ │ │ Config Pool ││ Query Pool ││ Admin Pool ││ Other Pools │ ││
│ │ │ ││ ││ ││ │ ││
│ │ │ 1-2 conns ││ 2-4 conns ││ 1 conn ││ 1-2 conns │ ││
│ │ └─────────────┘└─────────────┘└─────────────┘└─────────────┘ ││
│ └──────────────────────────────────────────────────────────────────┘│
│ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ Discovery Schema (SQLite) │ │
│ │ • Two-phase discovery catalog │ │
│ │ • Tables: runs, objects, columns, indexes, FKs, profiles │ │
│ │ • LLM artifacts: summaries, relationships, domains │ │
│ └──────────────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────────┐
│ MySQL Server (Port 3306) │
│ • Test Database: testdb │
│ • Tables: customers, orders, products, etc. │
└──────────────────────────────────────────────────────────────────────┘
Where:
TH = Tool Handler| Category | Tools | Purpose |
|---|---|---|
| Inventory | list_schemas, list_tables | Discover available databases and tables |
| Structure | describe_table, get_constraints | Get schema details (columns, keys, indexes) |
| Sampling | sample_rows, sample_distinct | Sample data safely with row limits |
| Query | run_sql_readonly, explain_sql | Execute SELECT queries with guardrails |
| Relationships | suggest_joins, find_reference_candidates | Infer table relationships |
| Profiling | table_profile, column_profile | Analyze data distributions and statistics |
| Catalog | catalog_upsert, catalog_get, catalog_search, catalog_delete, catalog_list, catalog_merge | Store/retrieve LLM discoveries |
| Discovery | discovery.run_static | Run Phase 1 of two-phase discovery for a specific target_id |
| Agent Coordination | agent.run_start, agent.run_finish, agent.event_append | Coordinate LLM agent discovery runs |
| LLM Interaction | llm.summary_upsert, llm.summary_get, llm.relationship_upsert, llm.domain_upsert, llm.domain_set_members, llm.metric_upsert, llm.question_template_add, llm.note_add, llm.search | Store and retrieve LLM-generated insights |
| RAG | rag.search_fts, rag.search_vector, rag.search_hybrid, rag.get_chunks, rag.get_docs, rag.fetch_from_source, rag.admin.stats | Retrieval-Augmented Generation tools |
Location: Built into ProxySQL (lib/MCP_*.cpp)
Purpose: Exposes HTTPS endpoints that implement JSON-RPC 2.0 protocol for LLM integration.
Key Configuration Variables:
| Variable | Default | Description |
|---|---|---|
mcp-enabled | false | Enable/disable MCP server |
mcp-port | 6071 | HTTPS port for MCP endpoints |
mcp-config_endpoint_auth | (empty) | Auth token for /config endpoint |
mcp-observe_endpoint_auth | (empty) | Auth token for /observe endpoint |
mcp-query_endpoint_auth | (empty) | Auth token for /query endpoint |
mcp-admin_endpoint_auth | (empty) | Auth token for /admin endpoint |
mcp-cache_endpoint_auth | (empty) | Auth token for /cache endpoint |
mcp-ai_endpoint_auth | (empty) | Auth token for /ai endpoint |
mcp-timeout_ms | 30000 | Query timeout in milliseconds |
runtime_mcp_target_profiles | n/a | Logical targets (target_id, protocol, hostgroup, auth profile) |
runtime_mcp_auth_profiles | n/a | Backend credentials bound to target profiles |
RAG Configuration Variables:
| Variable | Default | Description |
|---|---|---|
genai-rag_enabled | false | Enable RAG features |
genai-rag_k_max | 50 | Maximum k for search results |
genai-rag_candidates_max | 500 | Maximum candidates for hybrid search |
genai-rag_query_max_bytes | 8192 | Maximum query length in bytes |
genai-rag_response_max_bytes | 5000000 | Maximum response size in bytes |
genai-rag_timeout_ms | 2000 | RAG operation timeout in ms |
Endpoints:
POST https://localhost:6071/mcp/config - Configuration toolsPOST https://localhost:6071/mcp/query - Database exploration and discovery toolsPOST https://localhost:6071/mcp/rag - Retrieval-Augmented Generation toolsPOST https://localhost:6071/mcp/admin - Administrative toolsPOST https://localhost:6071/mcp/cache - Cache management toolsPOST https://localhost:6071/mcp/observe - Observability toolsPOST https://localhost:6071/mcp/ai - AI and LLM toolsLocation: lib/*_Tool_Handler.cpp
Purpose: Each endpoint has its own dedicated tool handler with specific tools and connection pools.
Tool Handlers:
Location: Each Tool_Handler manages its own connection pool
Purpose: Manages reusable connections to backend MySQL/PostgreSQL targets for tool execution.
Features:
pthread_mutex_tLocation: lib/Discovery_Schema.cpp
Purpose: External memory for LLM to store discoveries and two-phase discovery results.
Features:
mcp_catalog.db)| Script | Purpose | What it Does |
|---|---|---|
setup_test_db.sh | Database setup | Creates test MySQL database with sample data (customers, orders, products) |
configure_mcp.sh | ProxySQL configuration | Sets MCP variables and loads to runtime |
test_mcp_tools.sh | Tool testing | Tests all MCP tools via JSON-RPC |
test_catalog.sh | Catalog testing | Tests catalog CRUD and FTS search |
test_nl2sql_tools.sh | NL2SQL testing | Tests natural language to SQL conversion tools |
test_nl2sql_e2e.sh | NL2SQL end-to-end | End-to-end natural language to SQL testing |
stress_test.sh | Load testing | Concurrent connection stress test |
demo_agent_claude.sh | Demo agent | Demonstrates LLM agent interaction with MCP |
┌─────────────────────────────────────────────────────────────────────┐
│ Step 1: Setup Test Database │
│ ───────────────────────────────────────────────────────────────── │
│ ./setup_test_db.sh start --mode native │
│ │
│ → Creates 'testdb' database on your MySQL server │
│ → Creates tables: customers, orders, products, order_items │
│ → Inserts sample data (5 customers, 5 products, 5 orders) │
│ → Creates view: customer_orders │
│ → Creates stored procedure: get_customer_stats │
└─────────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────┐
│ Step 2: Configure ProxySQL MCP Module │
│ ───────────────────────────────────────────────────────────────── │
│ ./configure_mcp.sh --host 127.0.0.1 --port 3306 --user root \ │
│ --password your_password --enable │
│ │
│ → Sets mcp-mysql_hosts=127.0.0.1 │
│ → Sets mcp-mysql_ports=3306 │
│ → Sets mcp-mysql_user=root │
│ → Sets mcp-mysql_password=your_password │
│ → Sets mcp-mysql_schema=testdb │
│ → Sets mcp-enabled=true │
│ → Loads MCP VARIABLES TO RUNTIME │
│ │
│ Result: │
│ → MySQL_Tool_Handler initializes connection pool │
│ → Connection established to MySQL server │
│ → HTTPS server starts on port 6071 │
└─────────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────┐
│ Step 3: Test MCP Tools │
│ ───────────────────────────────────────────────────────────────── │
│ ./test_mcp_tools.sh │
│ │
│ → Sends JSON-RPC requests to https://localhost:6071/query │
│ → Tests tools: list_schemas, list_tables, describe_table, etc. │
│ → Verifies responses are valid JSON with expected data │
│ │
│ Example Request: │
│ POST /query │
│ { │
│ "jsonrpc": "2.0", │
│ "method": "tools/call", │
│ "params": { │
│ "name": "list_tables", │
│ "arguments": {"schema": "testdb"} │
│ }, │
│ "id": 1 │
│ } │
└─────────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────┐
│ Step 4: Verify Connection Pool │
│ ───────────────────────────────────────────────────────────────── │
│ grep "MySQL_Tool_Handler" /path/to/proxysql.log │
│ │
│ Expected logs: │
│ MySQL_Tool_Handler: Connected to 127.0.0.1:3306 │
│ MySQL_Tool_Handler: Connection pool initialized with 1 connection(s)│
│ MySQL Tool Handler initialized for schema 'testdb' │
└─────────────────────────────────────────────────────────────────────┘
# Add to ~/.bashrc or run before testing
export PROXYSQL_ADMIN_PASSWORD=admin # Your ProxySQL admin password
export MYSQL_PASSWORD=your_mysql_password # Your MySQL root password
cd /home/rene/proxysql-vec/scripts/mcp
# 1. Setup test database on your MySQL server
./setup_test_db.sh start --mode native
# 2. Configure and enable ProxySQL MCP module
./configure_mcp.sh --host 127.0.0.1 --port 3306 --user root --enable
# 3. Run all MCP tool tests
./test_mcp_tools.sh
# 4. Run catalog tests
./test_catalog.sh
# 5. Run stress test (10 concurrent requests)
./stress_test.sh -n 10
# 6. Clean up (drop test database when done)
./setup_test_db.sh reset --mode native
cd /home/rene/proxysql-vec/scripts/mcp
# 1. Start test MySQL container
./setup_test_db.sh start --mode docker
# 2. Configure and enable ProxySQL MCP module
./configure_mcp.sh --host 127.0.0.1 --port 3307 --user root --password test123 --enable
# 3. Run all MCP tool tests
./test_mcp_tools.sh
# 4. Stop test MySQL container when done
./setup_test_db.sh stop --mode docker
Purpose: Creates a test MySQL database with sample schema and data for MCP testing.
What it does:
testdb database with 4 tables: customers, orders, products, order_itemscustomer_orders) and stored procedure (get_customer_stats)init_testdb.sql for reproducibilityCommands:
./setup_test_db.sh start [--mode native|docker] # Create test database
./setup_test_db.sh status [--mode native|docker] # Check database status
./setup_test_db.sh connect [--mode native|docker] # Connect to MySQL shell
./setup_test_db.sh reset [--mode native|docker] # Drop/recreate database
./setup_test_db.sh --help # Show help
Native Mode (your MySQL server):
# With defaults (127.0.0.1:3306, root user)
./setup_test_db.sh start --mode native
# With custom credentials
./setup_test_db.sh start --mode native --host localhost --port 3307 \
--user myuser --password mypass
Docker Mode (isolated container):
./setup_test_db.sh start --mode docker
# Container port: 3307, root user, password: test123
Purpose: Configures ProxySQL MCP module variables via admin interface.
What it does:
mcp-mysql_hosts - Where to find MySQL servermcp-mysql_ports - MySQL portmcp-mysql_user - MySQL usernamemcp-mysql_password - MySQL passwordmcp-mysql_schema - Default databasemcp-enabled - Enable/disable MCP serverCommands:
./configure_mcp.sh --enable # Enable with defaults
./configure_mcp.sh --disable # Disable MCP server
./configure_mcp.sh --status # Show current configuration
./configure_mcp.sh --help # Show help
Options:
--host HOST MySQL host (default: 127.0.0.1)
--port PORT MySQL port (default: 3307 for Docker, 3306 for native)
--user USER MySQL user (default: root)
--password PASS MySQL password
--database DB Default database (default: testdb)
--mcp-port PORT MCP HTTPS port (default: 6071)
Full Example:
./configure_mcp.sh \
--host 127.0.0.1 \
--port 3306 \
--user root \
--password your_password \
--database testdb \
--enable
What happens when you run --enable:
mcp-mysql_hosts='127.0.0.1' in ProxySQLmcp-mysql_ports='3306' in ProxySQLmcp-mysql_user='root' in ProxySQLmcp-mysql_password='your_password' in ProxySQLmcp-mysql_schema='testdb' in ProxySQLmcp-enabled='true' in ProxySQLLOAD MCP VARIABLES TO RUNTIMEMySQL_Tool_Handler initializes connection pool to MySQLPurpose: Tests all MCP tools via HTTPS/JSON-RPC to verify the connection pool and tools work.
What it does:
/query endpointTools Tested:
| Category | Tools | What it Verifies |
|---|---|---|
| Inventory | list_schemas, list_tables | Connection works, can query information_schema |
| Structure | describe_table, get_constraints, describe_view | Can read schema details |
| Profiling | table_profile, column_profile | Aggregation queries work |
| Sampling | sample_rows, sample_distinct | Can sample data with limits |
| Query | run_sql_readonly, explain_sql | Query guardrails and execution |
| Catalog | catalog_upsert, catalog_get, catalog_search | Catalog CRUD works |
Commands:
./test_mcp_tools.sh # Test all tools
./test_mcp_tools.sh --tool list_schemas # Test single tool
./test_mcp_tools.sh --skip-tool catalog_* # Skip catalog tests
./test_mcp_tools.sh -v # Verbose output
Example Test Flow:
$ ./test_mcp_tools.sh --tool list_tables
[TEST] Testing tool: list_tables
[INFO] ✓ list_tables
Test Summary
Total tests: 1
Passed: 1
Failed: 0
Purpose: Tests the SQLite catalog (LLM memory) functionality.
What it does:
Tests:
CAT001: Upsert table schema entryCAT002: Upsert domain knowledge entryCAT003: Get table entryCAT004: Get domain entryCAT005: Search catalogCAT006: List entries by kindCAT007: Update existing entryCAT008: Verify updateCAT009: FTS search with wildcardCAT010: Delete entryCAT011: Verify deletionCAT012: Cleanup domain entryPurpose: Tests concurrent connection handling by the connection pool.
What it does:
Commands:
./stress_test.sh -n 10 # 10 concurrent requests
./stress_test.sh -n 50 -d 100 # 50 requests, 100ms delay
./stress_test.sh -t list_tables -v # Test specific tool
Check ProxySQL logs:
tail -f /path/to/proxysql.log | grep -i mcp
Verify configuration:
mysql -h 127.0.0.1 -P 6032 -u admin -padmin
SHOW VARIABLES LIKE 'mcp-%';
Expected output:
Variable_name Value
mcp-enabled true
mcp-port 6071
mcp-mysql_hosts 127.0.0.1
mcp-mysql_ports 3306
...
Verify MySQL is accessible:
mysql -h 127.0.0.1 -P 3306 -u root -pyourpassword testdb -e "SELECT 1"
Check for connection pool errors in logs:
grep "MySQL_Tool_Handler" /path/to/proxysql.log
Expected logs on success:
MySQL_Tool_Handler: Connected to 127.0.0.1:3306
MySQL_Tool_Handler: Connection pool initialized with 1 connection(s)
MySQL Tool Handler initialized for schema 'testdb'
Common causes:
./setup_test_db.sh start first./configure_mcp.sh --enableEnable verbose output:
./test_mcp_tools.sh -v
To reset everything and start over:
# 1. Disable MCP
./configure_mcp.sh --disable
# 2. Drop test database
./setup_test_db.sh reset --mode native
# 3. Start fresh
./setup_test_db.sh start --mode native
./configure_mcp.sh --enable
| Variable | Default | Description |
|---|---|---|
mcp-enabled | false | Enable MCP server |
mcp-port | 6071 | HTTPS port for MCP |
mcp-config_endpoint_auth | (empty) | Auth token for /config endpoint |
mcp-observe_endpoint_auth | (empty) | Auth token for /observe endpoint |
mcp-query_endpoint_auth | (empty) | Auth token for /query endpoint |
mcp-admin_endpoint_auth | (empty) | Auth token for /admin endpoint |
mcp-cache_endpoint_auth | (empty) | Auth token for /cache endpoint |
mcp-ai_endpoint_auth | (empty) | Auth token for /ai endpoint |
mcp-timeout_ms | 30000 | Query timeout in milliseconds |
mcp-mysql_hosts | 127.0.0.1 | MySQL server host(s) |
mcp-mysql_ports | 3306 | MySQL server port(s) |
mcp-mysql_user | (empty) | MySQL username |
mcp-mysql_password | (empty) | MySQL password |
mcp-mysql_schema | (empty) | Default schema |
# ProxySQL Admin Configuration (for configure_mcp.sh)
export PROXYSQL_ADMIN_HOST=${PROXYSQL_ADMIN_HOST:-127.0.0.1}
export PROXYSQL_ADMIN_PORT=${PROXYSQL_ADMIN_PORT:-6032}
export PROXYSQL_ADMIN_USER=${PROXYSQL_ADMIN_USER:-admin}
export PROXYSQL_ADMIN_PASSWORD=${PROXYSQL_ADMIN_PASSWORD:-admin}
# MySQL Configuration (for setup_test_db.sh and configure_mcp.sh)
export MYSQL_HOST=${MYSQL_HOST:-127.0.0.1}
export MYSQL_PORT=${MYSQL_PORT:-3306}
export MYSQL_USER=${MYSQL_USER:-root}
export MYSQL_PASSWORD=${MYSQL_PASSWORD:-}
export TEST_DB_NAME=${TEST_DB_NAME:-testdb}
# MCP Server Configuration (for test scripts)
export MCP_HOST=${MCP_HOST:-127.0.0.1}
export MCP_PORT=${MCP_PORT:-6071}