MySQL Forums
Forum List  »  Replication

Re: Replication Behind A Load Balancer
Posted by: Rick James
Date: December 21, 2014 04:39PM

If the code does not expect multi-Master, then you _will_ get corruption sooner or later.

> At least that much is OK.

No. In some extreme cases it will "flap" back and forth. (All traffic to one server, then none.) "Round robin" is usually safest and often close to optimal.

If the 3rd Party (Grrrr at 3rd party software!) uses AUTO_INCREMENT but does not let you set auto_increment_offset and auto_increment_increment, then writing to both sides of dual-master is absolutely a disaster.

> I guess we will need to write something that monitors

Yes.

> and perhaps automatically switches masters somehow.

I have watched lots of people try. Most succeed most of the time. Again, the end-cases will bite you.

Have a look at
http://yoshinorimatsunobu.blogspot.com/2014/03/mha-056-is-now-available.html
(and preceding blog posts about MHA). It is perhaps the best tool for _safely_ doing a Master-Slave failover.

Galera/PXC can also be run in a form of Master-Slave mode by having all writes go to one particular "node". The other nodes then serve as standby Masters.

In all of these "single" Master cases, you could have the load balancer pointing at only the one Master, yet easily changeable when you decide to switch Masters. The problem comes in _safely_ _automating_ the failover.

Options: ReplyQuote


Subject
Views
Written By
Posted
1783
December 19, 2014 09:49AM
1104
December 20, 2014 09:51AM
882
December 20, 2014 10:48AM
Re: Replication Behind A Load Balancer
944
December 21, 2014 04:39PM


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.