Which one is preferred prepare at the time of incremental backup or restore stage..

MySQL & MariaDB Percona XtraBackup
1

I have a Question, We take full backup on Sunday and incremental remaining days of the week. My question is regarding prepare stage to incremental, which way is preferred way, whether to apply at the time of incremental backup is taken or wait and do at the time of restore. I explored the document not able to decide which way is better.

More about the confusing problem:
FULL BACKUP taken on SUNDAY and did --apply-log and --redo-only(doing this wright after taking the backup)
INCR BACKUP taken on MONDAY and did --apply-log and --redo-only (doing this wright after taking the backup)
INCR BACKUP taken on TUESDAY and did --apply-log and --redo-only ​(doing this wright after taking the backup)
INCR BACKUP taken on WEDNESDAY and did --apply-log and --redo-only ​(doing this wright after taking the backup)
INCR BACKUP taken on THURSDAY and did --apply-log and --redo-only ​(doing this wright after taking the backup)
INCR BACKUP taken on FRIDAY and did --apply-log and --redo-only ​(doing this wright after taking the backup)
INCR BACKUP taken on SATURDAY and did --apply-log only ​(doing this wright after taking the backup)

and the cycle continues, my question doing this way is better or

FULL BACKUP taken on SUNDAY and did --apply-log (doing this wright after taking the backup)
INCR BACKUP taken on MONDAY
INCR BACKUP taken on TUESDAY
INCR BACKUP taken on WEDNESDAY
INCR BACKUP taken on THURSDAY
INCR BACKUP taken on FRIDAY
INCR BACKUP taken on SATURDAY

Just taking the full and incremental backup on the corresponding days, --apply-log and and do prepare stage to incremental at the time of restore(i mean --apply-log and --redo-only to incremental backup). Am doing the right way or is there a technical mistake that am making while taking backup.

Am having this doubt since long please some one percona or some expert help me have the right knowledge.

Thanks
Lakshma

2

The best option is to apply the logs after every incremental backup to check if the backup is ok. This is done to check the backup integrity because sometimes the apply-log process can fail and then you should discard it and run the backup again. If you don’t prepare it after every backup, you don’t know if the backup is correct or not. Also, it depends on disk requirements too. For example,

  1. innobackupex --apply-log --redo-only full-backup
  2. innobackupex --apply-log --redo-only full-backup --incremental-dir=INCREMENTAL-DIR-1
  3. innobackupex --apply-log --redo-only full-backup --incremental-dir=INCREMENTAL-DIR-2

At this moment, full-backup will contains data up to date till 2nd incremental and after testing backup restore you can remove INCREMENTAL-DIR-1 & INCREMENTAL-DIR-2. So, this way you can save disk space too but you can’t restore to incremental 1 as your full backup is prepared till incremental 2. on the other hand, make another copy of full backup and copy it some other place and all incrementals and for consistency check you can apply logs as suggested above but it requires additional disk space. For details on preparing incremental backups check [url]Incremental Backups with innobackupex

Hope that helps.

3

Thank you. I will check and update if something goes the other way.