Phase 3.1: Official ProxySQL failover hooks

[renecannao]

Phase 3.1 — Official ProxySQL Hooks

Goal: Built-in pre/post failover hooks that notify ProxySQL via its Admin API — no custom scripts needed. "orchestrator + ProxySQL works out of the box."

Sub-tasks

• 3.1.1 Research ProxySQL Admin API endpoints for hostgroup management (LOAD MYSQL SERVERS TO RUNTIME, mysql_servers table manipulation)
• 3.1.2 Design hook interface — define Go interface for pre-failover and post-failover hooks
• 3.1.3 Implement ProxySQL client library (go/proxysql/) — connection management, authentication, query execution against Admin interface
• 3.1.4 Implement pre-failover hook — drain traffic from failing master via ProxySQL (set weight=0 or move to offline hostgroup)
• 3.1.5 Implement post-failover hook — update ProxySQL hostgroups to point to new master, restore traffic
• 3.1.6 Add configuration options in Config struct — ProxySQL admin host/port/credentials, hostgroup mappings, hook enable/disable
• 3.1.7 Add CLI commands for manual ProxySQL integration testing (orchestrator-client -c proxysql-test)
• 3.1.8 Write integration tests with ProxySQL mock
• 3.1.9 Documentation: setup guide, configuration reference, troubleshooting
• 3.1.10 Example configurations for common topologies (single-writer, multi-reader)

Design considerations

• Must be optional — orchestrator should work without ProxySQL configured
• Support multiple ProxySQL instances (fleet-wide failover)
• Respect existing hook mechanism (PreFailoverProcesses, PostFailoverProcesses) — ProxySQL hooks should compose with, not replace, custom scripts
• Consider ProxySQL Cluster (admin variable propagation) for multi-ProxySQL setups

Dependencies

None — can start immediately.

Part of Phase 3 — Feature Direction.