pt-table-checksum

Prerequisites (Must Verify Before Running Checks)

• Only a single host can be specified, and it must be the IP address of the primary database.
• The tool adds S (shared) locks on tables during the check.
• If the binary log format on the primary or replica is not STATEMENT, you must use the --no-check-binlog-format option.
• Before execution, the replication I/O and SQL threads on the replica must be in the YES state.
• Tables must have a primary key or a unique key index. Row count differences can be detected without a primary or unique key, but content differences cannot be verified without one.
• An account that can access both the primary and the replica is required.
• The replica must have report_host and report_port configrued; otherwise, execution may fail with the error: Diffs cannot be detected because no slaves were found. Review the --recursion-method documentation for more details.

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 replicas

Common Parameters

--nocheck-replication-filters : Skip checking replication filters; recommended to enable. Use --databases to restrict the scope.
--no-check-binlog-format      : Skip checking the binary log format. If the format is ROW, an error will occur without this flag.
--replicate-check-only        : Only display out-of-sync information.
--recursion-method            : Method used to discover replicas.
--replicate=                  : Name of the table where checksum information is stored. It is advisable to write it into the database being checked.
--databases=                  : Comma-separated list of databases to verify.
--tables=                     : Comma-separated list of tables to verify.
h=192.168.1.128               : Address of the primary.
u=root                        : Username.
p=123456                      : Password.
P=3306                        : Port number.

Usage Examples

Granting Required Privileges

CREATE DATABASE pt;
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'pt'@'192.168.79.%' IDENTIFIED BY 'mysql';
GRANT CREATE, DELETE, UPDATE, INSERT, SELECT ON pt.* TO 'pt'@'192.168.79.%';

Runing Checks Between Primary and Replica

Execute on the primary host.

• Pay attention to the value of --recursion-method.

pt-table-checksum \
  --nocheck-replication-filters \
  --no-check-binlog-format \
  --replicate=pt.checksums \
  --databases=test \
  h=192.168.79.112,u=percona,p=mysql,P=3311 \
  --max-load Threads_running=200

pt-table-checksum \
  --nocheck-replication-filters \
  --no-check-binlog-format \
  --replicate=pt.checksums \
  --recursion-method=hosts \
  --databases=test \
  h=192.168.79.112,u=percona,p=mysql,P=3311 \
  --max-load Threads_running=200 \
  --chunk-size-limit=8

Inspecting Checksums on the Replica

SELECT
    db,
    tbl,
    SUM(this_cnt) AS total_rows,
    COUNT(*) AS chunks
FROM pt.checksums
WHERE (
    master_cnt <> this_cnt
    OR master_crc <> this_crc
    OR ISNULL(master_crc) <> ISNULL(this_crc)
)
GROUP BY db, tbl;

Sample result:

+------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db   | tbl        | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| test | runoob_tbl |     1 |   0.002375 | NULL        | NULL           | NULL           | 14dc5712 |        3 | 14dc5712   |          3 | 2019-06-24 11:34:59 |
| test | tbosc      |     1 |    0.00312 | NULL        | NULL           | NULL           | 5476b1ca |        2 | a32d2ec6   |          3 | 2019-06-24 11:34:59 |
+------+------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
2 rows in set (0.00 sec)

An empty result from the above query indicates that the checksums are consistant and the data across primary and replica is identical. Otherwise, they are inconsistent.

The output shows a discrepancy in row count between the current node and the primary.

Common Issue

• replica-is-stopped-when-replica-is-not

pt-table-sync

• After detecting inconsistencies between primary and replica, use this tool to repair out-of-sync data.
• Pay attention to privilege requirements; assign the necessary permissions based on any errors encountered.

pt-table-sync \
  --replicate=test.checksums \
  h=192.168.79.112,u=percona,p=mysql,P=3311 \
  h=192.168.79.112,u=percona,p=mysql,P=3312 \
  --print

Note for dual-primary (master-master) replication repair

• When operating on the second master to fix its own data:

pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl

pt-table-sync \
  --execute \
  --sync-to-master \
  h=10.25.1.10,u=root,p='xxx' \
  --databases=activity \
  --tables=xxx \
  --max-load thread_running=20