Friday, March 7, 2008
Tales from the DBA wars, Part I
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.