How to Restore MySQL Data from a Crashed Server
First off let me say that I hope you never have to deal with this. It’s terrible. I thought I had my servers sufficiently backed up, but I didn’t. Back up your database servers multiple ways; it’ll definitely pay off. Seriously.
OK so let me put you into the thick of it: through a terrible set of circumstances you have an unbootable Ubuntu server and can only recover the data. No other backups exist. A lot of data is on the line. Here’s how to be a hero.
Disclaimer: I’m not actually sure this is the best or right way to do this, but it worked for me. I am repeating this from memory so I apologize in advance for errors. Proceed at your own risk.
Setup a new MySQL server
I use the Rackspace Cloud, so spinning up a new server was no problem for me. If you’re using real hardware, good luck. Next, get MySQL up and running. Hopefully if you’re this far in you know how to do that.
Compress the data directory on your crashed server
You’ll need to start the crashed server in recovery mode if your cloud-ish host offers that or mount the drives in a working machine. In my case the data was stored in /var/lib/mysql. If you don’t know where your data is, check your MySQL config file. Look for
/etc/mysql/my.cnf. Compressing the directory would look something like this:
$ cd path/to/mount/var/lib/ $ sudo tar -czvf mysql.tgz mysql/
Once you’ve gzipped your data, scp it over to your new server.
$ scp mysql.tgz user@newserverip:
Run your new server with your old data
Login to your new server. Your compressed data should by in your home folder. Decompress your data.
$ tar -xzvf mysql.tgz
Now let’s stop MySQL:
$ sudo /etc/init.d/mysql stop
Move the new MySQL data for safe keeping:
$ cd /var/lib $ sudo mv mysql mysql_new
Copy your old data into place and fix the permissions:
$ sudo cp -r ~/mysql mysql $ sudo chown -R mysql:mysql mysql/
Now it would be great if you could just start MySQL and call it a day, but there is good chance you’d have a mess. So instead we’re going to start MySQL and have it ignore permissions so that we can dump the data with
mysqldump. Start MySQL, ignoring grant tables:
$ sudo mysqld_safe --skip-grant-tables &
You should now be able to connect to MySQL as root without a password and poke around in your databases. This is a good time to do a sanity check and make sure everything is in order.
$ mysql -u root
Once you feel good about what’s in there, it’s time to dump the data. You can dump all the data at once, and if you have lots of databases that is probably your best bet. I prefer to do it one database at a time so that I can selectively restore. Let’s head back to our home folder and put these files somewhere safe.
$ cd ~ $ mkdir backup $ cd backup
Run the following for each one of your databases and you’ll end up with a backup directory full of .sql files.
$ mysqldump -u root databasename > databasename.sql
Get your new server back in fighting shape
Now at this point you can kill MySQL if you know how to look up its process ID and kill it, but sometimes MySQL can be a hard one to kill so we’re going to resort to rebooting. Let’s get or data directories back in order first:
$ cd /var/lib $ sudo mv mysql mysql_old $ sudo mv mysql_new mysql
All right. Our clean data is back in place. Let’s reboot the server.
$ sudo reboot now
Let’s restore some data!
At this point MySQL should be running with a standard set of data. So, reconnect to your server and make sure that MySQL is running and you can login. While you’re in there, create a database for each one that you’d like to restore.
$ mysql -u root -p > create database databasename;
Once you’ve confirmed that all is well and have created your databases, exit from there and move to your backup directory.
$ cd ~/backup
Hey, we’ve made it to the big moment where you get to restore some data! Run the following for each file that you need to restore.
$ mysql -u root -p databasename < databasename.sql
Your data is back on a fresh MySQL server! You did it! Great work. Everyone whose data you saved will be anxiously lined up to shake your hand (and then ask why there were no backups).
You have averted disaster. Time to grab a cocktail and celebrate! That is unless you still need to reconfigure all your applications to use the new server… drat.