PDA

View Full Version : Including a binary log with a full backup?



pv2b
December 18th, 2012, 05:20 AM
I have mysql-zrm set up to run a backup set daily and weekly - with the weekly job happening 2 AM on Mondays, and the daily job happening 3 AM every day. The weekly backup is a full backup running mysqldump, and the daily backup is an incremental backup that just copies the mysql binary log.

With this setup, the binary logs taken on 3 AM on a monday will include only the database log between 2 AM and 3 AM on Monday. What happens between 3 AM on Sunday and 2 AM on Monday is never backed up.

How can I fix this?

This is my configuration in a nutshell:



[root@backup mysql-zrm]# cat mysql-zrm-release
ZRM for MySQL Community Edition - version 2.2.0
[root@backup mysql-zrm]# crontab -l -u mysql
0 3 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set REDACTED-core --backup-level 1 --interval daily
0 2 * * 1 /usr/bin/zrm-pre-scheduler --action backup --backup-set REDACTED-core --backup-level 0 --interval weekly
0 3 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set REDACTED-test --backup-level 1 --interval daily
0 2 * * 1 /usr/bin/zrm-pre-scheduler --action backup --backup-set REDACTED-test --backup-level 0 --interval weekly
0 4 * * * /usr/bin/mysql-zrm --action purge
[root@backup mysql-zrm]# find . -name mysql-zrm.conf -exec grep -He '^[^#]' '{}' \;
./mysql-zrm.conf:backup-mode=logical
./mysql-zrm.conf:backup-type=regular
./mysql-zrm.conf:compress=1
./mysql-zrm.conf:compress-plugin=/usr/bin/xz
./mysql-zrm.conf:mailto="REDACTED@REDACTED"
./mysql-zrm.conf:mail-policy=always
./mysql-zrm.conf:html-reports=backup-method-info, backup-status-info, backup-retention-info, backup-performance-info, restore-info, selective-restore-info, replication-info | backup-app-performance-info, backup-cluster-info
./mysql-zrm.conf:html-report-directory=/var/www/html/mysql-zrm/reports/
./REDACTED-test/mysql-zrm.conf:comment=REDACTED Test
./REDACTED-test/mysql-zrm.conf:retention-policy=10W
./REDACTED-test/mysql-zrm.conf:user="dba-backup"
./REDACTED-test/mysql-zrm.conf:password="REDACTED"
./REDACTED-test/mysql-zrm.conf:host="test.REDACTED.REDACTED"
./REDACTED-test/mysql-zrm.conf:copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
./REDACTED-test/mysql-zrm.conf:ssh-user="mysql"
./REDACTED-test/mysql-zrm.conf:remote-mysql-binpath="/usr/bin"
./REDACTED-core/mysql-zrm.conf:comment=REDACTED Core
./REDACTED-core/mysql-zrm.conf:retention-policy=10W
./REDACTED-core/mysql-zrm.conf:user="dba-backup"
./REDACTED-core/mysql-zrm.conf:password="REDACTED"
./REDACTED-core/mysql-zrm.conf:host="core.REDACTED.REDACTED"
./REDACTED-core/mysql-zrm.conf:copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
./REDACTED-core/mysql-zrm.conf:ssh-user="mysql"
./REDACTED-core/mysql-zrm.conf:remote-mysql-binpath="/usr/bin"
[root@backup mysql-zrm]#

Ciro
December 18th, 2012, 05:31 AM
Hello pv2b,

It looks like the issue you are experiencing is with backup interaction: http://kb.zmanda.com/article.php?id=548

Backup interaction is when your DLEs are the same between backup sets. Amanda/ZRM will be aware of the last Daily backup and only backup the most recent changes between that and the weekly backup. In that article are methods for preventing this (including using the FQDN in the Daily backup set, and the IP address in the Weekly backup set).

Regards