Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: error restoring first incremental backup after a full

  1. #1
    Join Date
    Sep 2008
    Posts
    21

    Default error restoring first incremental backup after a full

    Hi,

    I've got two ZRM backup jobs scheduled:

    Code:
    14 12 * * * mysql-zrm-scheduler --now --backup-set app01-full
    34 * * * * mysql-zrm-scheduler --now --backup-set app01-txn-incr
    So, that's a full backup at 12:14 everyday, and an incremental backup at 34 mins past the hour, also every day.

    I am testing the restore process on a different machine (same MySQL version).

    First I restore the full backup:

    Code:
    mysql-zrm-restore --source-directory /var/lib/mysql-zrm/app01-full/20080917121401/
    Then I restore the first incremental backup since the full backup:

    Code:
    mysql-zrm-restore --source-directory /var/lib/mysql-zrm/app01-txn-incr/20080917123401/
    However, this fails with the following output:

    Code:
    restore:INFO: ZRM for MySQL Community Edition - version 2.0
    BackupSet1:restore:WARNING: Binary logging is off.
    BackupSet1:restore:ERROR: Output of command: 'mysql -e "source /tmp/hz90tKx0lD;"' is {
    ERROR 1062 (23000) at line 14 in file: '/tmp/hz90tKx0lD': Duplicate entry 'bdf7c536-d5fc-102b-9fc1-001d09667b50-1300' for key 1
    }
    ERROR: Incremental restore failed
    BackupSet1:restore:ERROR: Incremental restore failed
    BackupSet1:restore:ERROR: could not delete /var/lib/mysql-zrm/app01-txn-incr/20080917123401
    BackupSet1:restore:ERROR: Restore failed
    Restores of subsequent incremental restores work just fine - it's only the one immediately following the full backup that is a problem.

    Am I doing something wrong here? Could it be that the full backup contains transactions that are also in the incremental backup?

    Any suggestions?

    R.

  2. #2

    Default

    It is not best practice to have different different backup-sets for Full and incremental backup of same database. In this case restore becomes difficult.

    The recommended is, have only one backup set for a same database and through CRON, manage the backup levels

    So in this case, cron entry may be

    14 12 * * * mysql-zrm-scheduler --now --backup-set app01-full --backup-level 0
    34 * * * * mysql-zrm-scheduler --now --backup-set app01-full --backup-level 1


    This will help in restore.

  3. #3

    Default

    Full backup will contain all the transaction till that time. Hence user can safely delete previously taken backups if needed. Restoring such backup will restore the database, till the time on which user has taken a backup.

  4. #4
    Join Date
    Sep 2008
    Posts
    21

    Default

    Quote Originally Posted by kulkarni_mangesh View Post
    It is not best practice to have different different backup-sets for Full and incremental backup of same database. In this case restore becomes difficult.
    Hi,

    Yes, I wondered if this might be a problem, so I modified my backup sets and now have just one and run it exactly as you suggest:

    Code:
    14 12 * * * mysql-zrm-scheduler --now --backup-level 0 --backup-set app01
    34 * * * * mysql-zrm-scheduler --now --backup-level 1 --backup-set app01
    I still see exactly the same problem.

    R.

  5. #5

    Default

    Is it possible to test it with another database with new backup-set..?

    You can perform following steps with test database.:

    1. Create test database.
    2. Take one full of test backup.
    3. Add some rows in to one of the table of test database.
    4. Take incremental of test bakup.
    5. Drop test database.
    6. Restore full backup, and see the status of test database.
    7. Restore incremental backup and see the data of the table.

  6. #6

    Default

    Oops missed following entry from your log.

    restore:INFO: ZRM for MySQL Community Edition - version 2.0
    BackupSet1:restore:WARNING: Binary logging is off.


    Binary logging should be on for incremental backup.

    Enable binary logging and perform the steps.

  7. #7
    Join Date
    Sep 2008
    Posts
    21

    Default

    Quote Originally Posted by kulkarni_mangesh View Post
    Full backup will contain all the transaction till that time. Hence user can safely delete previously taken backups if needed. Restoring such backup will restore the database, till the time on which user has taken a backup.
    Yes, I understand that.

    Are you saying that the first incremental following a full will contain a transaction log holding transactions from *before* the time of the full backup?

    Ugh, I guess that makes sense. And yes, I have verified that each incremental backup immediately after a full backup contains *two* transaction logs; one containing the transactions since the last incremental but *before* the full, and another containing the transactions *since* the full backup.

    So, to do a restore, it is necessary to skip that first file. Ugh, that's going to take some manual frigging around.

    Unless there's someway for zrm to do this automatically?

    R.

  8. #8
    Join Date
    Sep 2008
    Posts
    21

    Default

    Quote Originally Posted by kulkarni_mangesh View Post
    Oops missed following entry from your log.

    restore:INFO: ZRM for MySQL Community Edition - version 2.0
    BackupSet1:restore:WARNING: Binary logging is off.


    Binary logging should be on for incremental backup.

    Enable binary logging and perform the steps.
    That msg is from the scratch machine I'm using to test the restores - binary logging is enabled on the production machine.

    R.

  9. #9
    Join Date
    Sep 2008
    Posts
    21

    Default

    Quote Originally Posted by kulkarni_mangesh View Post
    Is it possible to test it with another database with new backup-set..?
    Well, it would be possible, but I think I've bottomed out the issue (see my earlier post).

    Now, I just need to find a way to deal with it!

    R.

  10. #10
    Join Date
    Sep 2008
    Posts
    21

    Default

    So, I've been thinking about this...

    To recap, when a full backup is performed the transaction log is rolled over. if you have, for example, a full backup daily at 12:00 and an incremental backup hourly on the half hour, then the backups contain the following content:

    11:30
    - transaction log 10:30 - 11:30
    12:00
    - full backup
    12:30
    - transaction log 11:30 - 12:00
    - transaction log 12:00 - 12:30
    etc.

    When doing a restore, you would typically restore from a full backup, then restore all the incrementals since the last full backup. However, this fails because the incremental backup immediately following the full backup contains transactions that occurred *before* the full backup.

    I'm wondering if it would actually make sense for the transaction log immediately preceding the full backup to be stored with the full backup image, eg:

    11:30
    - transaction log 10:30 - 11:30
    12:00
    - full backup
    - transaction log 11:30 - 12:00
    12:30
    - transaction log 12:00 - 12:30
    etc.

    Would that be an easy change to make?

    R.

Posting Permissions

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