MySQL Failover for Asynchronous replication with Orchestrator, ProxySQL and Consul
summary
MySQL asynchronous replication failover is implemented with Orchestrator, ProxySQL and Consul where Orchestrator detects failures and runs hooks that write new master info into Consul so ProxySQL agents update runtime host groups via consul-template; the demo uses a 3-node Raft orchestrator, sets master_connect_retry=1 for fast detection and shows hooks writing successor and failed host IPs to Consul and ProxySQL being updated, with a recovery block of 3600s to avoid flapping; automatic failover works but is warned as potentially hazardous.
AI-written summary · about
chapters (15)
- 0:00Intro
- 2:28Factors considered for High Availability
- 3:26How many 9's do you really need?
- 12:02Orchestrator (Discovery)
- 12:30Orchestrator (Restructure)
- 13:24Orchestrator (Failure Detection)
- 16:02Orchestrator (Candidate Selection)
- 16:44Orchestrator (Recovery)
- 18:58ProxySQL (Implementation)
- 20:39What do we have now?
- 28:57Orchestrator hook and Consul-template
- 29:52Logs for Orchestrator hook
- 31:45How does consul-template work
- 33:06The Flow
- 34:10Conclusions
related talks
55mProduction with ProxySQL and Orchestrator
41mAutopsy of a MySQL Automation Disaster
35mMySQL HA, Recovery and Load Balancing
46mDatabase Reliability Engineering for MySQL
21mHow Safe is Asynchronous Master-Master Setup?
24mComparing the High Availability of MySQL with Modern Distributed SQL Databases
14mThe State of the Art of MySQL Replication
08mMySQL as a Layered Service: How to use Proxy SQL to Control Traffic and Scale Out