Percona Xtrabackup incremental backup based on prepared full backup

Question

I want to create an hourly backup of our MySQL database to a staging system using Percona XtraBackup.

Is it possible to prepare the $BACKUPBASE and then use this prepared backupbase as base for the following incremental backup (using the incremental-lsn option)?

My plans are:

• take a full backup once into $BACKUPBASE

• then every hour:

  • take an incremental backup on top of $BACKUPBASE

  • integrate the incremental backup into $BACKUPBASE using

    innobackupex --apply-log $BACKUPBASE --incremental-dir=$INCREMENTALDIR
    innobackupex --apply-log $BACKUPBASE
    

  • remove $INCREMENTALDIR

To be more specific:

• what about the --redo-log option. When do i have to specify this option in this scenario?
• can you apply a incremental backup to an prepared $BACKUPBASE

Answer 1

No because the above mentioned --apply-log won't update LSN in xtrabackup_checkpoints, thus every next incremental backup will copy pages modified since the last full backup. That's not what you want to achieve

UPD

To implement your scenario you need:

1. Take full backup

   innobackupex --no-timestamp /path/full
   

2. Save the last LSN

   # cat /path/full/xtrabackup_checkpoints 
   backup_type = full-backuped
   from_lsn = 0
   to_lsn = 1887987291
   last_lsn = 1887987291
   compact = 0
   
   to_lsn=`grep to_lsn /path/full/xtrabackup_checkpoints | awk '{ print $3 }'`
   

3. Apply xtrabackup REDO log

   innobackupex --apply-log --redo-only /path/full/
   

4. Take incremental backup

   innobackupex --no-timestamp --incremental /path/inc/ --incremental-lsn=$to_lsn
   

5. Save last LSN

   # cat /path/inc/xtrabackup_checkpoints 
   backup_type = incremental
   from_lsn = 1887987291
   to_lsn = 1887987291
   last_lsn = 1887987291
   compact = 0
   
   to_lsn=`grep to_lsn /path/inc/xtrabackup_checkpoints | awk '{ print $3 }'`
   

6. Apply incremental changes and REDO log

   innobackupex --apply-log --redo-only --incremental-dir=/path/inc /path/full/
   

7. Remove directory with incremental backup

   rm -r  /path/inc
   

Repeat 4-7 as many as you need. /path/full will contain the last version of your database.

When you want to restore the database

1. Finish applying logs(= create REDO log):

   innobackupex --apply-log /path/full
   

2. Copy the backup copy to datadir

   mv /path/full/* /var/lib/mysql
   

3. Fix permissions (Check that options in /path/full/backup-my.cnf are the same as in /etc/my.cnf (/etc/mysql/my.cnf for Debian))

   chown -R mysql /var/lib/mysql
   

4. Start MySQL

   /etc/init.d/mysql start