Backup all MySQL databases and other important stuff on FreeBSD

This is the (bash) script that i use for backup all MySQL databases and other important stuffs like etc/ dir, configuration files in /usr/local/etc and whatever you want.
As you can see, it’s launched 3 times a week by cron, on sunday, wednesday and friday at 23:55. It create a list of installed packages, dump all MySQL databases, sync important files and dirs with rsync and finally create a tar.gz of backupped files.
It would be interesting add one cool thing, after tar.gz is done it could be copied via scp to another host (and it would be a better backup :P).

Actually i haven’t a pc running 24/7 (old good OpenBSD gateway, i miss you!!) and i download manually the tar.gz the morning after (shame on me) :P
NB: MySQL backup part is partially taken from a GREAT MySQL backup script and adapted to my needs. Script is from bash.cybercity.biz
http://bash.cyberciti.biz/mysql/freebsd-nas-backup-mysql-shell-script/

The variable BACKUP_DIR is where the script will create all the files. It must be launched with a variable to specify the day of the week, it will create the correct dir to keep old backup of other days safe.

do_backup.sh

#!/bin/sh

case “$1” in
“-dom” )
DAY=dom
;;
“-mer” )
DAY=mer
;;
“-ven” )
DAY=ven
;;
* )
echo “Use -dom or -mer or -ven.”
exit
;;
esac

BACKUP_DIR=/tmp/backups
CWD=${BACKUP_DIR}/backup_$DAY
if [ ! -d “$CWD” ]; then
mkdir -p $CWD
fi
cd $CWD

#CREATE A LIST OF INSTALLED PACKAGES
/usr/sbin/pkg_info > packages.txt

#
# MYSQL DATABASES BACKUP
# this part is a slighty modified mysql section of nixCraft shell script
# collection (NSSC).
# Visit http://bash.cyberciti.biz/ for more information.
mysql_backup () {

MYSQL=/usr/local/bin/mysql
MYSQLDUMP=/usr/local/bin/mysqldump
GZIP=/usr/bin/gzip
MUSER=root
MPASS=’your_root_password
MHOST=”127.0.0.1″

DBS=”$($MYSQL -u $MUSER -h localhost -p$MPASS -Bse ‘show databases’)”
db=””;
for db in $DBS
do
FILE=”${CWD}/mysqlbackup/${db}.gz”
$MYSQLDUMP –single-transaction -u $MUSER -h localhost -p”$MPASS” $db | $GZIP -9 > $FILE
done
}

#
# IMPORTANT FILES BACKUP VIA RSYNC
#
rsync_backup () {

for file in /etc \
/usr/local/etc \
/data/stuffs/some_file \
/data/another_file
do
/usr/local/bin/rsync -aqPR –delete $file ${BACKUP_DIR}/backup_$DAY/rootdir/
done
}

#
# CREATE TGZ
#
create_tgz () {

/usr/bin/tar cfz backup_$DAY.tar.gz mysqlbackup/ \
packages.txt ../do_backup.sh rootdir/

mv backup_$DAY.tar.gz ${BACKUP_DIR}
}

mysql_backup || { echo “MYSQL ERROR”; exit; }
rsync_backup || { echo “RSYNC ERROR”; exit; }
create_tgz || { echo “TGZ ERROR”; exit; }

crontab entry

55  23  *  *  0  sh /path/to/script/do_backup.sh -dom
55  23  *  *  3  sh /path/to/script/do_backup.sh -mer
55  23  *  *  5  sh /path/tp/script/do_backup.sh -ven

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *