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 at least one full backup before doing any incremental 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 the --incremental option of the mysqlbackup command. You also indicate the point in time of the previous full or incremental backup, through the --start-lsn option, where you specify the highest log sequence number from a previous full or incremental backup.

To prepare the backup data to be restored, you combine each incremental backup with an original full backup. To simplify the process of storing and tracking a sequence of incremental backups, typically you perform a new full backup after a designated period of time, after which you can discard the older incremental backup data. Or, rather than tracking the ending LSN value for each incremental backup and specifying that number with the --start-lsn option of the next incremental backup, you could reuse the same ending LSN value from the full backup each time. In this case, your incremental backups become larger each time because more and more data has changed, but you can prepare the backup to be restored using the full backup and one incremental backup, rather than several sets of 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.

Space 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. For non-InnoDB files, the entire file is included in an incremental backup if that file changed since the previous backup.

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

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.

When an InnoDB table is dropped and you do a subsequent incremental backup, the apply-log step removes the corresponding .ibd file from the full backup directory. Since the backup program cannot have the same insight into the purpose of non-InnoDB files, when a non-InnoDB file is removed between the time of a full backup and a subsequent incremental backup, the apply-log step does not remove that file from the full backup directory. Thus, restoring a backup could result in a deleted file reappearing.

Examples of Incremental Backups

This example uses the mysqlbackup command to make an incremental backup of a MySQL server, including all databases and tables.

$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --incremental \
  --start-lsn=2654255716 --incremental-backup-dir=/incr-backup \
  --with-timestamp 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!

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 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  ibbackup: Incremental backup completed!

Next steps: