4.3 Example: Making an Incremental Backup of InnoDB Tables

An incremental backup only backs up data that changed since the previous backup. This technique provides additional flexibility in designing a backup strategy and reduces required storage for backups. Because an incremental backup always adds to an existing set of backup files, make a full uncompressed or compressed backup before doing any incremental backups.

Incremental backups detect changes at the level of pages in the data file, as opposed to table rows; each page that has changed is backed up. Thus, the space and time savings are not exactly proportional to the percentage of changed rows or columns.

Incremental backup is enabled through the --incremental option of the ibbackup command. You must also indicate the point in time of the previous full or incremental backup, through the --lsn option, where you specify the highest log sequence number from a previous full or incremental backup.

When running the apply log step for an incremental backup, you specify the option sequence --apply-log --incremental, and the paths to 2 MySQL configuration files, first the .cnf file pointing to the full backup that you are updating, then the .cnf file pointing to the incremental backup data files. If you have taken several incremental backups since the last full backup, you might run several such apply log steps, one after the other, to bring the full backup entirely up to date.

If you use the mysqlbackup command to back up tables from MyISAM and other storage engines, you can perform an incremental backup with that command also, using slightly different command-line syntax.

This example shows an incremental backup. The last full backup we ran reported that the highest LSN was 2638548215:

ibbackup: Was able to parse the log up to lsn 2638548215

We specify that number again in the command here; the incremental backup includes all changes that came after the specified LSN.

$ ibbackup --incremental 2638548215 /home/pekka/.my.cnf /home/pekka/.incr-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/.my.cnf:
innodb_data_home_dir got value /sqldata/mts
innodb_data_file_path got value ibdata1:10M;ibdata2:20M;ibdata3:50M:autoextend
datadir got value /sqldata/mts
innodb_log_group_home_dir got value /sqldata/mts
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760

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

ibbackup: System tablespace file format is Barracuda.
ibbackup: Found checkpoint at lsn 2654252454.
ibbackup: Starting log scan from lsn 2654252032.
101208 17:12:16  ibbackup: Copying log...
101208 17:12:16  ibbackup: Log copied, lsn 2654252454.
ibbackup: We wait 1 second before starting copying the data files...
101208 17:12:17  ibbackup: Copying /sqldata/mts/ibdata1 (Barracuda file format).
101208 17:12:17  ibbackup: Copying /sqldata/mts/ibdata2 (Barracuda file format).
101208 17:12:18  ibbackup: Copying /sqldata/mts/ibdata3 (Barracuda file format).
101208 17:12:21  ibbackup: Copying /sqldata/mts/test/alex1.ibd (Antelope file format).
101208 17:12:22  ibbackup: Copying /sqldata/mts/test/alex2.ibd (Antelope file format).
101208 17:12:22  ibbackup: Copying /sqldata/mts/test/alex3.ibd (Antelope file format).
101208 17:12:22  ibbackup: Copying /sqldata/mts/test/blobt3.ibd (Antelope file format).
101208 17:12:23  ibbackup: Copying /sqldata/mts/test/ibstest0.ibd (Antelope file format).
101208 17:12:24  ibbackup: Copying /sqldata/mts/test/ibtest09.ibd (Antelope file format).
ibbackup: A copied database page was modified at 2654252454.
ibbackup: Scanned log up to lsn 2654252454.
ibbackup: Was able to parse the log up to lsn 2654252454.
ibbackup: Maximum page number for a log record 0
ibbackup: Backup contains changes from lsn 2638548216 to lsn 2654252454
101208 17:12:24  ibbackup: Incremental backup completed!

This is the initial state of the backup files from the full backup:

$ ls -hlR /full-backup/
/full-backup/:
total 105M
-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:11 ibbackup_logfile
-rw-r----- 1 pekka pekka  10M 2010-12-08 17:11 ibdata1
-rw-r----- 1 pekka pekka  20M 2010-12-08 17:11 ibdata2
-rw-r----- 1 pekka pekka  74M 2010-12-08 17:11 ibdata3
drwxr-x--- 2 pekka pekka   48 2010-12-08 17:11 test

/full-backup/test:
total 69M
-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  19M 2010-12-08 17:11 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

These are the (much smaller) files created by the incremental backup:

$ ls -hlR /incr-backup/
/incr-backup/:
total 2.7M
-rw-r--r-- 1 pekka pekka  155 2010-12-08 17:12 ibbackup_export_variables.txt
-rw-r--r-- 1 pekka pekka  118 2010-12-08 17:12 ibbackup_ibd_files
-rw-r----- 1 pekka pekka 1.0K 2010-12-08 17:12 ibbackup_logfile
-rw-r----- 1 pekka pekka 2.2M 2010-12-08 17:12 ibdata1
-rw-r----- 1 pekka pekka 481K 2010-12-08 17:12 ibdata2
drwxr-x--- 2 pekka pekka   24 2010-12-08 17:12 test

/incr-backup/test:
total 14M
-rw-r----- 1 pekka pekka 14M 2010-12-08 17:12 blobt3.ibd

Next steps: