Automated Offsite MySQL backup to Strongspace

You should be doing offsite MySQL backup. Replication is nice, but a corrupted master means a corrupted replica. Thankfully, automated offsite MySQL backup to Strongspace is fairly simple. You write the contents of your database to disk using mysqldump and push the dumpfile off to Strongspace with rsync.

Basic MySQL backup

On the MySQL server, create a folder to store the backup files

$ mkdir -p ~/backup/mysql/

Dump the contents of your database to that folder and compress with gzip

mysqldump --user=USERNAME --password=PASSWORD DATABASE_NAME | gzip > "DATABASE_NAME - date +%F\ %T.sql.gz"

mysqldump has a bunch of option, but this is the most basic form and should work well in most cases. Check mysqldump --help if you want to tweak something or are having trouble.

Now push the backup off to Strongspace

rsync -avz ~/backup/mysql username@username.strongspace.com:/strongspace/username/home

Automating the backup

With the basic backup process is working, let's roll it into a small script that to run nightly with cron. To start, you will probably want to setup password-less login to avoid putting your Strongspace password in the script. Put this script in ~/backup/mysql_backup.sh, substituting your information in the configuration section.

#!/bin/sh
### Configuration
#
MYSQL_USERNAME=backup_username
MYSQL_PASSWORD=your_mysql_password
MYSQL_DATABASE=production
BACKUP_DIR="/users/home/somebody/mysql_backup"
STRONGSPACE_USERNAME=username
STRONGSPACE_PATH=/strongspace/${STRONGSPACE_USERNAME}/home/mysql_backup
#
#### Do the backup. Note: use absolute paths for mysqldump/gzip/find/rsync
/opt/local/bin/mysqldump --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} ${MYSQL_DATABASE} | /usr/bin/gzip > "${BACKUP_DIR}/${MYSQL_DATABASE} - `date +%F\ %T`.sql.gz"
# remove backups that are more than 2 weeks old
/usr/xpg4/bin/find ${BACKUP_DIR} -mtime +14 -exec rm {} \;
# Push the backups off to Strongspace
/opt/local/bin/rsync -a ${BACKUP_DIR} ${STRONGSPACE_USERNAME}@${STRONGSPACE_USERNAME}.strongspace.com:${STRONGSPACE_PATH}
#

Next, we want to make the script executable, and visible by no-one else.

$ chmod 700 ~/backup/mysql_backup.sh

Run the script and make sure everything works. If everything worked it won't output any error message and a new backup will have arrived on Strongspace

$ ~/backup/mysql_backup.sh

Schedule to run that job every night at midnight using cron. Run crontab and add a line like this

0 0 * * * /user/home/somebody/backup/mysql_backup.sh

As always, test the restore process. A backup that doesn't correctly restore isn't a backup.