Mistakes Were Made: Upgrading Database Edition

So today, on a personal project, a plugin I’m using pointed out that I’m not using the most up to date database software on my server, so I went to backup my database and upgrade the database software, unfortunately, the upgrade went wrong and the backup was empty.

All of the work, approximately twenty hours or so, was gone.

But, I had a copy of the IBD and FRM files for the database. This meant that I had the essence of the database, but there was no easy way to make it go live again.

I was so frustrated. I had dozens of tabs open, asked some people that I knew were smarter than me at server management and database administration and the consensus was that there was no easy way to get my data back. I was going to have to start over…

I spent the afternoon trying everything I could find in hopes of getting back up and running without starting over, and by the end of the day, I was successful. I was able to restore about 90% of my work and the last 10% isn’t super important and would take less time to redo than restore.

It all worked out because of the IBD files and discarding and importing the tablespace as seen at this StackExchange Discussion – How to Recover an InnoDB table whose files were moved around.

I’ve activated additional backup tools so that this doesn’t happen again and I’ll definitely check backup file sizes before doing something so risky in the future.

Please learn from my mistake, and don’t just backup, but check that you have good backups!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s