Capturing MySQL Backups with BackupPC

BackupPC with its default configuration will catch the MySQL databases, but I don’t consider that kind of backup to be particularly reliable.  Much safer is to do a mysqldump of any relevant databases and then capture that with BackupPC.

Firstly, you’ll need to create a special user on your MySQL install who will be used to do the backups.  Do this in mysql as root.  We’ll assume the database you want to back up is your_database, and the backup user will be backup.

GRANT USAGE ON *.* TO backup@localhost IDENTIFIED BY 'passwordgoeshere';
GRANT SELECT,LOCK TABLES ON your_database.* to backup@localhost;
FLUSH PRIVILEGES;

At the end of that, you’ll have a new user.  Now, in the home directory of the account which will run mysqldump (we’ll assume this is backup), create a file .my.cnf by running the following commands;

sudo -u backup bash
cd
cat > ~/.my.cnf << EOF
[mysqldump]
user = backup
password = passwordgoeshere
EOF
chmod og= .my.cnf

This will let your backup user run mysqldump.  Now, we’ll test it;

sudo -u backup bash
cd
mysqldump -u backup -h localhost your_database | gzip > test.sql.gz

You should wind out with a test.sql.gz which will be a dump of all the commands necessary to rebuild that database.  Now we create a new script as backup to manage our backups;

sudo -u backup bash
cd
mkdir your_database
[Create the mysql_backup script below]
chmod u+x ~/mysql_backup

The script you want to create is;

#!/bin/bash
DBNAME=your_database
HOME=/home/backup
FILENAME=$HOME/$DBNAME/`date "+%Y%m%d.%H%M%S"`.sql.gz
mysqldump -u backup -h localhost $DBNAME | gzip > $FILENAME
chmod og= $FILENAME
find $HOME/$DBNAME -name "*.sql.gz" | sort -r | tail -n +8 | xargs rm -f

That will make a backup of your database and then throw it into the folder we created.  It will keep the 7 latest backups, and delete any older ones.  You can then back that up with BackupPC and get a consistent backup.

The last step is to add it to your crontab;

crontab -u backup -e
0 0 * * * /home/backup/mysql_backup
[Ctrl-D]

And that will make the backup run at midnight each night.  Enjoy!