Backup MySQL Databases

Automated Offsite MySQL backup to rSyncIT

You should be doing offsite MySQL backup. Replication is nice, but a corrupted master means a corrupted replica. Thankfully, automated offsite MySQL backup to rSyncIT is fairly simple. You write the contents of your database to disk using mysqldump and push the dumpfile off to rSyncIT 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 rSyncIT

rsync -avz ~/backup/mysql username@username.backupuser.com:/backupuser/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 SSH Keys to avoid putting your rSyncIT 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="/home/somebody/mysql_backup"
STRONGSPACE_USERNAME=username
STRONGSPACE_PATH=/home/${STRONGSPACE_USERNAME}/mysql_backup
#
#### Do the backup. Note: use absolute paths for mysqldump/gzip/find/rsync
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
find ${BACKUP_DIR} -mtime +14 -exec rm {} \;
# Push the backups off to rSyncIT
rsync -a ${BACKUP_DIR} ${STRONGSPACE_USERNAME}@${STRONGSPACE_USERNAME}.backupuser.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 rSyncIT

$ ~/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.

Also Read