Finding differences between a master and slave when pt-table-sync fails
Here's a method that does not interrupt service on the master, though it does lock the slave temporarily.


Stop the slave.

mysql&gt; STOP SLAVE;

Dump the offending table on the master with mysqldump in a repeatable-read transaction. Include the binary log coordinates in the output (--master-data). Output one INSERT per row (--skip-extended-insert).

$ mysqldump --single-transaction --master-data=2 --skip-extended-insert 
  mydatabase myoffendingtable &gt; master-dump.sql

Note the binlog coordinates in the dump file, and start slave until the coordinate shown in the master's dump file.

mysql&gt; START SLAVE UNTIL MASTER_LOG_FILE='xxxx' MASTER_LOG_POS=yyyy;

Wait until the slave thread catches up to that position and stops itself. This shouldn't take long.

mysql&gt; SELECT MASTER_POS_WAIT('xxxx', yyyy);

Dump the table on the slave, also with one row per INSERT.

$ mysqldump --skip-extended-insert mydatabase myoffendingtable &gt; slave-dump.sql

Resume replication.

mysql&gt; START SLAVE;



Now you can diff the dump files, and be assured they represent exactly the same logical point in the stream of changes. This allows you to find data discrepancies.