MySQL Forums
Forum List  »  Replication

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


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:

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

Written By
Mysql 2 server replication setup with fail over
September 23, 2011 05:58PM

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.