Setting Up Multi-Master Circular Replication with MySQL

March 22, 2011 under The 100% Uptime Challenge

Circular replication diagramThe goal of this configuration is to allow each server to operate autonomously for a period of time should it ever lose contact with the other 2 servers, while doing everything I can to make sure there will be no conflicting database updates once connectivity resumes. Each server will act as both a Master and a Slave with replication going in a circle. The binary log, which is written by the master and read by the slave, contains the name of the MySQL server which wrote it. Therefore a loop will never happen, as once the query comes back around to its original master, the master will know that it has already been executed.

I’ll attempt to prevent key conflicts by using the auto_increment_offset and auto_increment_increment configuration variables, to ensure AUTO_INCREMENT values will be unique to each server. This may also prove handy as I’ll be able to tell which server created a record, just by looking at the key value.

There is one remaining potential problem which I can’t guarantee won’t happen, that is the problem of the order of updates. Based on my lovely sketch of the replication circle, consider this scenario:

  • loses connectivity to, interrupting replication
  • user A updates a post on UPDATE posts SET title=’Some new title’ WHERE id=123;
  • the post does not get replicated to because the link is down
  • later, user B updates the same post on UPDATE posts SET title=’Some other title’ WHERE id=123;
  • user B’s update gets replicated around the circle to
  • the connection between nl and us comes back up
  • the original update statement from gets replicated to us and nl servers
  • we now have inconsistent data:
    • title on nl: Some other title
    • title on us: Some new title
    • title on ie: Some new title

It’s an unlikely scenario, but plausible. One potential workaround would be to design your application such that records never get updated, but instead create a new record for every change. You can then always select the newest record based on a timestamp column, so in the above scenario all servers would show ‘Some other title’ as the newest blog post title.

Another possibility, should you consider the likelihood of this scenario limited for your application, would be to store a checksum of each row and compare the checksums across all the servers. Any inconsistencies could be flagged and/or an administrator notified.

I already installed mysql-server earlier, so let’s get down to the configuration!

Step 1: Enable the MySQL service and create a config file. I’ve posted my config as an example. This will need to be done on all 3 servers.

chkconfig mysqld on
vi /etc/my.cnf (use my config as a starting point, or create your own - not forgetting to set
server_id and auto_increment_offset appropriately)
service mysqld start

Step 2: Log in to your new MySQL server using ‘mysql -u root’

Step 3: Set your root password and configure your replication user (do this on each server):

USE mysql
DELETE FROM user WHERE user='';
UPDATE user SET password=PASSWORD('your new root password') WHERE user='root';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'' IDENTIFIED BY 'somethinglongandrandom';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'' IDENTIFIED BY 'somethinglongandrandom';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'' IDENTIFIED BY 'somethinglongandrandom';
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin-ie.000003 |      685 |              |                  |

Now on each server, use CHANGE MASTER to start slaving the next server in the circle, until each server is both master and slave. Use the log file name and position as shown in SHOW MASTER STATUS.

  MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-ie.000003', MASTER_LOG_POS=685;

  MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-nl.000003', MASTER_LOG_POS=685;

  MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-us.000003', MASTER_LOG_POS=685;

Step 4: Replication should now be up and running in a multi-master, circular configuration. Use ‘SHOW SLAVE STATUS\G’ on each server to check that it’s working properly. Check that Slave_IO_Running and Slave_SQL_Running both report Yes. Once it’s all working properly, create a test table to check that the AUTO_INCREMENT values are being created properly (with unique values per server):

mysql ie> USE test;
mysql ie> INSERT INTO test (val) VALUES ('I am Ireland');
mysql nl> INSERT INTO test (val) VALUES ('I am the Netherlands');
mysql us> INSERT INTO test (val) VALUES ('I am the USA');
mysql ie> INSERT INTO test (val) VALUES ('I am Dublin');
mysql nl> INSERT INTO test (val) VALUES ('I am Amsterdam');
mysql us> INSERT INTO test (val) VALUES ('I am Chicago');

Check the table contents and note the id values:

mysql> SELECT * FROM test;
| id | val                  |
|  1 | I am Ireland         |
|  2 | I am the Netherlands |
|  3 | I am the USA         |
| 11 | I am Dublin          |
| 12 | I am Amsterdam       |
| 13 | I am Chicago         |
6 rows in set (0.00 sec)

Looking good! Note the records created by the Irish server always end in 1, the Dutch server always end in 2 and the American server always will end in 3.

comments: 4 » tags: , , ,

4 Responses to "Setting Up Multi-Master Circular Replication with MySQL"

  • Brian says:

    Thank you ever so much, I read hundreds of documents and found yours to be simple, concise and most helpful.

    I was able to get replication running on my cluster very swiftly after reading your post.

  • Hi, i’m configuring multi-master circular replication, howto run with incremental id?

    • Chris Wik says:

      In my.cnf (see my example linked in the post above), you need to set auto_increment_offset and auto_increment_increment parameters:

      # Each server should generate auto_increment values in a different range
      # to prevent key conflicts in multi-master replication configuration.
      # For example in this case AUTO_INCREMENT will generate values:
      # 1,11,21,31,41,etc.
      # server 2 will generate:
      # 2,12,22,32,42,etc.
      auto_increment_offset = 1
      auto_increment_increment= 10

Pings responses :

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>