Aug 27, 2010

MySQL backup script

Nothing extraordinary, just a backup script which runs on our production db servers each night:

#!/bin/bash
SUFIX=`date +%Y%m%d`
DIR_BASE=/tmp/`hostname`_db_
DIR_WRK=$DIR_BASE$SUFIX
DB_USER=backup
DB_PASSWD=abcde
STORAGE=backup@int.storage.server:/path/to/backup/location

# -- init working directory
echo "`date` - Preparing backup directory"
rm -rf $DIR_BASE*
mkdir -p $DIR_WRK
echo "`date` - Changing into backup directory '$DIR_WRK'"
cd $DIR_WRK

# -- backup all databases
for db in `mysql -u $DB_USER -p$DB_PASSWD -e 'SHOW DATABASES;'`
do
case $db in
Database|information_schema) continue;;
*)
echo "`date` - Backing up '$db' to '$db.sql'"
mysqldump -u $DB_USER -p$DB_PASSWD --opt $db > $db.sql
echo "`date` - Compressing '$db.sql' to '$db.tgz'"
tar -czf $db.tgz $db.sql
echo "`date` - Deleting '$db.sql'"
rm $db.sql
;;
esac
done

# -- transfer to a safe place
echo "`date` - Transfering backup directory to a remote location"
rsync -re ssh $DIR_WRK $STORAGE

echo "`date` - Cleaning up backup directory"
rm -rf $DIR_WRK
echo "`date` - Done"

No comments: