rsnapshot is a nifty little perl script for managing rsync backups in an easy way. rsync is great for backing up almost everything, but MySQL databases is an exception, since the files could get corrupted if data is not flushed and locked prior to backup.
To use mysqldump with rsnapshot the following script can be placed on the target machine (the machine being backed up). The script can of course be modified to use mysqlhotcopy instead of mysqldump, the principle is still the same.
#!/bin/bash PATH="/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin" backupdir="/tmp/mysqldump" if [ ! -d $backupdir ]; then mkdir -p $backupdir fi chmod -R 700 $backupdir case $1 in run) for db in `mysql --defaults-file=.my.cnf -e 'show databases' |tr -d '| ' |grep -v 'Database' |grep -v 'information_schema'`; do mysqldump --defaults-file=.my.cnf $db |gzip > $backupdir/$db.sql.gz done ;; clean) rm -f $backupdir/*.sql.gz ;; esac
A .my.cnf file also needs to be created on the target machine in the home folder of the backup user (probably root), with the following content. This is because we dont wont to pass the password as a command line argument (visible to other users).
[client] user=root password=<password>
Then use the following configuration for rsnapshot.
backup_script /usr/bin/ssh root@example.com "/root/backup.sh run" unused0/ backup root@example.com:/tmp/mysqldump/ example.com/ backup_script /usr/bin/ssh root@example.com "/root/backup.sh clean" unused1/
This assumes that SSH key authentication is already configured. Adjust path and user names accordingly.
