MySQL Clustering vs. Replication (single & multi-master)

There is a really cool method of implementing replication in MySQL (5.0.24+, 5.1.12+) that allows you to have more than one replication master server for a data set without clustering. There are some really nice capabilities this gives you, however, there are some gotcha's with it too:

Pro's:

• Having dual replication masters allows an administrator to take one of the masters off-line during an upgrade allowing the other master(s) to handle requests during that upgrade. This provides a cheap form of high availability (HA).


• Hardware failures in a multi-master situation are less of an issue a s long as the software is configured to use more than one of the masters or a load balancer deals with sensing which host to use.


• Given a large enough pipe, multi-mastering makes it possible to have a physically remote master. You can't do that reliably with MySQL cluster (not replicated) because it requires extremely fast connections between the nodes of the cluster.


• No storage engine changes are required to implement for MyISAM (as opposed to Cluster requiring changing to NDB - a transactional storage engine).

Con's:

• Locking/Updating Race: It's possible for a lock to succeed in multiple masters simultaneously then fail on the "other" master(s). For example, a user double-clicks a submit causing the application to start two different threads of the same type. It's possible that neither will know about the other until it's "too late" if each thread gets a different DB server connection.


• Each DB still needs to have a full copy of the entire data set unlike cluster which can distribute copies of data across multiple servers.


• Suffers from al the problems of replication and the fragility it introduces.


• requires modifications to every table using auto_increment primary keys.


• Tables that don't use an auto-increment field as the primary key or tables that have a UNIQUE key constraint on a column or set of columns may allow inserts to succeed on separate masters then fail in replication.

MySQL multi-master replication can be a cheap alternative to MySQL cluster without some of the issues introduced by changing storage engines and incurring the expense of setting up the number of servers (and associated RAM, disk, networking, etc.) required to make MySQL cluster work efficiently.

To learn more about MySQL Multi-Master Replication, see:

• http://www.howtoforge.com/mysql_master_master_replication
  
• http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
  
• http://www.mysql.com/training/courses/mysql_5_ha.html
  
• http://www.google.com/search?hl=en&q=mysql+master+master+replication+auto_increment+site%3Adev.mysql.com