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: ,
Subscribe