MySQL Forums
Forum List  »  Replication

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Mysql 2 server replication setup with fail over
1272
September 25, 2011 01:20PM


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.