Restoring an incremental backup

MySQL & MariaDB Percona XtraBackup
1

Hello all,

As I am new to Xtrabackup I am facing some difficulties on how to restore an incremental backup. In more details:

Step 1. Take a fll backup

Step 2. Create an incremental backup
[COLOR=#B22222] #[COLOR=#B22222]innobackupex --incremental --user= --password= /home/mysql_test/MYSQL_BACKUPS/ --incremental- basedir=/home/mysql_test/MYSQL_BACKUPS/2015-05-14_15-55-09_FULL_BACKUP

where[/B]2015-05-14_15-55-09_FULL_BACKUP → latest full backup
[B]
The above will create another dir under [B][/B]/home/mysql_test/MYSQL_BACKUPS/ with execution date e.g 2015-05-15_15-55-09_1st_INCREMENTAL_BACKUP

Step 3. Create another incremental backup:
[COLOR=#B22222] #[B][COLOR=#B22222]innobackupex --incremental --user= --password= /home/mysql_test/MYSQL_BACKUPS/ --incremental- basedir=/home/mysql_test/MYSQL_BACKUPS/2015-05-15_15-55-09_1st_INCREMENTAL_BACKUP

[/B]and this will create the next incremental backup: e.g 2015-05-16_15-55-09_2nd_INCREMENTAL_BACKUP

So up to now we have one full and 2 incremental backups under our backup directory:

/home/mysql_test/MYSQL_BACKUPS/
[B][I]- 2015-05-14_15-55-09_FULL_BACKUP

  • 2015-05-15_15-55-09_1st_INCREMENTAL_BACKUP[/I][/B]
    -2015-05-16_15-55-09_2nd_INCREMENTAL_BACKUP

Here is where I face my dificulties and need your help, how to restore one of my 2 incremental backups…

Based on [B][U][url]https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/incremental_backups_innobackupex.html[/url][/U][/B] before restoring an incremental backup we need to prepare it where I do:

Step 4. Prepare the incremental backup:
[COLOR=#B22222] [COLOR=#B22222]#[COLOR=#B22222]innobackupex --apply-log --redo-only /home/mysql_test/MYSQL_BACKUPS/[COLOR=#FF0000]<???????>

Question 1. Which incremental I should use for this command, should I use this command for all the incrementals created above (1st… and 2nd…)

Question 2. After preparing my incremental backups (if this is what I have to do on Step 4) how do I restore one incremental backup e.g the latest one 2015-05-16_15-55-09_2nd_INCREMENTAL_BACKUP

Thank you all and looking forward for your suggestions
George

2

Hi George;

Percona has a good guide for this which should get you on the right track:
[url]Percona XtraBackup

Basically you do --apply-log on the base with --redo-only, then do the same on the 1st incremental, then do the same on the second incremental except on the last one you can leave off the --redo-only part.

After the prepare step, the full backup in your base directory will have all the incrementals applied to it, so you would restore that (the full backup from the base directory).

-Scott

3

So, let me try to answer you here.

I would assume you now have the following directories
Full: /home/mysql_test/MYSQL_BACKUPS/2015-05-14_15-55-09_FULL_BACKUP

Incremental Dir 1: 2015-05-15_15-55-09_1st_INCREMENTAL_BACKUP
Incremental Dir 2: 2015-05-16_15-55-09_2nd_INCREMENTAL_BACKUP

To restore Incremental Dir 1 backup, you need to prepare the full + incremental_dir_1 with the following:

innobackupex --apply-log --redo-only /path/to/fullbackup

then

innobackupex --apply-log /path/to/fullbackup --incremental-dir=/path/to/incremental-dir-1

At this moment, /path/to/fullbackup contains the data up to the moment of the first incremental backup. Note that the full data will always be in the directory of the base backup, as we are appending the increments to it.

4

Thank you for your replies. Problem solved usng the below link which describes clearly all steps.
[url]Xtrabackup in a nutshell | FromDual