Wednesday, April 30, 2008

Backup MySQL Databases

I run a Gallery2 website, locally hosted on my home PC. It serves our family's photo album, keeping our remote relatives happy.

The Gallery2 FAQ suggests three methods for backing up its MySQL database, all of which are manual due to the simple fact that you have to type in a password in order to perform database maintenance.

This is a case of regurgitated documentation - that particular Gallery2 FAQ entry is based on backup instructions taken from the Wordpress Codex, while the official methods are well documented in MySQL Backup and Recovery and mysqldump — A Database Backup Program.

To be fair, the official backup instructions do not provide a solution to the password entry problem. But after some more reading, I realized that all the MySQL client programs have a common command line options facility, that allow a password to be specified in an options file. This seemed useful, but not more secure than specifying the password on the mysqldump command line inside a backup script.

I figured out the missing piece in the puzzle after browsing the MySQL databases using phpMyAdmin. Looking at the users table in the mysql database I found a user called debian-sys-maint - a quick internet search revealed that this is the user name that's used by Debian scripts to access the database. The password for this user is defined in a MySQL options file called /etc/mysql/debian.cnf.

The following bash function can be used in a script (to be run by root) on a Debian machine, to dump MySQL databases to a given directory, that can later be backed up:

backup_mysql ()
{
echo "Backing up MySQL databases..."
rm -rf "$1"
mkdir -p "$1"
mysql --defaults-file=/etc/mysql/debian.cnf --batch --skip-column-names -e "show databases" |
while read DB ; do
echo Dumping "${DB}" ...
mysqldump --defaults-file=/etc/mysql/debian.cnf --add-drop-table "${DB}" > "$1/${DB}.sql"
done
}

You may want to avoid the while loop and simply dump all databases to a single .sql file using
mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --add-drop-table > backup.sql
but I wanted to be able to cherry pick which database to restore, if and when the need arises.

I also did not bother to compress the .sql files, because Bacula does that for me.

No comments:

Post a Comment