Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a web app with 2 web servers and 2 database servers. The DBs are setup for multimaster replication. (this is the primary environment)

I also have the exact same setup on a different location acting as standby, in case the primary env fails. (this is the backup env)

What I need is for the backup env to be in sync with the dbs of the primary site. However, all DBs in both environments have already replication configured.

How can I achieve my goal?

Thanks

share|improve this question
    
+1 for this type of question because it lets DRBD be suggested as part of the infrastructure and as part of the solution : meta.dba.stackexchange.com/questions/207/…. –  RolandoMySQLDBA Aug 29 '11 at 17:03
    
Oops, I forgot the +1 from before. Upvoting now... –  RolandoMySQLDBA Aug 29 '11 at 19:09

1 Answer 1

up vote 3 down vote accepted

This is actually an interesting scenario because I addressed this in the DBA StackExchange back on March 29, 2011.

The basic idea would be to use MySQL Replication in conjunction with DRBD and ucarp

Here is what you would need:

  • Two DRBD Clusters, one in each DataCenter
  • DRBD Pair at each DataCenter connected via a Crossover Cable (perhaps using 192.168.x.x subnet)
  • DRBD Primary has MySQL Running
  • DRBD Primary at one DataCenter running MultiMaster Replication with the DRBD Primary at other DataCenter via ucarp (in both directions)
  • Any needed read slaves at each Data Center would the DBVIP established for use within MySQL at that DataCenter via ucarp

Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

CAVEAT

This setup will not prevent table corruption, particularly if MyISAM is involved. Even with DRBD, block-replication of a corrupt MyISAM table would produce...you guessed it, a corrupt MyISAM table in the DRBD Secondary. Therefore, it is preferable if all user data were InnoDB.

share|improve this answer

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.