Highly Available, Multi-Site MySQL
I’ve been playing with designs for this lately, and it’s been interesting, given some of the following considerations:
- The applications in question are (relatively) low volume, low transaction applications.
- Only one site will be active at any given time.
- Delays of 30 seconds or so are acceptable in case of failure within a site.
With that in mind, I’ve been playing with MySQL+Heartbeat/Pacemaker/Corosync+Multi-Master replication. MySQL + Heartbeat/Pacemaker/Corosync gives redundancy within a site, utilizing shared storage (though I could throw DRBD into the mix as well, though for little benefit). Multi-master replication allows for asynchronous replication between sites (hundreds of miles apart—high bandwidth but also relatively high latency) without impacting performance.
The one portion I was a little unsure about was how the MySQL replication would act when connecting to a VIP shared between two systems at a given site, but it seems to work just fine. I’ve got some more testing to do (quite a bit of testing, to be honest), but this should make my life (and job) relatively easy.
As a bit of an aside, MySQL multi-master replication does extend beyond two hosts, but it’s circular in nature. If you have four nodes (A, B, C, and D), your replication goes from A to B, to C, to D, and back to A. If you lose, say, node B, your replication is completely broken. This page includes a very, very clever use of stored procedures to work around this, but as ingenious as it is, that violated the KISS principle a bit too much for my tastes.