MySQL Circular Replication

I spoke about master – slave replication in my previous blog post and how to set up one way replication. The problem with one way replication is that any changes that are made to the slave would not be replicated to the master and would likely end up causing inconsistencies in future queries. This means that you can only apply updates on your master server.

If only two servers are involved then we can set up each server as both a master and a slave. This allows for two way replication – if changes are made on server 1 they are replicated on server 2, and vice versa. If more than two servers are needed for replication then it can get a little more complicated. Using the circular replication method we set up each server as a master, and as a slave to another master until all the masters have slaves. This results in a circular flow of information as each change is replicated to the next server in the chain. The below diagram indicates how replication changes would travel.

In the above illustration there are 8 MySQL Server nodes that all replicate changes they are sent directly and changes from the MySQL Server node before it are processed and sent to the next MySQL Server node along. A change made on any one of the MySQL Server nodes would be replicated to the next server along until every server in the chain has processed the update.

There are some drawbacks to to this type of configuration:

• When many nodes are added it can take a long time for a change to one server to make it round the chain to update all other servers. For example, if a change is made on Server 2, it has to go to Server 3, then server 4, 5, 6 and 7 before Server 8 is notified of the change.
• Conflicts can occur in MySQL Server circular replication. The way MySQL processes replication updates and direct SQL statements from clients is in parallel. MySQL does not guarantee consistency across all the databases in the chain. This is because a replication request may act on a column at the same time as a direct request from a client. Due to the time it takes to receive a replication update and process it, a direct request from a client could have updated the same row, even though, as far as the overall sequence of events is concerned, the initial event that triggered the replication from another server could have happened first.

Conflicts will have to be considered and affect the way a group of MySQL Servers set up in this way can be used. For example, you may choose to only update each table on one node at a time – Server 1 for the sales table, Server 2 for the payments table and so on. This will have to be built into your process for writing to these MySQL tables.

Setting up MySQL Server circular replication is straightforward – it’s essentially setting up master-slave replication multiple times for how many nodes have.

I’m not going to write out all of the commands for setting up circular replication because all the steps are written down in my blog post about master slave replication. Follow these steps to set up Server 1 as your master and then Server 2 as your slave. Then repeat the steps with Server 2 as your master and Server 3 as your slave. Continue doing this until you make your last server a master and then Server 1 as a slave to complete the circle.

And that’s all there is to it! Just remember to make sure you plan your workloads to protect your data from inconsistency issues.