Friday, March 7, 2008

Tales from the DBA wars, Part I

Humorous Pictures

Good database administrators have to plan for the worst. You make a script to backup your data, make sure the script runs properly under cron, store copies of the backups off site, test to make sure you can restore from an old backup, and you still are almost comfortable with the safety of your data. Something in recesses of your mind whispers an almost audible message that you forgot to check one thing. 'What could it be?' you ask yourself in the sleepless hours spent looking for your Achilles Heel.

A friend sent me an email this morning to tell me he wanted to pull back an old copy of a large database that he had backed up months before. The backup was in a file named DEPT072-may-06.sql. So he did the following:

Linux> mysqladmin create scratchdb
Linux> mysql scratchdb > DEPT072-may-06.sql

And then he walked away to get a fresh cup of coffee. When he returned, he was surprised to find out the restore was still running. But it was a big database. Big databases take time to restore. A little while later he glanced over and was dumb founded to find that the restore was still running. Then he spotted the problem.

Or should I say, the first problem.

He meant to type:

Linux> mysql scratchdb < DEPT072-may-06.sql

He corrected the '>' and then found his second problem. The backup file he need to restore from was now a zero length file. D'oh!

So a word to the wise -- Good DBAs plan for all contingencies AND pay attention to what they are doing to avoid making a mess.


Anonymous said...

A good DBA would make a copy of the .sql and not try to recover directly from where the backup is stored. He would also have multiple copies of the backup, 1 onsite and 1 offsite.

Mark Schoonover said...

I agree with Artem, plus it's good practice for backup scripts to change the backup data file to read only. This should also be extended to tapes by moving the write protect tab to read only.