You may know about the MySQL Cluster, which is a complex architecture to achieve high availability and performance. One of the advantages of MySQL Cluster is that each node is a peer to the others, whereas in a normal replicating system you have a master and many slaves, and applications must be careful to write only to the master. The main disadvantages of MySQL Cluster are (as of MySQL 5.0): The database is in memory only, thus requiring more resources than a normal MySQL database. (MySQL 5.1 introduces table spaces, with the capability of storing nonindexed data on disk.) Some normal features are not available, such as full-text searches, referential integrity, and transaction isolation levels higher than READ COMMITTED. There are some cases where the MySQL Cluster is the perfect solution, but for the vast majority, replication is still the best choice. Replication, too, has its problems, though: There is a fastidious distinction between master and slaves. Your applications must be replication-aware, so that they will write on the master and read from the slaves. It would be so nice to have a replication array where you could use all the nodes in the same way, and every node could be at the same time master and slave. There is the fail-over problem. When the master fails, it’s true that you have the slaves ready to replace it, but the process of detecting the failure and acting upon it requires the administrator’s intervention. Fixing these two misfeatures is exactly the purpose of this article. Using features introduced in MySQL 5.0 and 5.1, it is possible to build a replication system where all nodes act as master and slave at the same time, with a built-in fail-over mechanism. Setting Up a Multimaster Replication System For those of you not well acquainted with the replication basics, I can refer to an earlier article explaining MySQL replication, and the demanding reader can integrate with the dry but extensive official MySQL replication manual. Back to business. Consider the situation where you set up a replication system with more than one master. This has been a common scenario over the past several years. Chapters 7 and 8 of Jeremy Zawodny’s High Performance MySQL describe such a solution. At the time of the book’s publication, though, the necessary technology was not yet available. One hard-to-solve problem in a multimaster replication is the conflict that can happen with self-generated keys. The AUTO_INCREMENT feature is quite convenient, but in a replication environment it will be disruptive. If node A and node B both insert an auto-incrementing key on the same table, conflicts arise immediately. Rescue comes with recent versions. MySQL 5 introduces a couple of server variables for replicated auto-increment that address this specific problem and allow for the creation of an array of peer-to-peer nodes with MySQL replication. Quoting from the manual: auto_increment_increment controls the increment between successive AUTO_INCREMENT values. auto_increment_offset determines the starting point for AUTO_INCREMENT column values. By choosing non-conflicting values for these variables on different masters, servers in a multiple-master configuration will not use conflicting AUTO_INCREMENT values when inserting new rows into the same table. To set up N master servers, set the variables like this: Set auto_increment_increment to N on each master. Set each of the N masters to have a different auto_increment_offset, using the values 1, 2, … , N. Using those two variables as described in the manual, you can ensure that all nodes in your replication array will use different sequences of auto-incrementing numbers. For example, using auto_increment_increment = 10 and auto_increment_offset=3, the numbers generated when inserting three records will be 3, 13, 23. Using 10, 7, you’ll get 7, 17, 27, and so on. For my four-node array, I set auto_increment_increment to 10 for each node, and auto_increment_offset to 1 in the first node, 2 in the second, and so on. This is theoretically clear, but it still isn’t clear how I managed to transform these servers into peer-to-peer nodes. The answer is a circular replication, where each node is master of the following node and slave of the preceding one. Circular replication with two nodes In its simplest form, circular replication has two nodes, where each one is at the same time master and slave of the other (Figure 1).
...