Fix MySQL Replication Inconsistencies with pt‑table‑checksum & sync

In many workplaces MySQL replication (especially version 5.1.x) often encounters problems such as master or slave crashes that interrupt replication, and the need to promote a slave to master while ensuring data consistency.

Tool Installation

1. Download Percona Toolkit:

[root@MySQL-01 ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

2. Install required dependencies:

[root@MySQL-01 ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

3. Install the toolkit package:

[root@MySQL-01 ~]# rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm
Preparing... ########################################### [100%]
 1:percona-toolkit ########################################### [100%]

Using pt-table-checksum

Run the tool on the master to compare checksum values between master and slaves:

pt-table-checksum --nocheck-replication-filters --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306

If the slave uses MIXED binlog format, add --no-check-binlog-format to suppress the check.

When no slaves are found, specify the recursion method, e.g.:

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=yayun.checksums --databases=yayun --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306

Supported --recursion-method values:

METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
cluster      SHOW STATUS LIKE 'wsrep_incoming_addresses'
dsn=DSN      DSNs from a table
none         Do not find slaves

Ensure the slave can be discovered by configuring report_host and report_port in /etc/my.cnf and granting the master appropriate privileges:

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.10' IDENTIFIED BY '123456';
flush privileges;

After a successful checksum run, a non‑zero DIFFS column indicates data drift. The results are stored in the yayun.checksums table.

Using pt-table-sync

To synchronize the inconsistent tables, use pt-table-sync:

pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --print

The command prints SQL statements that delete the extra rows on the slave. Replace --print with --execute to apply the changes automatically.

pt-table-sync --replicate=yayun.checksums h=127.0.0.1,u=root,p=123456 h=192.168.0.20,u=root,p=123456 --execute

After synchronization, rerun pt-table-checksum to verify that DIFFS is zero and the master and slave tables contain identical rows.

Important notes:

A single account that can log into both master and slave is required.

Only one host (the master IP) should be specified.

The tool acquires a shared lock on the tables during the check.

Both the master and slave replication I/O and SQL threads must be in the YES state before running the check.

Tables without a unique index or primary key cannot be synchronized and will cause an error.

With proper privileges (SELECT, PROCESS, SUPER, REPLICATION SLAVE) and careful preparation, Percona Toolkit provides an efficient way to detect and fix MySQL replication inconsistencies.