Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Infrastructure

I have two database servers, running on two different boxes. One works as a primary database for my web application and other works as secondary database. Secondary data base is updated by replication facility in mysql server. Primary database server works as master and secondary database server works as client.

Requirement

currently, my web application only interact with primary database. I want to add a functionality that will enable the application to use secondary database server if primary is down by some reason. Now, I need some tool/program to sync the changes made in secondary database to the primary database when it is up.

Basically I am looking for a feature that will enable my databases to be in sync all the time without fail. Please suggest me, how can I achieve this?

Thanks,

Vijay

share|improve this question
    
Have you looked into this, I do not think it is really what you are after but I have heard good things and it might be helpful: forge.mysql.com/wiki/MySQL_Proxy –  enterzero Jan 4 '12 at 4:49
add comment

1 Answer

up vote 3 down vote accepted

You need to do something called multi-master replication (aka master-master replication).

There are a number of guides that walk you through the process.

There are two ways to use a master-master replication setup. The improper way is to write (INSERT/UPDATE etc.) to both masters at the same time. Although MySQL provides configuration parameters that can help you if you decide to do this, I suggest you not rely on this behavior. Particularly, don't think that you can split your writes across the machines as some kind of load balancing, there is no such thing. All writes will occur on all servers.

The proper way is to use one master as a hot standby and only write to one master at a time. Trust me this is will save you a lot of heartache when things go wrong. There is even a tool, MMM (Multi-Master Replication Manager for MySQL) that can assist with monitoring and failover for this. It does require a 3rd server to monitor the masters though.

One final thing. Do NOT, I repeat, DO NOT try to "scale" outward by adding additional masters to create a loop. Like I said earlier, this is not scaling at all since every server must do every write from every other server. It is also a complete nightmare to repair replication for a non-transient failure of one of the masters. The loss of one server "breaks" every server in the loop after it, because they no longer are getting updates from the servers downstream from them.

share|improve this answer
    
There are also resources on this site if you search for "master-master replication". –  hobodave Sep 25 '10 at 5:19
    
Thanks for this explanation. I am going this way. I am not able to vote you up. :( –  vijay.shad Sep 25 '10 at 5:28
    
@vijay.shad: ill vote up for you :) J/K. I'll vote up for a good answer :)) –  Avada Kedavra Sep 25 '10 at 12:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.