Re: Mysql 2 server replication setup with fail over
Posted by:
Rick James
Date: September 25, 2011 01:20PM
Master-Master is helpful when you need to switch over or failover. One Master is read/write; one is readonly. Once you have determined that read/write Master is really dead (not just unreachable), you can promote the back to being read/write.
Note that if _either_ Master has a disk crash, you have to take the surviving machine offline (or at least avoid writes) to do a new rsync/mysqldump/etc to a new box. For this reason, additional slaves are recommended.
Automated failovers have flaws when there is a partial network outage -- Each Master can each decide that the other is dead, and promote itself to read/write. This leads to terrible data corruption (such as duplicate AUTO_INCREMENT keys).
OK, so you try to have both Masters read/write. And you use "auto_increment_increment" etc to deal with the AUTO_INCREMENT issue. If there are UNIQUE keys, and the application might hit both Masters at the same time (due to coincidence, repeated query, etc), then the is the potential for trouble. MySQL has no "conflict resolution" to deal with such. Replication in both directions will freeze, awaiting human intervention.
Subject
Views
Written By
Posted
4071
September 23, 2011 05:58PM
1265
September 24, 2011 05:26AM
Re: Mysql 2 server replication setup with fail over
1272
September 25, 2011 01:20PM
1089
September 27, 2011 01:11AM
1171
September 27, 2011 10:23PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.