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