5.3 Example: Applying an Incremental Backup to a Full Backup

This example continues from Section 4.3, “Example: Making an Incremental Backup of InnoDB Tables”.

After you take an incremental backup, the changes reflected in those backup files must be applied to a full backup to bring the full backup up-to-date, in the same way that you apply changes from the binary log.

To bring the data files from the full backup up to date, first we run the apply log step so that the data files include any changes that occurred while the full backup was running:

$ ibbackup --apply-log /home/pekka/.full-backup-my.cnf
ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Run 'ibbackup --help' for help and 'ibbackup --version' for version info.

Note: Uses posix_fadvise() for performance optimization.

Contents of /home/pekka/.full-backup-my.cnf:
innodb_data_home_dir got value /full-backup
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /full-backup
innodb_log_group_home_dir got value /full-backup
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

101208 17:13:47  ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 2638547968, end lsn 2638548215,
ibbackup: start checkpoint 2638548215.
InnoDB: Doing recovery: scanned up to log sequence number 2638548215
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 2638548215.
ibbackup: Last MySQL binlog file position 0 538, file name ./MySQL-bin.000046
ibbackup: The first data file is '/full-backup/ibdata1'
ibbackup: and the new created log files are at '/full-backup/'
ibbackup: System tablespace file format is Barracuda.
101208 17:13:48  ibbackup: Full backup prepared for recovery successfully!

Then we apply the changes from the incremental backup, to the data files produced by the full backup:

$ ibbackup --apply-log --incremental /home/pekka/.incr-backup-my.cnf /home/pekka/.full-backup-my.cnf
ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Run 'ibbackup --help' for help and 'ibbackup --version' for version info.

Note: Uses posix_fadvise() for performance optimization.

Contents of /home/pekka/.incr-backup-my.cnf:
innodb_data_home_dir got value /incr-backup
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /incr-backup
innodb_log_group_home_dir got value /incr-backup
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

Contents of /home/pekka/.full-backup-my.cnf:
innodb_data_home_dir got value /full-backup
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /full-backup
innodb_log_group_home_dir got value /full-backup
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

101208 17:13:49  ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 2654252032, end lsn 2654252454,
ibbackup: start checkpoint 2654252454.
InnoDB: Doing recovery: scanned up to log sequence number 2654252454
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 2654252454.
ibbackup: Last MySQL binlog file position 0 538, file name ./MySQL-bin.000046
ibbackup: The first data file is '/full-backup/ibdata1'
ibbackup: and the new created log files are at '/full-backup/'
ibbackup: System tablespace file format is Barracuda.
101208 17:13:51  ibbackup: Incremental backup applied successfully!

Now the data files that are labelled as the full backup are fully up-to-date, as of the time of the incremental backup:

$ ls -hlR /full-backup/
/full-backup/:
total 135M
-rw-r--r-- 1 pekka pekka  155 2010-12-08 17:11 ibbackup_export_variables.txt
-rw-r----- 1 pekka pekka 1.0K 2010-12-08 17:13 ibbackup_logfile
-rw-r----- 1 pekka pekka  10M 2010-12-08 17:13 ibdata1
-rw-r----- 1 pekka pekka  20M 2010-12-08 17:13 ibdata2
-rw-r----- 1 pekka pekka  74M 2010-12-08 17:11 ibdata3
-rw-r----- 1 pekka pekka  10M 2010-12-08 17:13 ib_logfile0
-rw-r----- 1 pekka pekka  10M 2010-12-08 17:13 ib_logfile1
-rw-r----- 1 pekka pekka  10M 2010-12-08 17:13 ib_logfile2
drwxr-x--- 2 pekka pekka   48 2010-12-08 17:11 test

/full-backup/test:
total 73M
-rw-r----- 1 pekka pekka  23M 2010-12-08 17:11 alex1.ibd
-rw-r----- 1 pekka pekka 240K 2010-12-08 17:11 alex2.ibd
-rw-r----- 1 pekka pekka 240K 2010-12-08 17:11 alex3.ibd
-rw-r----- 1 pekka pekka  23M 2010-12-08 17:13 blobt3.ibd
-rw-r----- 1 pekka pekka  25M 2010-12-08 17:11 ibstest0.ibd
-rw-r----- 1 pekka pekka 912K 2010-12-08 17:11 ibtest09.ibd