You are here

Home

Check and fix MySQL Replication inconsistencies

There are many different possibilities how you can achieve inconsistencies between Master and Slave in a MySQL replication, intentional and non-intentional ones. How to achieve them is not the topic of this article.

What we want to know is how can we detect inconsistencies or differences between Master and Slave and how can we fix them.

To find those inconsistencies or differences we need 2 tools from the Percona Toolkit: pt-table-checksum and pt-table-sync.

Requirements

The following requirements have to be met to find the differences:

  • We need a running Master/Slave replication.
  • We need a user to do the checks and repairs. Run on Master the following command:
    GRANT ALL ON *.* to ptc@'%' identified by 'secret';
  • If you use non default ports for MySQL (3306) the following variables have to be set on the Slaves:
    # my.cnf
[mysqld]
report_host = slave
report_port = 3307

Introduction of differences

To test if and how the tools work we introduce some inconsistencies into a test table:

  • INSERT a row on master without replicating it.
  • UPDATE a row on slave.
  • DELETE a row on slave.

Now we have the following situation:

Data on Master

+----+------------------------+---------------------+
| id | data                   | ts                  |
+----+------------------------+---------------------+
|  2 | pt-table-checksum test | 2013-12-05 11:30:28 |
|  3 | pt-table-checksum test | 2013-12-05 11:30:28 |
|  4 | pt-table-checksum test | 2013-12-05 11:30:53 |
+----+------------------------+---------------------+

Data on Slave

+----+------------------------+---------------------+
| id | data                   | ts                  |
+----+------------------------+---------------------+
|  1 | pt-table-checksum test | 2013-12-05 11:30:27 |
|  2 | manipulated            | 2013-12-05 11:31:29 |
|  3 | pt-table-checksum test | 2013-12-05 11:30:28 |
+----+------------------------+---------------------+

Checking for data inconsistencies

To check for data inconsistencies run the following command on the Master:

./pt-table-checksum h=master,u=ptc,p=secret,P=3307 --set-vars innodb_lock_wait_timeout=50 --no-check-binlog-format --databases=test --tables=test

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-05T11:32:40      0      1        3       1       0   0.048 test.test

Fixing the data inconsistencies

To see how the inconsistencies from the Master against the Slave will be fixed run the following command on the Master:

./pt-table-sync --sync-to-master h=slave,u=ptc,p=secret,P=3307 --databases=test --tables=test --print

DELETE FROM `test`.`test` WHERE `id`='1' LIMIT 1;
REPLACE INTO `test`.`test`(`id`, `data`, `ts`) VALUES ('2', 'pt-table-checksum test', '2013-12-05 11:30:28');
REPLACE INTO `test`.`test`(`id`, `data`, `ts`) VALUES ('4', 'pt-table-checksum test', '2013-12-05 11:30:53');

To fix the inconsistencies from the Master against the Slave run the following command on the Master:

./pt-table-sync --sync-to-master h=slave,u=ptc,p=secret,P=3307 --databases=test --tables=test --execute

tags: 
replication