Skip navigation links

MySQL Forums :: Replication :: Mysql 2 server replication setup with fail over


Advanced Search

Mysql 2 server replication setup with fail over
Posted by: Steven SRS ()
Date: September 23, 2011 05:58PM

Hi,

I have a website which is GEO balanced on 2 servers in the UK and US (both with own domain .eu and .com). Currently they are rsync-ed and have a master - slave setup where the UK server is master. Now when the master goes offline (which happens too often) the whole eCommerce system isn't working anymore.
(Note that both servers have multiple other websites running which are running independently and do not need replication)

So i'm looking for a setup to solve this problem. So far I've found that a master-master setup would likely solve the problem partly as the ecommerce functionality wouldn't be offline if UK server is offline neither if US server is offline, however as far as I've researched looks like master - master setup has some drawbacks and when one server goes down and then comes back online it will most likely have replication issues which need manual fixing which is more a pain then just with master - slave setup.

So O think I need is a master - master with fail-over solution, however this seems to be very difficult with just 2 servers. I saw a nice setup on howtoforge but in this setup one master goes down the other one takes over the master function and when first master comes back online it will be slave. Which sounds good, but I don't see any method of determining which one is master in my php scripts to know where to to writing / editing the database..

Does anybody have some suggestion for this problem?

I found this which seems interesting:
https://code.google.com/p/tungsten-replicator/wiki/TungstenReplicatorCookbook#Install_bi-directional_replication

I think this does what I need, however I could use a bit more of step-by-step on what to do (a howto would be nice).

Also a second idea I had is:
Setup a 2nd fallback copy database on slave (realtime up-to-date copy)
Then setup a script which always runs directly when master comes back online which checks fallback database with differences, updates itself
and normal operations continues

issues with 2nd idea; haven't found anything to real-time replicate a database to another database locally (or do master - slave setup from 1 master database to 2 slaves databases on same server; *edit: perhaps maatkit can help me here). And not quite sure how to best setup a script run only once at startup on the master.

Any suggestion, advice on this would be highly appreciated!

Best regards,

P.s. also willing to pay some one for setting this up!

*edit some more info about setup



Edited 1 time(s). Last edit at 09/23/2011 07:13PM by Steven SRS.

Options: ReplyQuote


Subject Views Written By Posted
Mysql 2 server replication setup with fail over 2261 Steven SRS 09/23/2011 05:58PM
Re: Mysql 2 server replication setup with fail over 640 Irfan Ali 09/24/2011 05:26AM
Re: Mysql 2 server replication setup with fail over 724 Rick James 09/25/2011 01:20PM
Re: Mysql 2 server replication setup with fail over 574 Steven SRS 09/27/2011 01:11AM
Re: Mysql 2 server replication setup with fail over 619 Irfan Ali 09/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.