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: 12 » tags: , , ,

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

  • 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

  • Raul says:

    Hi, I did everything as you mention step by step, but I get the Slave_IO_Running and Slave_SQL_Running as No instead of Yes. What can I do?


    • Chris Wik says:

      Hi Raul, in the output of ‘SHOW SLAVE STATUS\G’ you should see a column ‘Last_SQL_Error’, what does this say?

      Also have you checked the MySQL log file on the slave? Usually it’s in /var/log/mysqld.log or in /var/lib/mysqld/

      Have you set the server_id variable correctly on the slave, to a different number from the master?

      • Raul says:

        Thanks for the quick reply Chris.
        I’m new to the whole MySQL replication stuff… I’ve done this step by step as you mention, but searching around, I see that for normal master-slave people use a START SLAVE; command, do I have to call that command? is that after the CHANGE MASTER command?

        Here’s my SHOW SLAVE STATUS \G
        Last_IO_Errno: 0
        Last_SQL_Errno: 0

  • Raul says:

    I have 4 servers, now that I started them as slaves and get the Yes from both IO and SQL running properties, I went ahead and did the table creation and testing that you mention, but it only gets replicated to the next server, so for example, if I do the table create in server 1, then it gets replicated in server 2 but the table wasn’t created in server 3 nor server 4. If do an insert in server 3, it’s replicated to server 4 but not in 1 nor 2, etc.

    I checked MySQL’s error log and there’s nothing. What would you recommend me to debug this situation?

    Thanks for the quick replies!

    • Chris Wik says:

      It sounds like you don’t have log-slave-updates enabled. Did you use the sample config from my blog post as a starting point? If not take a look at it…

      • Raul says:

        You are right Chris, I didn’t have that property, I missed it! the problem is that my servers are Windows Servers, so the my.ini config is different (ie. in Windows is required to configure the relay log or replication doesn’t work and doesn’ use a socket file).

        So I only took some properties from your example my.cnf file but completely missed the log-slave-updates property.

        The circular replication works now, thanks so much!

  • Mio says:

    how can I configure on server can replication to server if server was down.

    • Chris Wik says:

      This kind of circular replication topology is not self-healing! You would need to use CHANGE MASTER TO command to connect the nl server to us. It might be difficult or impossible to determine at which exact point in the binary log to start replication from though, you would have to look in the replication log on the nl server to see what the last replicated query was then try to find the position in the binary log on us where that query was logged, and start from there. It would probably be easier and safer to take a fresh consistent snapshot of the data on the us server and import it to the nl server and start two-way replication from there.

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>