MySQL verify backups [duplicate]

Question

There's standalone MySQL server 5.5 with no slaves. Daily dump is created using mydumper tool.

Is there any way to verify created dump (all tables are created, integrity, etc.) without restoring dump somewhere, using slaves, etc..?

Answer 1

This is one of the best posts I've seen on this (regular enough here) topic. Seems to be the quickest way to test a backup.

What you've got to remember is that no process is 100% cast-iron guaranteed to restore your databases - there's the thorny issue of money.

Basically, every extra 9 (3, 4, 5 nines) that you put on to your procedures will (my own rule of thumb here) put the price of your backup and restore procedures up by a 0 - i.e. a factor of 10! Are you going to be checking every backup you make? You can follow industry best practice and make it crystal clear that that is what you are doing (to your bosses/shareholders/stakeholders) and that if they want more guarantees, they'll have to pay and pay lots!

You can talk about clusters, master-slave replication, master-master, geo-replication, RAID10 SSD's - the number of solutions for HA MySQL is huge, but the prices get huge pretty fast also!

Best practice is testing backups ~ 6 weeks - a role for a junior DBA - you should take a full backup once a week with incremental backups every night when traffic is low (for MySQL I strongly recommend Percona's Open Source XtraBackup).

Motto of the story, formulate a backup and restore plan with the other stakeholders (explaining the pros and cons - risk, cost &c.) and follow industry best practice and you won't go far wrong!