Does pt-table-checksum point out rows that are different between Master and Slave?

Question

Trying to verify replication integrity of my replica. And I found that pt-table-checksum is one of the best ways of doing it.

I have a simple Master->Slave set up. Have created a sample table with 10 rows on the Master, which has successfully replicated on the Slave. Intentionally on the slave I have changed a column in one of the rows of that table. Executed -

pt-table-checksum "h=host,u=user,p=password,P=3306" --function=MD5 --databases=test --tables=tentotal

And indeed in my checksums table that pt-table-checksum created, I could see different checksum for master and slave for that table. Excellent so far!

Output was -

TS                  ERRORS DIFFS    ROWS    CHUNKS  SKIPPED  TIME  TABLE
09-05T12:17:30      0      0        3       1       0        0.182 test.tentotal

Strangely the DIFF column says 0 which is incorrect. What am I missing here?

Percona tool kit version: 2.1.3

Answer 1

By default, pt-table-checksum displays as result all tables even if there is no error or diffs.

If you want to display just errors and diffs you can add the --quiet to the invocation of pt-table-checksum

Print only the most important information (disables --progress). Specifying this option once causes the tool to print only errors, warnings, and tables that have checksum differences. Specifying this option twice causes the tool to print only errors. In this case, you can use the tool’s exit status to determine if there were any warnings or checksum differences.

Answer 2

You will need to run pt-table-sync like this

pt-table-sync --print

Run this against all tables that have differences and redirect output to a text file. This will show what SQL has to be executed on the Slave to sync its contents with that of the Master.

If any output is generated, it will be an entire row replacement (using REPLACE (SQL)) rather than showing the specific columns that are different.