lundi 1 décembre 2014

Are ZRM (Zmanda Recovery Manager) incremental backups reliable?


I had an issue while restoring incremental backups and I'm wondering if they are reliable. I'm making weekly full backups and daily incremental backups and I needed to restore a single database after an operator error. Database to restore is pretty small (~1GB) but it make lot of writes.


I've restored the full backup and after that all the following incremental backups taken after the full one. However I had an error while restoring first incremental backup about a duplicate entry for a unique index. I've investigated the issue and I was able to reproduce it on a local virtual machine. The issue arises because logs are flushed before taking the dump, so if there is any activity during dump time it will be recorded in the new log file. Next backup (the incremental one) will store that log file as part of its content thus if there is any incompatible query restore will fail. Even worse if during the dump there is a query similar to this



UPDATE table SET field = field + 1 WHERE...


it will be silently executed more than once.


Here's a quick way to reproduce the issue, create a test database with a table with id and unique field



CREATE DATABASE `test`;
USE test;
CREATE TABLE `serials` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uuid` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`)
);


Then open a couple of shell sessions and run the following commands:



# Fire one shell with (repeat is zsh syntax)
repeat 2000 mysql test -e "INSERT INTO serials(uuid) VALUES('`uuidgen`')"

# Fire another with
mysql-zrm-backup --backup-set localhost --backup-level 0 --verbose; sleep 5; mysql-zrm-backup --backup-set localhost --backup-level 1 --verbose


While full backup is taken new records are added to the bin log and they will cause an error when restoring the incremental backup. This happens after I've restored the full backup and I try to restore the only incremental backup:



# echo $incrementals | xargs -L 1 mysql-zrm-restore --backup-set $backup_set --source-directory
restore:INFO: ZRM for MySQL Community Edition - version 2.2.0
localhost:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
localhost:restore:INFO: BINLOG = mysqlbinlog --host="localhost" "/var/lib/mysql-zrm/localhost/20141124085026"/mysql-bin.[0-9]* >> /tmp/tLBMtpEZkD
localhost:restore:ERROR: Output of command: 'mysql --host="localhost" -e "source /tmp/tLBMtpEZkD;"' is {
ERROR 1062 (23000) at line 31 in file: '/tmp/tLBMtpEZkD': Duplicate entry '258' for key 'PRIMARY'
}
ERROR: Incremental restore failed
localhost:restore:ERROR: Incremental restore failed
localhost:restore:ERROR: Restore failed


If I extract the incremental backup and look into binlog for the last inserted record in full backup I'm able to specify the start position and to complete the restore



# echo $incrementals | xargs -L 1 mysql-zrm-restore --backup-set $backup_set --start-position 881 --source-directory
restore:INFO: ZRM for MySQL Community Edition - version 2.2.0
localhost:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
localhost:restore:INFO: BINLOG = mysqlbinlog --host="localhost" --start-position=881 "/var/lib/mysql-zrm/localhost/20141124085026"/mysql-bin.[0-9]* >> /tmp/YpoSOEFMi1
localhost:restore:INFO: Incremental restore done
localhost:restore:INFO: Restore done in 1 seconds.


Am I the only with this issue? Am I doing something wrong?


For the records here is backupset configuration:



localhost:backup:INFO: {
localhost:backup:INFO: retention-policy=10D
localhost:backup:INFO: backup-level=0
localhost:backup:INFO: mailto=root@localhost
localhost:backup:INFO: all-databases=1
localhost:backup:INFO: databases=test
localhost:backup:INFO: host=localhost
localhost:backup:INFO: database=test
localhost:backup:INFO: single-transaction=only-innodb
localhost:backup:INFO: mail-policy=only-on-error
localhost:backup:INFO: backup-mode=logical
localhost:backup:INFO: backup-type=regular
localhost:backup:INFO: compress=
localhost:backup:INFO: mysql-binlog-path=/var/log/mysql
localhost:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
localhost:backup:INFO: }




Aucun commentaire:

Enregistrer un commentaire