2

Which of the following method is the most fault tolerant way to achieve cross datacenter MySQL replication using DNS failover and why?

  1. MySQL master-master replication with one primary/active node and the other acting as hot spare. When the active node is down, the DNS failover switches to hot spare. Question: Does this setup achieve two-way sync reliably & automatically since both nodes are slaves to each other? If yes, the only manual action required would be to switch DNS to primary once its up and sync is complete.
  2. MySQL master-slave replication with master as primary/active node and slave acting as hot spare. When the active node is down, the DNS failover switches to hot spare. This setup requires manual recovery of master from slave after failover as explained here or here. Question: Is this more reliable or fault tolerant than option 1? (I've read of master-master replications having issues with conflicts and sometimes even where both the DBs are out of sync).

What other better options are there that are similar in complexity to setup/manage?

Updates:

Tested out option 1 today with a very complex stack and so far so good. The DNS FO and fault-tolerance is pretty good for production use.

Improve this question
7
  • Having just two servers risks the "split brain" scenario -- where both servers are alive, but the network between them is dead, so you can't tell that the 'other' server is really not down.
    – Rick James
    Feb 27, 2017 at 18:57
  • I agree this might be the case when using just plain MM replication. But combined with DNS failover -w/ system monitoring- where the switch only happens in case of a "real" downtime, this shouldn't be an issue. Consider M1-M2 servers in MM replication. M1 -active- has DNS FO configured. If M2 -hot spare- goes down its synced once its up; no disruptions. If M1 -active- goes down (verified by multiple location monitoring) then the DNS FO routes to M2. This FO takes 2-3 min and while not as impressive as 1s claimed by PerconaDB, this is good enough for most uses.
    – Pavin Joseph
    Feb 27, 2017 at 20:42
  • Monitoring is vulnerable to network outages, hence it sometimes cannot tell the difference between a server being down and split brain.
    – Rick James
    Feb 27, 2017 at 21:13
  • If 3-6 monitoring attempts all fail from multiple locations, then it can be safely considered that the active node M1 is down and FO can switch to hot spare M2. Since we're not writing to both disks at the same time and the spare is not lagging behind I don't see how this can result in a split brain condition esp. since DNS FO removes M1 with network/host problems from use. M2 now being the active node will sync the changes to M1 once its up/reachable. I apologize for keeping at it, but the norm seems to be cluster setup with arbitrator and so on; but have never seen anyone use MM with DNS FO.
    – Pavin Joseph
    Feb 27, 2017 at 22:36
  • contd.. Am I missing something important with the setup I suggested? Haven't seen MM with DNS FO (& monitoring) implemented or discussed elsewhere. Appreciate your inputs :)
    – Pavin Joseph
    Feb 27, 2017 at 22:39

0

Reset to default