Home

Backup your LAMP DB externally

Submitted by dgtlmoon on Tue, 2009-11-17 17:37.

Simple script you could drop into /etc/cron.daily/backup_db , handy for testing the exit level of mysqldump and rsync, and then rsyncing to your external server, keeps a rolling month's worth of backups.

error checked from mysqldump by testing for info in the STDERR output, and rsync tested by examining the return code ($?)


#!/bin/bash

# keeps a rolling 30 days of DB snapshots

mysqldump -uroot -pxxx mydrupal 2> /tmp/mysql-dump-fail.log |grep -v "INSERT INTO .cache"| bzip2 > /root/db_drupal-`date +%d`.sql.bz2
if [ -s /tmp/mysql-dump-fail.log ]
then
echo "mysqldump failed `cat /tmp/mysql-dump-fail.log`"|mail -s "mysqldump failed rsync failed" -a "From: cron@somewhere.com" me@gmail.com
fi

date >> /var/log/db-backup.log
rsync -avz /root/db* mycheap@backup.dreamhost.com: 2>/tmp/rsync.log >> /var/log/db-backup.log
if [ $? -ne 0 ]
then
echo "rsync failed `cat /tmp/rsync.log`"|mail -s "rsync failed" -a "From: cron@somewhere.com" me@gmail.com
fi