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.

Incremental backups are typically smaller and take less time than a full backup, making them a good choice for frequent backup jobs. Taking frequent incremental backups ensures you can always restore the database to the same state as a few hours or days in the past, without as much load or storage overhead on the database server as taking frequent full backups.

Incremental backup is enabled through an option to the mysqlbackup command. For straightforward incremental backups, specify the --incremental option. An alternative method uses the --incremental-with-redo-log-only option, requiring additional planning on your part.

You also indicate the point in time of the previous full or incremental backup. For convenience, you can use the --incremental-base option to automatically derive the necessary log sequence number (LSN) from the metadata stored in a previous backup directory. Or, you can specify an explicit LSN value using the --start-lsn option, using the ending LSN from a previous full or incremental backup (see Other Considerations for Incremental Backups on some limitation that applies when using the --start-lsn option).

To prepare the backup data to be restored, you combine each incremental backup with an original full backup. Typically, you perform a new full backup after a designated period of time, after which you can discard the older incremental backup data.

When running the “apply log” step for an incremental backup, you specify the option sequence --incremental apply-log, 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.

Other Considerations for Incremental Backups

The incremental backup feature is primarily intended for InnoDB tables, or non-InnoDB tables that are read-only or rarely updated. Incremental backups detect changes at the level of pages in the InnoDB data files, 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 InnoDB rows or columns.

For non-InnoDB files, the entire file is always included in an incremental backup, which means the savings for backup resources are less significant when comparing with the case with InnoDB tables.

You cannot perform incremental backups with the --compress option.

When making an incremental backup that is based on a backup (full or incremental) created using the --no-locking option, use the --skip-binlog option to skip the backing up of the binary log, as binary log information will be unavailable to mysqlbackup in that situation.

No binary log files are copied into the incremental backup if the --start-lsn option is used. To include binary log files for the period covered by the incremental backup, use the --incremental-base option instead, which provides the necessary information for mysqlbackup to ensure that no gap exists between binary log data included in the previous backup and the current incremental backup.

Examples of Incremental Backups

This example uses the mysqlbackup command to make an incremental backup of a MySQL server, including all databases and tables. We show two alternatives, one using the --incremental-base option and the other using the --start-lsn option.

With the --incremental-base option, you do not have to keep track of LSN values between one backup and the next. Instead, you specify the directory of the previous backup (either full or incremental), and mysqlbackup figures out the starting point for this backup based on the metadata of the earlier one. Because you need a known set of directory names, you might use hardcoded names or generate a sequence of names in your own backup script, rather than using the --with-timestamp option.

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \
  --incremental-base=dir:/incr-backup/wednesday \
  --incremental-backup-dir=/incr-backup/thursday \
  backup
...many lines of output...
mysqlbackup: Backup created in directory '/incr-backup/thursday'
mysqlbackup: start_lsn: 2654255717
mysqlbackup: incremental_base_lsn: 2666733462
mysqlbackup: end_lsn: 2666736714

101208 17:14:58 mysqlbackup: mysqlbackup completed OK!

With the --start-lsn option, you do have to record the LSN of the previous backup, but then the location of the previous backup is less significant, so you can use --with-timestamp to create named subdirectories automatically.

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \
  --start-lsn=2654255716 \
  --with-timestamp \
  --incremental-backup-dir=/incr-backup \
  backup
...many lines of output...
mysqlbackup: Backup created in directory '/incr-backup/2010-12-08_17-14-48'
mysqlbackup: start_lsn: 2654255717
mysqlbackup: incremental_base_lsn: 2666733462
mysqlbackup: end_lsn: 2666736714

101208 17:14:58 mysqlbackup: mysqlbackup completed OK!

Wherever you use the --incremental option, you can use the --incremental-with-redo-log-only option instead. Because --incremental-with-redo-log-only is more dependent on the precise LSN than the --incremental option is, use the --incremental-base option rather than the --start-lsn option with this kind of incremental backup.

For this alternative kind of incremental backup to work, the volume of changed information must be low enough, and the redo log files must be large enough, that all the changes since the previous incremental backup must be present in the redo log and not overwritten. See the --incremental-with-redo-log-only option description to learn how to verify those requirements.

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \
  --incremental-base=dir:/incr-backup/wednesday \
  --incremental-backup-dir=/incr-backup/thursday \
  backup
...many lines of output...
mysqlbackup: Backup created in directory '/incr-backup/thursday'
mysqlbackup: start_lsn: 2654255717
mysqlbackup: incremental_base_lsn: 2666733462
mysqlbackup: end_lsn: 2666736714

101208 17:14:58 mysqlbackup: mysqlbackup completed OK!

See Section C.3, “Sample Directory Structure for Incremental Backup” for a listing of files from a typical incremental backup.

Once again, we apply to the full backup any changes that occurred while the backup was running:

$ mysqlbackup --backup-dir=/full-backup/2010-12-08_17-14-11 apply-log
..many lines of output...
101208 17:15:10  mysqlbackup: Full backup prepared for recovery successfully!

101208 17:15:10 mysqlbackup: mysqlbackup completed OK!

Then, we apply the changes from the incremental backup:

$ mysqlbackup --incremental-backup-dir=/incr-backup/2010-12-08_17-14-48 
  --backup-dir=/full-backup/2010-12-08_17-14-11 apply-incremental-backup
...many lines of output...
101208 17:15:12 mysqlbackup: mysqlbackup completed OK!

Now, the data files in the full backup directory are fully up-to-date, as of the time of the last incremental backup.

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

mysqlbackup: 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.

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \
  --start-lsn=2638548215 \
  --incremental-backup-dir=/incr-backup/2010-12-08_17-14-48 \
  --backup-dir=/full-backup/2010-12-08_17-14-11 \
	backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2654252454.
mysqlbackup: Was able to parse the log up to lsn 2654252454.
mysqlbackup: Maximum page number for a log record 0
mysqlbackup: Backup contains changes from lsn 2638548216 to lsn 2654252454
101208 17:12:24  mysqlbackup: Incremental backup completed!

Next steps: