Not the answer you need?
Register and ask your own question!

pt-table-checksum slows point in time recovery

scottbscottb EntrantCurrent User Role Beginner
Master/Slave replication.
Backup Slave nightly.
Run pt-table-checksum on Master nightly.

We periodically restore from a nightly Slave backup and replay Master binary log files from the time of the backup to a specific date/time.

Replaying the binary log files includes the statements that take a checksum of all dbs/tables. Depending on the recovery could be multiple days.

Is there a recommended way of excluding the checksum statements from going to the Masters binary log?
Or excluding them from the mysqlbinlog export or mysql replay?

Thanks Scott

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    I wonder why you have to check the data consistency every day, do the data inconsistencies happen that often? If so, maybe you should try to prevent them, like by using super_read_only setting on the slave, etc.

    The pt-table-checksum tool does work that way, it logs the checksum statement to the binary log, so that the slave can execute it's own checksums. So, no way to skip those from being binlogged.
    However, when you do full restore later, you may use PITR technique by setting up temporary "master" and use replication filters to skip those. Check out this method here: https://www.percona.com/blog/2017/10/23/mysql-point-in-time-recovery-right-way/
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.