#!/bin/sh

#=========================================================================
# hourly & daily backup of specified mysql databases
#=========================================================================

# run from cron: 'full' once a day (3am), 'incremental' hourly (4am - 2am)

# script user needs RELOAD privilege for flush-logs

# --ignore-table = MyISAM tables (not compatible with single-transaction)
# --master-data records the binary log position in the resulting dump file
# (=2 causes this line to be a comment in the dump file)

# http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html

# to view bin-logs: mysqlbinlog /path/to/bin-log.000001 > bin_log.sql

# to restore full db(s):
# see RESTORE_DB_PROCEDURE.txt

DBUSER=raj
DBPASS=adm1n

NOW=$(date +"%Y-%m-%d")
# increased security if ARCHIVEPATH on another system:
ARCHIVEPATH=/backups/mysql

case $1 in

full)
# daily backup
cd $ARCHIVEPATH

for i in outreach hmrn hilis4 uclh genomics bristol
do
	mysqldump -u $DBUSER -p$DBPASS \
	--single-transaction \
	--quick \
	--add-locks \
	--flush-logs \
	--routines \
	--master-data=2 \
	--ignore-table=${i}.sessions \
	--ignore-table=${i}.email_addresses \
	${i} > ${i}.sql
done

# --databases $i | gzip -9 - >$ARCHIVEPATH/${NOW}_${i}.sql.gz # -9 = best compression

tar czf ${NOW}.tar.gz *.sql

# --delete-master-logs | gzip > $ARCHIVEPATH/full-$DATE.sql.gz # DANGEROUS
echo 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 2 DAY);' \
| /usr/bin/mysql -u $DBUSER -p$DBPASS
;;

incremental)
# hourly backup
mysqladmin -u $DBUSER -p$DBPASS flush-logs
;;

*)
echo "Usage: mysql_backup [full|incremental]"
;;

esac