#!/bin/sh

# 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:
# mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
# OR, write them all to file first, then restore from that:
# mysqlbinlog binlog.000001 >  /tmp/statements.sql
# mysqlbinlog binlog.000002 >> /tmp/statements.sql
# mysql -u root -p -e "source /tmp/statements.sql"

# see http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

DBUSER=raj
DBPASS=adm1n

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

case $1 in

full)
# daily backup
mysqldump -u $DBUSER -p$DBPASS \
--single-transaction \
--quick \
--add-locks \
--flush-logs \
--master-data=2 \
--ignore-table=hilis4.sessions \
--ignore-table=hilis4.email_addresses \
--ignore-table=hilis4._lab_test_map \
--ignore-table=hmrn._patient_id \
--databases outreach hilis4 hmrn \
| gzip -9 - >$ARCHIVEPATH/full-backup_$NOW.sql.gz # -9 = best compression

# --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
