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 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.

share|improve this question
    
Questions like this can be asked here : meta.dba.stackexchange.com/q/207/877 –  RolandoMySQLDBA Jul 11 '13 at 16:49
add comment

1 Answer 1

up vote 2 down vote accepted

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:

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 !!!

share|improve this answer
    
Yet again a great response @RolandoMySQLDBA! Thanks for the help! This is exactly what I need. –  Ethan Hayon Jul 11 '13 at 15:10
1  
Adding one more suggestion ... –  RolandoMySQLDBA Jul 11 '13 at 15:10
    
My personal preference is using ucarp. Here is a post I made about using DRBD/ucarp for PostgreSQL: dba.stackexchange.com/a/14215/877. You can follow the same principles for MySQL as well. You can also replace ucarp with heartbeat, HAProxy, DNS Failover, etc. –  RolandoMySQLDBA Jul 11 '13 at 15:17
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.