Tuesday, November 17, 2009

Recovering from "database cleaning" - mySQL - innoDB

OR: "How to cover your ass when your belt snaps and you forgot to wear your braces.  (Proof of the wear clean underwear rule.)
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:

When you can just do it yourself :
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: )

That it was an innodb database not a myisam db, and so the data was stored elsewhere (by default in an ibdata1 file, in the directory one level up)

So I was pretty much able to restore the ibdata file and not lose anything. And Someone was pretty happy.

No comments:

Post a Comment