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,
where you specify the highest log
sequence number from a previous full or incremental
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
--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.
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
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
step removes the corresponding
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.
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!
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.
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.