You are here
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