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.
Hi Saloni_Gandhi.
For the record, am following the procedure for incremental backups described in: Incremental Backup - Percona XtraBackup
> 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” | sortbase/xtrabackup_info:innodb_from_lsn = 0base/xtrabackup_info:innodb_to_lsn = 2639392inc1/xtrabackup_info:innodb_from_lsn = 2639392inc1/xtrabackup_info:innodb_to_lsn = 2642569inc2/xtrabackup_info:innodb_from_lsn = 2642569inc2/xtrabackup_info:innodb_to_lsn = 2642711inc3/xtrabackup_info:innodb_from_lsn = 2642711inc3/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/basextrabackup --backup --target-dir=/data/backups/base -u root -psekretmkdir -p /data/backups/inc1xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base -u root -psekretmkdir -p /data/backups/inc2xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 -u root -psekretmkdir -p /data/backups/inc3xtrabackup --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/basextrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2and 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 Backups and Disaster Recovery - Percona Database Performance Blog ). You can have the backups be prepared before hand, or you can wait and apply the incrementals when needed.