Not the answer you need?
Register and ask your own question!

Restoring Base and incremental backups

saloni_gandhisaloni_gandhi Current User Role Beginner
I have to take 1 full backup a week and for the remaining days incremental backups. However I dont understand when i can compress my base back to which i am applying the incrementals. Is it advisable to apply all incrementals together on the restore host, or apply them to the base as soon as you take an incremental backup. Also, if i want to apply all the incrementals at once to the base how do I go about doing it, how do we know which incremental backup was taken first.

Answers

  • CTutteCTutte Percona Staff Role
    Hi Saloni_Gandhi.
    For the record, am following the procedure for incremental backups described in: https://www.percona.com/doc/percona-xtrabackup/2.4/backup_scenarios/incremental_backup.html

    > how do we know which incremental backup was taken first.

    As a good practice, when taking the backup you can name the folders with the date/time of the backup. Otherwise you can check file "xtrabackup_info" inside the backup folder to identify the "lsn" (log sequence number) from when backup was taken:
    # grep -ri "innodb_from_lsn\|innodb_to_lsn" | sort
    base/xtrabackup_info:innodb_from_lsn = 0
    base/xtrabackup_info:innodb_to_lsn = 2639392
    inc1/xtrabackup_info:innodb_from_lsn = 2639392
    inc1/xtrabackup_info:innodb_to_lsn = 2642569
    inc2/xtrabackup_info:innodb_from_lsn = 2642569
    inc2/xtrabackup_info:innodb_to_lsn = 2642711
    inc3/xtrabackup_info:innodb_from_lsn = 2642711
    inc3/xtrabackup_info:innodb_to_lsn = 2647535

    In the above example I have named the directories in order (inc 1 , 2 and 3), but if naming were different, I could still match the "lsn" numbers to identify which one was created from which other.


    > I have to take 1 full backup a week and for the remaining days incremental backups. However I dont understand when i can compress my base back to which i am applying the incrementals. 

    When you take an incremental backup, you need to to target the previous backup from where you intend to take an incremental, i.e:
    mkdir -p /data/backups/base
    xtrabackup --backup --target-dir=/data/backups/base -u root -psekret
    mkdir -p /data/backups/inc1
    xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base -u root -psekret
    mkdir -p /data/backups/inc2
    xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 -u root -psekret
    mkdir -p /data/backups/inc3
    xtrabackup --backup --target-dir=/data/backups/inc3 --incremental-basedir=/data/backups/inc2 -u root -psekret

    In the above example, "inc1" depends on "base" and "inc2" depends on "inc1". For taking "inc2" you can compress "base" backup but not "inc1" and so on.
    For taking the incremental backup, xtrabackup does not need to check entire backup folder, but just some xtrabackup coords. Potentially you could compress dependancy folders and leave a few files for the incremental to check, but I would suggest to not manipulate files/folders manually since you can end up with a corrupted/incomplete backup.


    > Also, if i want to apply all the incrementals at once to the base how do I go about doing it

    You apply one after the other (in order) , checking that each of them is applied correctly.
    Command for applying incrementals is:
    xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
    xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1
    xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2
    and so on.

    If you try to apply the wrong incremental folder, the following message will show:
    xtrabackup: error: This incremental backup seems not to be proper for the target.
    xtrabackup:  Check 'to_lsn' of the target and 'from_lsn' of the incremental.
     
    Citing from the doc:
    xtrabackup --apply-log-only should be used when merging all incrementals except the last one. That’s why the previous line doesn’t contain the xtrabackup --apply-log-only option. Even if the xtrabackup --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.


    flag --apply-logs-only will not go through the rollback phase for uncommited transactions in the redo log. If you do not use the flag, rollback will occur and you won't be able to apply following incrementals in the chain.
    If you restored last backup using --apply-logs-only (and did not go through rollback phase), crash recovery will be done by MySQL on startup, so you can choose to always prepar with "apply-logs-only" flag.


    > Is it advisable to apply all incrementals together on the restore host, or apply them to the base as soon as you take an incremental backup. 

    It's a good practice to store the backups in a different host other than the one you took the backup, because in case of disk corruption, you might lose both the DB and the backup.
    Preparing the backup will generate some load on the server. Taking that into consideration you can choose to prepare on a different server, or on the target to be restored.
    Applying incrementals to base backup straight away or not will depend on RTO (recovery time objective https://www.percona.com/blog/2017/07/18/backups-and-disaster-recovery/ ). You can have the backups be prepared before hand, or you can wait and apply the incrementals when needed.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.