Results 1 to 2 of 2

Thread: Are incremental backups reliable?

  1. #1
    Join Date
    Nov 2014
    Posts
    1

    Exclamation Are 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. 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 worst if during the dump there is a query similar to this
    Code:
    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

    Code:
    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:

    Code:
    # Fire one shell with 
    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:

    Code:
    # 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, look into binlog for the last inserted record I'm able to specify the start position and to complete the restore

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

    Code:
    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: }

  2. #2
    Join Date
    Apr 2015
    Posts
    1

    Default

    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.

    _______________
    raza

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •