2

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: }
  • Is your engine InnoDB, MyISAM or something else? – ForguesR Dec 5 '14 at 14:18
  • @ForguesR This happens with boty MyISAM and InnoDB. With InnoDB I found a potential solution with use of --single-transaction and --flush-logs but it involves extracting incremental backup and skip the first binlog file. – Fabio Dec 5 '14 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.