Last week I learned a valuable lesson about backing up MySQL, it was this:

• It is not enough to copy the files out of /var/lib/mysql or wherever if you want to backup your databases.

The thing with copying files is that if the mysql daemon is still running, there’s no guarantee that the physical files are up to date or even consistent. MySQL can do its own housekeeping whenever it would like to which means file write operations. It also keeps information in memory and will write it to disk at a later time to suit itself. The recommended backup method for MySQL is to use the mysqldump tool – more on that later.

The Scenario

Having had a server failure and discovered that file backups taken while the mysql server was running was all we had, I was unsure if we were going to get everything back. I did manage to recover the databases, by doing the following:

1. Find a server with a similar version of mysql. Happily my “lost” server was 5.0.21 and our development box runs 5.0.24 so I wasn’t too worried about changes in file formats.
2. Stop the mysqld on that box
3. Copy the files into /var/lib/mysql (this is on ubuntu but I think its a pretty similar setup on other distros). I didn’t have any data on this machine to start with which made things simpler. MyISAM stores its data in directories called the name of the database.
4. Check the file permissions – the files need to be owned by the user that mysql runs as. This caught me out the first time around.
5. Bring up mysqld and hold your breath.

In the event we seem to have most things and to be honest that’s more than I dared hope. There are some corrupt tables but nobody can swear that they weren’t like that before so with a bit of luck we won’t make any nasty discoveries further down the line [1].

MySQL Backup Strategy

I’ve been doing some setup on the new servers and backing up what was salvaged from the old one. I’ve devised a simple loop which dumps and zips each database in turn, using a shell script. The code for the script is below:

#!/bin/sh
for i in `mysql -B -u root -e “show databases” | tail -n +2`; do
echo $i
mysqldump -u root $i | gzip -9 > mysql/$i.`date —iso-8601`.gz
done

Its nothing that isn’t used by sysadmins everywhere but its simple and it works for me so its here (for when I forget what I did!). This example writes a .gz file for each database into a folder called mysql in the same location as where you call the script from. You probably also need to add -p switches to both the mysql and the mysqldump calls unless you are running with no root password which is rarely a good idea.

I hope this is all helpful to someone; I know I learned some useful lessons this week!

¹ I’d also like to point out that this was an internal server with little critical information on it and a backup routine that’s been running so long nobody can remember setting it up. Which isn’t great but its less bad that it might be.