How to Detect and Fix MySQL Replication Data Inconsistencies with Percona Toolkit

Yesterday two MySQL servers were migrated; the master was re‑connected today, but the replica’s binlog position started from the current time, causing data loss for the period of the migration.

Problem

The goal is to identify the inconsistencies between master and replica without affecting business, and then fill the missing data on the replica.

Possible Solutions

Re‑sync from scratch – heavy on performance and network.

Dump the master, lock it, then sync – impacts business.

Use Percona Toolkit tools (pt-table-checksum and pt-table-sync) – fits the current situation but requires learning.

Operation Process

Both machines run CentOS 6.5 with MySQL 5.6. Sensitive information such as IPs and passwords has been redacted.

Master: 192.168.1.100

Replica: 192.168.1.98

Database to fix:

radius

Tool Installation (on master)

# yum install perl-DBI perl-DBD-MySQL perl-TermReadKey perl-Time-HiRes # wget percona.com/get/percona-toolkit.tar.gz # tar zxvf percona-toolkit-2.2.14.tar.gz # cd percona-toolkit-2.2.14 # perl Makefile.PL && make && make install

Data Consistency Check

Create a user with the same name on both master and replica so the tools can connect.

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.100' IDENTIFIED BY 'slavecheck'; flush privileges;

Run pt-table-checksum on the master:

pt-table-checksum h='192.168.1.100',u='checksums',p='slavecheck',P=3306 -d radius \ --nocheck-replication-filters \ --replicate=radius.checksums

Key options: --nocheck-replication-filters: ignore replication filters. --replicate: store checksum results in radius.checksums. --databases, --tables: limit scope.

The command creates a table radius.checksums on the master (must be replicated to the replica).

Table Creation (if needed)

CREATE TABLE IF NOT EXISTS `radius`.`checksums` ( db CHAR(64) NOT NULL, tbl CHAR(64) NOT NULL, chunk INT NOT NULL, chunk_time FLOAT NULL, chunk_index VARCHAR(200) NULL, lower_boundary TEXT NULL, upper_boundary TEXT 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=INNODB;

If the table already exists on the master but not on the replica, grant the replica appropriate privileges.

Check Results

Sample output shows rows with non‑zero DIFFS, indicating tables where data differs.

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 06-16T16:50:21 0 1 8379 4 0 0.322 radius.account_account 06-16T16:50:21 0 1 11429 1 0 0.278 radius.account_mac ...

Query the radius.checksums table to list the mismatched tables.

SELECT * FROM radius.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G

Fix Inconsistent Data

Use pt-table-sync based on the checksum results. Backup the master first because the tool writes to it.

# On master pt-table-sync --execute --replicate radius.checksums \ --sync-to-master h="192.168.1.98",P=3306,u="checksums",p="slavecheck" \ --ignore-tables radacct,django_session

If permission errors occur, grant the user full rights on the database:

GRANT ALL ON radius.* TO 'checksums'@'192.168.1.100'; flush privileges;

After syncing, run pt-table-checksum again to verify that no differences remain.

Summary

The main points are:

All commands run on the master; the replica is only used for verification.

Create a dedicated user with appropriate SELECT, REPLICATION, and DML privileges.

Use pt-table-checksum to locate inconsistent tables, then pt-table-sync to reconcile them.