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.

We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of the master/multiple-slaves kind i.e. db1 as the master, and db2 and drdb as the slaves. drdb is activated only when both db1 and db2 are down i.e. disaster recovery, business cont. sort of thing. Our application also has a rudimentary failover 'feature' in which it automatically connects to the next available db server in case the default active db server i.e. db1-to-db2-drdb, is inaccessible.

I am currently considering the idea of applying a master-master replication between db1 and db2, and making drdb as a slave of db1. In this setup, I'l make db1 as the default active db server (performs all writes and read operations) and db2 the passive one (backup db server). When db1 goes down, db2 becomes the active db server, and dynamically change the master-host of drdb to db2. And so on.

Is this a good idea? What is a better replication setup for this kind of environment? Please give some suggestions.

UPDATE 1:

I also would like to add that these databases are already existent, and I've read somewhere the possibility of key-collisions/error in this kind of setup, what modifications in our current tables/databases configuration should we made before we setup this kind of replication?

Thank you very much!

share|improve this question
    
I also would like to add that these databases are already existent, and I've read somewhere the possibility of key-collisions/error in this kind of setup... Would using GUIDs instead of integer IDs help with this - that should prevent key collisions? –  Suman Apr 26 '12 at 19:14

1 Answer 1

I wrote up an interesting layout last year which features DRBD pairs in two data centers (DC1,DC2) with as follows

  • DRBD Pair in DC1 (db1 and db2)
  • DBVIP for Primary of DRBD Pair1 is 10.1.2.30
  • DRBD Pair in DC2 (db3 and db4)
  • DBVIP for Primary of DRBD Pair2 is 10.1.2.40
  • Have MySQL Circular Replication Between DRBD Primaries
  • Have the 10.1.2.40 as Master_Host for DBRD Pair 1
  • Have the 10.1.2.30 as Master_Host for DBRD Pair 2

MySQL high availability, failover and replication with Latency

MySQL Replication : 1 Slave / Multiple Masters

Here is why I suggested this: Using two data centers, you setup automatic failover for DRBD Pair in one data center. Let the other DRBD Pair in the second data center be for DB disaser site with it own local redundancy and failover. Should you ever loses one data center, the other data center is fully read with it own local failover setup. Your app would just have to use the DBVIP of the other database center in such a catastrophic case.

Please keep in mind that using DRBD in conjunction with MySQL is only beneficial if all of your data uses the InnoDB Storage Engine. Hard failovers in DRBD could easily result in crashed MyISAM tables.

Here is another setup to consider:

As with DRBD setups, any DRBD Secondary would provide just a Disk-Level copy of your MySQL Folder. It is available as a warm standby. MySQL is not being run on the DRBD Secondary. If you want the third DB server to become hot standby, ditch DRBD altogether and use pure MySQL Replication. With three DB servers, using db3 at a remote site, simply setup the following:

+--> db1 --> db2 --> db3 -->+
^                           |
|                           V
+<--------------------------+

Using your rudimentary failover, now you have two hot standby servers. You just have to make sure each DB server has a unique server_id value. I also recommend using MySQL 5.5 because it uses SemiSync Replication which is more sensitive to communication failures and stop Replication better. You will have to setup the appropriate heartbeats and timeouts.

share|improve this answer
    
I don't know, but I feel that the name we have for the 3rd database in the DR site i.e. drdb, brought out some form of confusion.(?). We are not currently using a DRBD technology, and I don't think we are going to, because as you said in your answer, not all of our tables are InnoDB. And yeah, about the idea of upgrading to MySQL 5.5 from a previous version, how easy do you think is it? Is a downtime necessary and for how long? Thank you very much Rolando! –  ultrajohn Mar 27 '12 at 5:29
    
Sorry, I'm asking a lot of questions. So, what are differences between the setup I was considering(in the post) to the second one you suggested. I mean, wouldn't it be a good idea to apply master-master replication between db1 and db2, so db1 would just replicate from db2 in case it goes down, as compare to replicating from db3 (in your 2nd setup), considering they are on a different data center i.e. latency, etc. ? –  ultrajohn Mar 27 '12 at 5:45

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.