MySQL Multi-Master (Circular) Repliclation

With a little bit of work tonight, I now have a multi-master MySQL database configuration running.  The setup is pretty slick, and surprisingly simple to set up.   I have been playing with simple Master-Slave configurations for a while now, and recently came across this OnLamp article about Circular Replication

The basic concept is pretty straightforward – each server writes its own binary log as normal.   You also enable log-slave-updates which writes updates received from the master server to its own binary log as well, so that those are passed on to the next server in the  circle.

I actually used a slightly modified version of the circle.   I have several servers at one location.  The ‘main’ master MySQL instance is on one of those, and the others all are slaves to it.   We recently added a server at a remote location and made it a simple slave as well.   All of the servers sent updates to the single master.

The problem I had was that the server at the remote location is about 70-80 ms away, which works fine for some applications, but I’m adding a feature that will potentially do 30 or so updates on each request.   Multiply 80 ms times 30 updates, and there is some noticable problems.

My new setup has updates being written locally on the remote server, and then using MySQL replication to get that back to the old master server, which then relays it to the rest of the servers in that location.  The remote server still receives updates from the main master server.  I can now update either server, and updates propagate to all servers in very near real-time.  It is very slick.

Note that this might not be suitable for all sorts of data and all situations.  My data is an extremely simple key => value caching system.  I use REPLACE INTO statements so that it doesn’t matter if a given key already exists or not.  Also, if my data becomes inconsistent, it also is not a big deal, as the caching system will just fail, and the application will proceed without using the cached results.

Previously, the major obstacle to this setup was when using automatically incrementing INSERT_ID’s and it was possible for each master server to use the same ID.   MySQL 5 introduced the auto_increment_increment and auto_increment_offset settings which makes it possible to guarantee that will never happen.   There still are some potential problems where statements not executed in the same order on all nodes may result in inconsistent data. So use with caution and make sure you understand the potential issues.