Continuous MySQL backup validation: Restoring backups

Re:Continuous MySQL backup validation: Restoring backups by predakanga on Friday October 28, 2016 @06:48PM (#12821425)

Interesting, though I was hoping for a bit more focus on the validation part - unless I'm missing something, there's nothing protecting the actual SQL dumps against bitrot.

I had to design against that particular problem recently, ended up taking a pt-table-checksum at the time-of-dump and verifying newly restored backups against that to ensure the backup's integrity.

Unfortunately that requires halting replication temporarily, so I was hoping to hear of a more ingenious solution.

[ Parent | on HN ]

Re:Continuous MySQL backup validation: Restoring backups by jivid on Friday October 28, 2016 @07:09PM (#12821551)

There's a few different ways to verify, a few of them involving stopping replication, like you pointed out. These can also sometimes be quite expensive, so depending on the type of verification required, the verification method can be tuned.

We also implemented table checksums inside of mysqldump, allowing us to dump out the restored data and compare checksums as well, if required. https://github.com/facebook/mysql-5.6/commit/54acbbf915935a0...

[ Parent | on HN ]

Re:Continuous MySQL backup validation: Restoring backups by predakanga on Friday October 28, 2016 @07:18PM (#12821598)

Very nice, thanks for the link!

I'll definitely be seeing if I can replace my system with that; reducing the overhead to a single transaction would be a big win.

[ Parent | on HN ]