MySQL replication between datacenters

MySQLreplication

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.

Best Answer

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

MULTIMASTER SETUP

I would recommend a multimaster setup only with the idea that you restrict Asia's data to one master, and Europe's data to the other master. As for the shared databases, make sure auto_increment_increment and auto_increment_offset are in full use. In addition, make sure you only write Asia's data to the shared databases at the Asian DC and likewise with Europe.

STORAGE ENGINE CONSIDERATIONS

If there is no specfic reason to keep MyISAM (such as FULLTEXT indexing or heavy volume reads), I would recommend converting everything to InnoDB. That way, if a server crashes, then crash recovery can bring databases to a consistent state. With MyISAM, tables can be left in a crashed state and repairing those tables may result in the disappearence of one or more rows of data.

HIGH AVAILABILITY

You should strengthened your HA Setup by implementing DRBD. DRBD should be setup as at each DC. What are the benefits?

You maintain a disk-level replica of the mysql data within both data centers
You can have automatic failover within each data center
You only worry about MySQL Replication between DCs

REPLICATION

To minimize data loss with MySQL Replication over geographic distance, I would recommend using smaller binary logs. By default, a binary log/relaylog is 1G (max_binlog_size and max_relay_log_size). You can make these values much smaller. I got this idea from PostgreSQL because WAL files are 16M by default. This creates more log files, but those log files will be closed and complete faster on the acting slave. If you are not using it, you should upgrade to MySQL 5.5 because it has Semisynchronous Replication. It can be tuned to detect heartbeat timeouts with some granularity.

I wrote about some of these concepts in an earlier post back on March 29, 2011.

Mysql – Scaling Percona datacenters: setup and replication

In this instance, you actually have two choices

CHOICE #1 : Percona XtraDB Cluster

I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).

You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS

Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.

Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.