Hacker News Reader

Comment by Locke | Hacker News Reader

It's easy to say, "What? No backups... how stupid can you be?" But, without knowing the particulars, I wouldn't necessarily jump to that conclusion. It's very easy to have regular automated backups fail in some way.

Unfortunately, it's not enough just to have backups. You have to actually verify that they're correct and up-to-date. Verification is easy when your database is small (for example). You can just load it in your development environment occasionally. But, how do you verify your backups if you have hundreds of gigs or even terabytes of data?

As an example, I've seen cases where backups were successful every night... but, they were being run against a slave db and replication had failed. The result: Excellent backups of weeks old data.

View on HN

A backup is only a backup when you have restored from it successfully. Until then, it's no better than garbage.

A disk raid is only a raid when you have tried pulling a disk and having the raid successfully rebuild (while running in production!).

I once had a system administrator who didn't dare pull a raid disk while in production. After that, has wasn't a sysadm in my eyes, he was a lottery gambler.

Being paranoid doesn't mean nobody is following you.

That's nasty. Maybe you could automatically inject test data into a fake account and run test queries on the backup. That will not verify you are getting everything but at least that you got something recent.

Verifying backups is almost always many times more difficult than setting up the backups themselves. Checking that replication was in fact working turned out to be fairly easy to automate (although, short of checksumming all the records in all the tables you can't really be 100% sure everything is okay).

That's the thing with backups, they can fail or become corrupt in many ways. If you don't use them for something on a regular basis or have some regular verification process you may not know what you have until it's too late.

And, of course, I've also seen situations where subtle data corruption in the master database leads to weeks of subtly corrupt backups. By the time the corruption was discovered we faced the choice of rolling back several weeks to the last good backup or fixing the corruption. In the end we had to do a kind of merge -- it was a real pain.

If I just have one MySQL machine and take daily database dumps, what would be good way of testing that my backups are OK?

The best situation for me has been one of my personal projects. It's a gaming site that I use everyday. The database dump is less that 100MB gzipped, so I load it in my development environment every week or so. That way as I develop I'm verifying (to some extent) the quality of the backup.

As a baseline, you should at least restore from your backups occasionally.

It helps that I'm familiar with the data -- so after restoring the backup I expect to see games, messages, forum posts, etc that I've just seen in production.

I do some more thorough automated tests on the backups less frequently. App specific things, like replay games and verify results, etc. This process is more about assuring backward compatibility with the code though.

I think verification should ultimately be somewhat app specific. That said, I'm sure you can find tools to help with verification.

Perhaps creating a virtual machine (Xen, VMWare, whatever) on your development box and using that to test restoring your backups?

Reply on news.ycombinator.com