Mysql – 3 Databases, Best Replication Setup

MySQLreplication

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!

Best Answer

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.

Related Question