How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs?
According to me, I record the
COUNT(*) from random tables after issuing
flush logs on the database. I then take binlogs generated until it flushes the binary file and ensuring the table is not altered while doing
SELECT COUNT(*). I then restore the binary logs in different database server and cross check
COUNT(*) to ensure the random tables are the same or not against production database and restored database.
Any other thoughts? Keep in mind that hits from application to tables can be paused only for a few minutes.