Fixing a Dead MySQL Server on Ubuntu

I'm doing some freelance work for a company and unfortunately the other day their DB server crapped out. On top of that, there was some issues with the backup script, so the last backup done was quite some time ago.

Fortunately though, the hard drive was still intact. Here is a little guide to restoring your MySQL DB in the case that your DB server dies and you have no backups. This is a guide for Ubuntu server 10.04, but it shouldn't be too different with other versions of Ubuntu or other Linux distributions.

There is one requirement here: the hard drive still needs to work. If the hard drive is toast, then you're in trouble.

1) We had another computer that wasn't being used that had been purchased to be set up as a backup server. It wasn't set up yet, so I just stuck Ubuntu Server on it and plugged the old hard drive in.
2) Make sure MySQL is installed: sudo apt-get install mysql-server
3) Stop the MySQL server: sudo /etc/init.d/mysql stop
4) MySQL keeps the database files in /var/lib/mysql. Copy these from the old hard drive to the new hard drive. It might be a good idea to not copy over /var/lib/mysql/mysql, however I didn't have any problems after copying that over (yet). Try it and if the database doesn't work, copy that folder too. Also, don't copy over any .pid files.
5) Set permissions: sudo chown mysql:mysql -R /var/lib/mysql/*
6) Restart the MySQL server: sudo /etc/init.d/mysql start

Now the MySQL server should be just like it was before the system died. If you didn't copy over the /var/lib/mysql/mysql folder then you'll have to recreate any users that you had. If the whole thing just doesn't work then just copy that folder too.

Hopefully this saves some people some pain! I was particularly annoyed since I forgot step 5, so the MySQL server was able to list the other databases but kept saying that there were no tables in any of them. Not good! In the end it just turned out to be a permissions issue which is no big deal.

Anyway the more important thing to take out of all this is to make sure that your backup scripts work.