Architecture for highly available MySQL with automatic failover in physically diverse locations
- by Warner
I have been researching high availability (HA) solutions for MySQL between data centers.
For servers located in the same physical environment, I have preferred dual master with heartbeat (floating VIP) using an active passive approach. The heartbeat is over both a serial connection as well as an ethernet connection.
Ultimately, my goal is to maintain this same level of availability but between data centers. I want to dynamically failover between both data centers without manual intervention and still maintain data integrity.
There would be BGP on top. Web clusters in both locations, which would have the potential to route to the databases between both sides. If the Internet connection went down on site 1, clients would route through site 2, to the Web cluster, and then to the database in site 1 if the link between both sites is still up.
With this scenario, due to the lack of physical link (serial) there is a more likely chance of split brain. If the WAN went down between both sites, the VIP would end up on both sites, where a variety of unpleasant scenarios could introduce desync.
Another potential issue I see is difficulty scaling this infrastructure to a third data center in the future.
The network layer is not a focus. The architecture is flexible at this stage. Again, my focus is a solution for maintaining data integrity as well as automatic failover with the MySQL databases. I would likely design the rest around this.
Can you recommend a proven solution for MySQL HA between two physically diverse sites?
Thank you for taking the time to read this. I look forward to reading your recommendations.
