in Linux

MySQL database backup with remote storage

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/ 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/

#!/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/

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

#!/usr/bin/env bash
# dump all available databases
DBS=`mysql $AUTH --skip-column-names -e 'SHOW DATABASES;'`
for DB in $DBS
	mysqldump $AUTH $DB > $BACKUPS`date +%Y%m%d%H%M`_$DB.sql
# 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/

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.

Write a Comment


  1. I’ve never had to fully restore a site from backups following a failure, thankfully! I’ve done some disaster recovery testing with some of the web services I used to work with but never my own apps, I’d be interested to hear your thoughts? A wee blog post perhaps?