Using pt-table-checksum to Verify MySQL Master‑Slave Data Consistency

1. Overview

pt-table-checksum, a component of Percona Toolkit, checks whether data on a MySQL master and its replicas are identical by executing statement‑based SQL on the master to generate checksums for data chunks, replaying the same statements on replicas, and comparing the resulting checksums. It processes large tables by dividing them into row blocks, adjusting chunk size to keep each checksum query within a target execution time (default 0.5 seconds), thereby minimizing replication lag and server load.

2. Scenarios

The tool works out‑of‑the‑box for most environments, even with thousands of databases and trillions of rows, because it checks one table at a time and dynamically adjusts chunk size based on server load. It can automatically discover and connect to replicas, and will pause on a replica if replication lag exceeds a configurable threshold.

3. Safeguards

pt-table-checksum includes numerous safety features: it detects replicas and connects automatically, skips chunks that are too large, limits innodb_lock_wait_timeout to one second to avoid blocking other queries, and monitors server load, pausing if the number of concurrent queries exceeds a default limit (e.g., 25). It also handles replication filters, refusing to run queries that could break replication unless explicitly forced.

4. Operational Steps

4.1 Create Master‑Slave Architecture

Set up MySQL replication and create a replication user (e.g., GRANT REPLICATION SLAVE ON *.* TO repl@'10.186.63.%' IDENTIFIED BY '123'; ), then start the slave.

4.2 Generate Test Data

Use sysbench to create tables and populate them on the master, which will replicate to the slave:

# sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=10.186.63.82 \
--mysql-port=4380 --mysql-user=gengjin --mysql-password=123 \
--mysql-db=test --table-size=1000000 --tables=10 \
--threads=50 --report-interval=3 --time=20 prepare

4.3 Install pt‑table‑checksum

# Download and extract Percona Toolkit
wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz
yum -y install perl-devel perl-Digest-MD5 perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL.noarch perl-Time-HiRes
cd percona-toolkit-3.1.0/
perl Makefile.PL PREFIX=/usr/local/
make
make install

4.4 Important Parameters

--replicate-check : after each checksum query, store results in percona.checksums and optionally compare CRC32 immediately (default yes ).

--replicate-check-only : query only the existing percona.checksums table without running new checksum queries.

--nocheck-binlog-format : skip verification of binlog format; required when replicas use ROW format.

--replicate= : specify the database/table where checksum results are stored (default percona.checksums ).

4.5 Execute Checksum

Standard port example:

# pt-table-checksum h=10.186.63.82,u=gengjin,p='123',P=3306 \
  --databases=test --tables=sbtest1,sbtest2 \
  --nocheck-replication-filters

DSN‑based example for non‑standard ports, multiple replicas, or full‑instance checks:

# Create checksum table
CREATE DATABASE IF NOT EXISTS percona;
CREATE TABLE IF NOT EXISTS percona.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;

# Insert replica DSN
INSERT INTO percona.dsns(dsn) VALUES('h=10.186.63.83,P=4380,u=gengjin,p=123');

# Run checksum with DSN recursion method
pt-table-checksum \
  --replicate=percona.checksums \
  --nocheck-replication-filters \
  --no-check-binlog-format \
  --max-load Threads_connected=600 \
  h=10.186.63.82,u=gengjin,p='123',P=4380 \
  --recursion-method dsn=h=10.186.63.83,u=gengjin,p='123',P=4380,D=percona,t=dsns \
  --function MD5

The command outputs per‑table checksum statistics, creates the percona.checksums table automatically, and records any differences found.