Friday, November 18, 2011

MySQLDump Data Integrity

Your backup is only as good as your backup. That's a silly way to say it, but of course it's true. You can have a backup running all year long, reporting no errors, but if you've never checked it and never made sure that your data is actually recoverable, i.e that your backup is actually a good backup, you are bound to be screwed.

I had a situation once where I was working with Symantec Backup Exec with a client who had GBs and GBs of data (everyone's home drives was mapped to the server with no quotas or limitations, and there were file shares scattered everywhere) using a Dell 8-slot drive and LTO4 tapes. I've rarely gotten better than 1:1 compression with tape, so that 400GB tape pretty much only stored about 400GB. We used them in slots, 2 per day, and it was a struggle to get the backups to complete successfully because they just had too much data and weren't willing to cut much out. We made some changes to their backups (I was working with one of our support center engineers who was definitely better than I was with Backup Exec and tape loaders and the whole partitioning scheme) and they finally started working. 

I went back to do a test restore about a month later as part of our standard environment checks, and found some weird discrepancies between how much data was supposedly being backed up and how much space was actually available in the library. Long story short: the overwrite protection period on the tapes had been changed to a week or less, and so the jobs were completing because they were overwriting data on other tapes...for the same week! They didn't have any complete backups; they had bits and pieces of data. Yikes!

Having kept that experience in my heart I set up my own schedule at my current company to verify the integrity of our backups. I am mostly concerned about the MySQL backups because that's customer data. People rarely turn to backups for recovery unless it's a system outage kind of scenario. I've rarely had someone ask me to go through backups to find a deleted Word doc (although it has happened). So, I set out to find ways to test my MySQL dumps to make sure they were actually completing correctly.

I was surprised to find that there are no built-in utilities in MySQL to test the integrity of the dumped files. The only solutions I've found are pretty surface. You can restore the database and run queries against it to verify that the data is there. This of course isn't a fail-proof method, especially if you have many different databases. You can also do a select count on your tables and also run mysqlcheck.Again, if you have multiple databases this can become unwieldy. Sounds like a script that iterates through the databases and runs a range of tests would be handy. Anyone else have methods for checking the integrity of multiple databases? 

No comments:

Post a Comment