Multi-Master Replication Verification And Conflict Resolution With Maatkit

March 29, 2011 under The 100% Uptime Challenge

I mentioned briefly in Setting Up Multi-Master Circular Replication with MySQL how a particular situation could potentially arise in which data becomes inconsistent across the MySQL masters, but replication does not fail. The two scenarios where this could happen are near-simultaneous updates to the same record on different servers, or the same record being updated on two servers while replication is down (eg. due to a link failure, where we get a “split brain” scenario)

One way to avoid this kind of problem is to design your application such that records are never updated. Instead, create a brand new record for every update. With the help of a timestamp column, you can then always select the newest version of the record by date. Removing the Update_priv from your MySQL user’s access privileges will ensure your app never UPDATE’s a record, even unintentionally.

In many cases the DBA will not have any control over the queries sent to the database server. This is my case with the www.cwik.ch blog, which runs on WordPress and does occasionally use UPDATE queries. While data inconsistency should be extremely rare under normal operation, it would definitely be a good idea to be able to verify data integrity after an incident of replication failure (for example because the link between two of the servers went down temporarily)

Thankfully this problem has already been tackled by folks more knowledgeable on the topic than myself and a great toolset released under the name maatkit. On CentOS 5 you can install maatkit from yum, simply ‘yum install maatkit’. I’ve created a simple demonstration to illustrate how it works:

# Create a table to store checksums in:
mysql ie> CREATE DATABASE maatkit;
mysql ie> USE maatkit
mysql ie> CREATE TABLE checksum (
    db         char(64)     NOT NULL,
    tbl        char(64)     NOT NULL,
    chunk      int          NOT NULL,
    boundaries char(100)    NOT NULL,
    this_crc   char(40)     NOT NULL,
    this_cnt   int          NOT NULL,
    master_crc char(40)         NULL,
    master_cnt int              NULL,
    ts         timestamp    NOT NULL,
    PRIMARY KEY (db, tbl, chunk)
);

# Create a test table and populate it with some test values:
mysql ie> USE cwik
mysql ie> CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    val VARCHAR(255) NOT NULL
);
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> SELECT * FROM test;
+----+----------------------+
| id | val                  |
+----+----------------------+
|  1 | I am Ireland         |
|  2 | I am the Netherlands |
|  3 | I am the USA         |
+----+----------------------+

# Stop the slaves temporarily. For this test just IE and NL.
mysql ie> SLAVE STOP;
mysql nl> SLAVE STOP;

# Update the same record on both servers
mysql nl> UPDATE test SET val='Ich bin Niederlande' WHERE id=2;
mysql ie> UPDATE test SET val='Ik ben Nederland' WHERE id=2;

# Re-start the slaves
mysql nl> SLAVE START;
mysql ie> SLAVE START;

# Check the test table contents
mysql ie> SELECT * FROM test;
+----+---------------------+
| id | val                 |
+----+---------------------+
|  1 | I am Ireland        |
|  2 | Ich bin Niederlande |
|  3 | I am the USA        |
+----+---------------------+

mysql nl> SELECT * FROM test;
+----+------------------+
| id | val              |
+----+------------------+
|  1 | I am Ireland     |
|  2 | Ik ben Nederland |
|  3 | I am the USA     |
+----+------------------+

Note that IE and NL have replicated each other’s updates, the result is the value of record #2 is switched! Now I can use the maatkit tools to flag this inconsistency, and then fix it:

# Use mk-table-checksum to checksum all the tables in the cwik database.
# You'll see a list of tables and checksums
[cwik@ie ~]$ mk-table-checksum -u root -pmy_supersecret_pw --algorithm BIT_XOR \
  --databases cwik --emptyrepltbl --replicate maatkit.checksum localhost

# Use mk-table-sync with --print to see the conflicts that have arisen
[cwik@ie ~]$ mk-table-sync --print --synctomaster --replicate maatkit.checksum \
  u=root,p=my_supersecret_pw,h=nl.cwik.ch,D=cwik
REPLACE INTO `cwik`.`test`(`id`, `val`) VALUES (2, 'Ich bin Niederlande');
# Cool, note that cwik.test.id=2 is out of sync

# Update nl.cwik.ch with the corrected record:
[cwik@ie ~]$ mk-table-sync --execute --synctomaster --replicate maatkit.checksum \
  u=root,p=my_supersecret_pw,h=nl.cwik.ch,D=cwik

Re-checking the test table on both IE and NL shows we now have consistent data!!

mysql ie> SELECT * FROM test;
+----+---------------------+
| id | val                 |
+----+---------------------+
|  1 | I am Ireland        |
|  2 | Ich bin Niederlande |
|  3 | I am the USA        |
+----+---------------------+
mysql nl> SELECT * FROM test;
+----+---------------------+
| id | val                 |
+----+---------------------+
|  1 | I am Ireland        |
|  2 | Ich bin Niederlande |
|  3 | I am the USA        |
+----+---------------------+

I’ve just touched the surface of a very complex topic here. More reading on maatkit and all it can do is definitely warranted.

One more thing: running such a complex database system is far from risk free, so to further mitigate the risk of data loss or corruption, I set up regular MySQL backups on all 3 of my servers. It’s pretty simple to do with mysqldump and cron:

mysql ie> GRANT SELECT, LOCK TABLES ON cwik.* TO 'backup'@'localhost' \
  IDENTIFIED BY 'my_supersecret_pw';
mysql ie> FLUSH PRIVILEGES;
mysql nl> FLUSH PRIVILEGES;
mysql us> FLUSH PRIVILEGES;

[cwik@ie ~]$ sudo mkdir /backups
[cwik@ie ~]$ chown cwik:cwik /backups
[cwik@ie ~]$ crontab -e
0 3 * * * mysqldump -u backup -pmy_supersecret_pw --single-transaction \
  cwik | gzip > /backups/`date +\%d`.sql.gz
:wq

I installed this cron job on all 3 servers, creating a daily snapshot of my ‘cwik’ database which I can use to restore a consistent copy should I mess anything up terribly. Combined with the binary log (which is enabled already as we’re using replication) I feel confident that I could never lose any important data. Recovering from an inconsistent setup could well be complex, but with the proper backups and logs, possible.

comments: 1 » tags: ,

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:

  • nl.cwik.ch loses connectivity to us.cwik.ch, interrupting replication
  • user A updates a post on nl.cwik.ch: UPDATE posts SET title=’Some new title’ WHERE id=123;
  • the post does not get replicated to us.cwik.ch because the link is down
  • later, user B updates the same post on us.cwik.ch: UPDATE posts SET title=’Some other title’ WHERE id=123;
  • user B’s update gets replicated around the circle to nl.cwik.ch
  • the connection between nl and us comes back up
  • the original update statement from nl.cwik.ch 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'@'80.93.25.175' IDENTIFIED BY 'somethinglongandrandom';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'83.96.156.169' IDENTIFIED BY 'somethinglongandrandom';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'67.202.99.74' IDENTIFIED BY 'somethinglongandrandom';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| 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.

On nl.cwik.ch: CHANGE MASTER TO MASTER_HOST='ie.cwik.ch', MASTER_USER='repl',
  MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-ie.000003', MASTER_LOG_POS=685;

On us.cwik.ch: CHANGE MASTER TO MASTER_HOST='nl.cwik.ch', MASTER_USER='repl',
  MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-nl.000003', MASTER_LOG_POS=685;

On ie.cwik.ch: CHANGE MASTER TO MASTER_HOST='us.cwik.ch', MASTER_USER='repl',
  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> CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, val VARCHAR(255) NOT NULL);
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: , , ,
Subscribe