Which of the following method is the most fault tolerant way to achieve cross datacenter MySQL replication using DNS failover and why?
- 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.
- 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?
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.