Circular Replication in MySQL
By Jeremy Smyth-Oracle on May 10, 2013
Replication is a hot topic in MySQL 5.6, and for good reason: There are many excellent features that make it a strong well-supported feature, from the new Global Transaction Identifiers (GTIDs), to simplified replication configuration and automated failover using MySQL Utilities (now available in alpha as a separate download).
The simplest topology consists of a master server that accepts changes, and slaves that replicate those changes from the master. A common requirement is for a network to have multiple servers that accept changes and replicate to each other. This is possible by means of circular replication, where each master is also the slave of another master, in a circular fashion. However, this configuration is prone to certain problems.
Firstly, you have to know a bit about how replication works. MySQL replication is asynchronous, which means each server executes operations without waiting for another server to replicate them. It does this by logging every event and subsequently transmitting these events to connected slaves. This works perfectly—without conflicts—when you have a single master that accepts changes from client applications. That master can have any number of slaves that can execute queries for applications, but don't change the contents of the database.
Circular replication introduces the possibility that two or more servers can replicate concurrent changes to each other. It is therefore is subject to conflicts, because it is possible that two servers update the same row at the same (or nearly the same) time and that each subsequently replicates its changes to the other server. If the replicated changes apply to the same row, then that row ends up with a different value on each server.
MySQL does not perform conflict resolution in such situations, so you have to make sure your application caters for that possibility.
For example, imagine a product that costs $520:
- The "promotions" team updates it on the sales server by subtracting $50
- At the same time, the "brands" team on the management server increases its price by 20%
|Operation||Sales server||Management server|
|(0) Initial price||$520||$520|
|(1) Promotions update (subtract $50)||$470|
|(2) Brands update (increase by 20%)||$624|
|(3) Promotions update replicates||$574|
|(4) Brands update replicates||$564|
|(5) Final price||$564||$574|
After each of the changes replicates to the other server—after being performed on its local server—the price on the sales server ends up being $564, and that on the management server (for the same product) is $574. There are no errors generated, because each server executes the statements in the order it receives them (either from a connected client application or a replicating master), which, due to the nature of asynchronous replication, is not always the same order on each server.
The preceding example describes a simple situation that could occur when you use statement-based replication. If you use row-based replication, you are not immune either, because there are several ways in which operations can conflict. Here are some further examples of conflicts that can occur whether you use statement- or row-based replication:
- One server deletes several rows based on a WHERE clause that should include a row that has just changed on the other server - the UPDATE propagates in one direction and the DELETE in the other, resulting in a row that exists on one server but not the other.
- One server updates a set of records at the same time as the other server updates an overlapping set
- Two servers generate an identical report at exactly 18:00 each night, but one server executes an update statement immediately before the report, that replicates to the other server just after it generates its report.
The simplest way to avoid conflicts in a circular replication topology is to ensure that each master server can only update rows that no other master server changes. For example:
- One master server updates rows within the sales table but not the products table, and the other updates products but not sales.
- The stock table can be updated by one master during the day, when the warehouse is packing and dispatching; another updates overnight when deliveries arrive.
- Masters can update each order based on the order's status. One master updates orders that have not yet shipped, another updates only shipped orders. Care must be taken that the order status changes in a controlled way, to avoid conflicts. For example, only the master with write access at that time (based on order status) can change its status; other masters use the order status to decide if they have write access at that time.
If you need to improve the performance of your replicated network, you can then scale out each master to multiple slaves to allow for greater bandwidth when running read-only queries. In practice, this means that you can load-balance queries across the slaves, and dedicate the master to handling writes for its tables.
Of course, to avoid conflicts all together, you should avoid circular replication and ensure that only one master accepts writes. But where would be the fun in that?