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
Make a note of the LSN value in the message at the end of
the backup, for example, ibbackup: Was able to
parse the log up to lsn
. You specify
this value when performing incremental backups of changes
that occur after this incremental backup.
LSN_number
Apply the incremental backup to the backup files, so that the backup is ready to be restored at any time. You can move the backup data to a different server first, to avoid the CPU and I/O overhead of this operation on the database server itself.
On a regular schedule, determined by date or amount of database activity, take further take incremental backups.
Optionally, periodically start the cycle over again by taking a full uncompressed or compressed backup. Typically, this milestone happens when you can archive and clear out your oldest backup data.