3.3.2 Making an Incremental Backup

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.

Note

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

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.

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.

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

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.

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.

Next steps:

Make a note of the LSN value in the message at the end of
the backup, for example, mysqlbackup: Was able to
parse the log up to lsn
LSN_number. 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.