VPS restoration from backup kills your InnoDB database — don’t let it happen to you!

A month ago my then VPS provider, JaguarPC, has some really freaky hardware issues, that to this day I have no idea what happened and they ended up restoring a two week old backup of the whole server which included my VPS. When I fired up this blog and a couple of other sites they failed due to mysql table corruption. The corrupt databases that used Myisam tables seemed to repair just fine, but all of my InnoDB databases (Rails uses InnoDB by default when you use migrations) were unrecoverable and I ended up having to try other means for getting my data back or at least as much of my data that I could get back.

Here is what I learned:

  1. Never assume that your hosts backups of your VPS will work when they are restored because they perform backups while the server is running and databases don’t like that too much.
  2. Always keep backups of your databases, especially the ones that use the InnoDB table engine, in a SQL dump format.

So here is what I do now to prevent this from happening again:

  1. Perform your own backups of your databases using the methods that are suggested for your db and db table engines.
  2. Get the data into SQL so when your VPS is backed up it will properly backup a dump.

Assuming that you have a file that contains a list of databases with one per line, you can do something like the following and then hook up your script t cron.


cd /var/lib/mysql

if [ ! -d sql_backup ]; then 
  mkdir sql_backup

for db in $(cat databases.txt); do echo $db; mysqldump --single-transaction $db > sql_backup/${db}.sql; done

Good luck, and oh BTW, you might want to get this running on your VPSbefore your host does the restore. 😉



Tags: ,

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: