OR: When work is a little like duck hunting with Dick Cheney.
Today we had a bit of a scare. Someone decided to "clean up the databases".
Who needs to worry about this:
mysql> drop foo;"oh... you mean that 'foo' was in use ? ? I was sure it was useless."
Rule 1: Always backup before you delete.
Always. Always. Always.Memorize this: # mysqldump -u username -ppassword database_name > FILE.sql
Thankfully the db was for an app with only a small amount of data. So though a loss would be sad, it would not be catastrophic. The other saving grace is that the app involves undergrad students, not say, Vice Presidents, or Dept heads. This is good as undergrad students are slightly above janitors in the pecking order.
I was training a new person when I got pulled in to the issue. So she got to experience the heavy breathing and waving of hands. Now that she has been exposed to the dark underbelly of our procedures on her first day, If she decides to stay, at least she has seen the worst.
This brings us to:
Rule 2: Backups are nice, but Restores are nicer.
It turns out that the restore plan was to copy all the files back in to place in the database directory in question. We kept restoring the files from backups, and guess what, they all had the original creation date still. No data! We questioned our backups. But something seemed fishy to me, so I poked around and noticed that the other database directories had additional files, e.g. .myi and .myd files - and this directory had only .frm files.
From this I discovered -(thanks Google: )
So I was pretty much able to restore the ibdata file and not lose anything. And Someone was pretty happy.
