MySQL replication between datacenters

I am trying to figure out the best MySQL replication topology, but I am not sure the best way to lay it out.

datacenter A | datacenter B
     ____    |     ____
    |    |   |    |    |
    | AA |   |    | BA |
    |____|   |    |____|
     ____    |     ____
    |    |   |    |    |
    | AB |   |    | BB |
    |____|   |    |____|
             |

These 4 MySQL servers are connected in pairs with AA and AB in Datacenter A, while BA and BB will be located in datacenter B. Datacenter A and Datacenter B are on different subnets to increase redundancy in the event of an ISP failure.

AA and AB will share a VIP using keepalived, heartbeat, or similar.

BA and BB will also share a different VIP using the same method.

I am trying to find the best way to use replication to ensure that all servers are kept in sync. Only one DB server will be written to at any given time (handled through HAProxy load balancer - not pictured)

I think there should be Master-Master replication between AA BB as well as Master-Master between BA BB.

Would it be better to use DRBD between AA AB as well as DRBD between BA BB?

If DRBD is used like this, how will I handle replication between datacenter A and datacenter B? I prefer something similar to master-master where a slave does not need to be manually promoted as this can take time for us to respond.

The HA aspect of this problem may be better suited on serverfault, but I am not sure.

I would definitely use a DRBD pair in one DataCenter and a DRBD pair in the other DataCenter.

I have written posts about using DRBB in this way:

• Mar 29, 2011 : MySQL high availability, failover and replication with Latency

• Mar 26, 2012 : 3 Databases, Best Replication Setup?

• Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication

Note that each DRBD pair would have a DBVIP for doing local failovers. Just setup Circular Replication between the DBVIPs only (which would be between the DRBD Primaries of each DataCenter).

Since you need MySQL Replication in addition to all this, I would use Semisync Replication to lower sensitivity to network latency.

Give it a Try !!!

StackExchange Database Administrators Q#46158, answer score: 2