Pt-table-checksum

MySQL & MariaDB
1

Can someone help out with this tool? I would like to use this tool to find out the discrepancies in my two databases. Well they are in master to slave replication I believe the Slave has some data the master doesn’t have. Any other ways to figure out this discrepancies? NOTE This is a big database about 1TB EACH.

2

Run pt-table-checksum on the source/master. You can use -d and -t to limit to specific databases and tables if you like. After the script is done, go to your replicas and run this SQL: SELECT db, tbl FROM percona.checksums WHERE this_crc != master_crc and that will show you which tables are different.

Then you need to run pt-table-sync --execute --sync-to-master --replicate percona.checksums h=localhost on your replicas with differences. This will cause pt-table-sync to run any SQL needed to add or delete data from the replica to match the source.

1 Like
3

pt-table-sync will make the replica match the source. If there is extra data on the replica that is not on the source, it will be removed from the replica. If there is extra data on the source that is not on the replica, it will be added to the replica.

1 Like
4

Thanks a lot! Do I need to create checksum user at all ? This is the confusion. According to the documentation by percona. I haven’t seen any but there is a blog written by “Irfan” on this tool in his demonstrations he created a user. If it’s not needed for this tool to work I’ll just move on

1 Like
5

You can use any user with read/write privileges to the tables you are checksumming and also grant privileges to the percona database to store the checksum data. You can use a different database/table for the checksum data if you like by adding --replicate mydatabase.mychecksums to both tools.

1 Like
6

After running pt-table-checksums on master it gave me a message like” my slave has binlog_format ROW which could break replication. Anyways it created a percona database in the Slave still. I used the query you mentioned and it gave me an empty set. Does it mean no discrepancies or in doing something wrong

1 Like
7

Is there any data on the replica inside that table? If not, then the checksum process did not finish. You can safely use --no-check-binlog-format to remove that warning as long as your replica does not also have a replica.

1 Like