Prevent a disaster
After reading Jeff Atwood’s backup failure last month I decided to finally get around to doing something I’d been intending to do “one of these days” but had in actual fact been putting off for years.
Here’s the steps I took to ensure the databases on my webserver were backed up every night and copies of the dumps stored remotely.
On the remote storage machine
Generate an ssh key pair with and empty password and put the public key on the remote server. This will give our script access to the server without requiring you to enter a password each time:
$ ssh-keygen -t rsa -f /home/steve/code/db_backup/id_rsa
$ scp /home/steve/code/db_backup/id_rsa.pub REMOTEHOST:
This script will fetch all the backups, logging in as the rsync user and using the private key just generated. It’s located at /home/steve/code/db_backup/sync_backups.sh:
#!/usr/bin/env bash
rsync -e "ssh -l rsync -i /home/steve/code/db_backup/id_rsa" -avz REMOTEHOST:mysql/ /data/primary/backup/mysql/
Have this happen automatically daily at 12:20am:
$ crontab -l
# m h dom mon dow command
20 0 * * * /home/steve/code/db_backup/sync_backups.sh
$
On the machine to be backed up
Create a new user and allow ssh access with the previously generated key:
# adduser rsync
# mkdir ~rsync/.ssh
# mv ~steve/id_rsa.pub ~rsync/.ssh/authorized_keys
# chown rsync:rsync ~rsync/.ssh/authorized_keys
# chmod 400 ~rsync/.ssh/authorized_keys
This script will dump all available databases and is located at /root/bin/backup_databases.sh:
#!/usr/bin/env bash
# dump all available databases
# SJW
AUTH='-uroot -pROOTPASSWORD'
DBS=`mysql $AUTH --skip-column-names -e 'SHOW DATABASES;'`
BACKUPS='/home/rsync/mysql/'
for DB in $DBS
do
mysqldump $AUTH $DB > $BACKUPS`date +%Y%m%d%H%M`_$DB.sql
done
# delete backups older than 5 days
find $BACKUPS -mtime +5 -type f | awk '{print "rm "$1}' | sh
Have the script run nightly at 12:10am via cron:
# crontab -l
# m h dom mon dow command
10 0 * * * /root/bin/backup_databases.sh
#
Closing thoughts
This approach is realtively straight forward, everything happens automatically and it could easily be extended to cover mailboxes, source code repositories, uploaded content etc. However, for mission-critical databases master-slave replication may be more appropriate. For further reading you may enjoy JWZ’s thoughts on backups.
