Mysql – is Master-Master replication ok for me

multi-masterMySQLreplication

I have two separated locations connected by a not too reliable VPN. I have a common system that depends on MYSQL that read/write tables.
Will master-master replication keep both locations in sync?
I don't care that tables might not look exactly the same in time on both servers (for example, rows on both masters when VPN fails, might get a different order after both locations write transactions…)

Note: Not doing auto-increment / offset settings

Best Answer

I have a third party app that is using a master-master MySQL system and if your network/communication is not good you may have some issue with your replication.

Types of errors you may encounter is 1062 (duplicate key).

Right now to avoid this error we put it in the slave_skip_errors in themy.cnf file.

Also if your network/communication is not good is not good, your instances may keep and failing back and forth.

It can work but you will need a lot of fine tuning and monitoring.

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 – Best way to setup master to multi master replication

There are four(4) options you need to combine

Option 1: MySQL in Star Topology

So as not to reinvent the wheel, please read my past posts about this subject

Mysql Master-Master Replication Topologies on >2 machines
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504
https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444

Option 2: Use DRBD

The supermaster should be a DRBD pair and using a DBVIP
The Star Topology's distribution master should replicate from the DRBD primary
All Slaves should replicate from Star Topology's distribution master
See my past post on using DRBD in Circular Replication between two datacenters (DRBD cannot sync well over geographic distance but can provide disk-level + failover capability per data center)

Option 3: Use Semisynchronous Replication

Any network latency can affect the collecting of binary logs and its shipping of entries over a network could be bottleneck. Using MySQL 5.5's Semisynchronous Replication can allow you to tune the MySQL Heartbeat characteristics so as to minimize replication losing its place due to any significant network latency.

Option 4: Circular Replication

If you have multiple masters, circular replication is a must. There are many products out there for providing circular replication, such as mysql-mmm (mentioned in the comment), which is great within the confines of a single datacenter. This is only one piece of your puzzle in that four(4) things must be accommodated outside of just circular replication:

Network latency
Geogrpahic Distance
Disk-level Redundancy per DataCenter (handled by DRBD)
Automatic Failover of DBVIP backbone of DRBD (via ucarp)

CAVEAT

This may not be your full answer but these options can provide ideas you can try out in different scenarios. For example, when in the star topology, you could do the following:

SCENARIO #1

Have the DB Server at one data center be SuperMaster holding InnoDB data
Have the Distribution Master in the same data center (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #2

Have the DB Server at one data center be SuperMaster holding no data, which means the supermaster is the Distribution Master (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #3

Setup a Distribution Master in each Data Center in Circular Replication
Within each Data Center, have MySQL Instance (Local SuperMaster) with InnoDB as the only Storage Engine
From each Local SuperMaster, hang two or more read slaves. Each read slave should use MyISAM as the main Storage Engine. Each MyISAM table should have ROW_FORMAT=FIXED to increase read speed 20-30%

As far as the possibilities go, it's up to your imagination and department budget.

Links for MySQL and DRBD

http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-drbd.html
http://downloads.mysql.com/docs/mysql-ha-drbd-en.a4.pdf

DRBD for Disk Level Redundancy and ucarp for DBVIP Automatic Failover

http://www.drbd.org
http://www.ucarp.org