#!/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"
Aug 27, 2010
MySQL backup script
Nothing extraordinary, just a backup script which runs on our production db servers each night:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment