MySQL Enterprise Backup User's Guide (Version 4.1.4)
Assuming a good portion of the data on your MySQL server remains unchanged over time, you can increase the speed and reduce the required storage space for your regular backups by backing up not all the data on the server each time, but only the changes to the data which have taken place over time. In order to that, after making first a full backup that contains all data, you can do one of the following:
Performing a series of differential backups.
Each
differential
backups includes all the changes made to the data
since the last full backup was performed. To restore
data up to, for example, time t
, you
simply restore first the full backup, and then, on top
of it, the differential backup taken for time
t
.
Perform a series of incremental backup.
Each incremental
backup only includes the changes since the
previous backup, which can itself be a full or
incremental backup. The first backup in an incremental
series is always then a differential backup; but after
that, each incremental backup only contains the changes
made since that last incremental backup. Each subsequent
incremental backup is thus usually smaller in size than
a differential backup, and is faster to make; that
allows you to make very frequent incremental backups,
and then enables you to restore the database to a more
precise point in time when necessary. However, restoring
data with incremental backups might take longer and more
work: in general, to restore data up to, for example,
time t
, you start with restoring the
full backup, and then restore the incremental backups
one by one, until you are finished with the incremental
backup taken for time t.
MySQL Enterprise Backup supports both incremental and differential backups. You should decide on which backup strategy to adopt by looking at such factors like how much storage space you have, how quickly you have to be able to restore data, and so on.
MySQL Enterprise Backup treats differential backup as a special case of incremental backup that has a full backup as its base. To create a differential backup, simply follow the instructions below for performing incremental backups, and make sure you specify a full backup as the base of your incremental backup using the methods we describe below; you should also ignore any instructions that only apply to the handling of multiple incremental backups.
See Section 16.7, “Incremental Backup Options”, for
descriptions of the mysqlbackup options used
for incremental backups. An Incremental backup is enabled with
one of the two options:
--incremental
and
--incremental-with-redo-log-only
option. See
Creating Incremental Backups Using Only the Redo Log
for their differences.
When creating an incremental backup, you have to indicate to
mysqlbackup 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 on the server. Or, you can specify
an explicit LSN value using the
--start-lsn
option,
providing to mysqlbackup 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 all incremental backups 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.
The --incremental-with-redo-log-only
might offer some benefits over the
--incremental
option for creating an
incremental backup:
The changes to InnoDB tables are determined based on the
contents of the InnoDB
redo log. Since the
redo log files have a fixed size that you know in advance,
it can require less I/O to read the changes from them than
to scan the InnoDB tablespace files to locate the changed
pages, depending on the size of your database, amount of DML
activity, and size of the redo log files.
Since the redo log files act as a circular buffer, with
records of older changes being overwritten as new
DML operations take place,
you must take new incremental backups on a predictable
schedule dictated by the size of the log files and the
amount of redo data generated for your workload. Otherwise,
the redo log might not reach back far enough to record all
the changes since the previous incremental backup, in which
case mysqlbackup will quickly determine
that it cannot proceed and will return an error. Your backup
script should be able to catch that error and then perform
an incremental backup with the
--incremental
option instead.
For example:
To calculate the size of the redo log, issue the command
SHOW
VARIABLES LIKE 'innodb_log_file%'
and, based
on the output, multiply the
innodb_log_file_size
setting by the value of
innodb_log_files_in_group
.
To compute the redo log size at the physical level, look
into the datadir
directory of the
MySQL instance and sum up the sizes of the files
matching the pattern ib_logfile*
.
The InnoDB LSN value
corresponds to the number of bytes written to the redo
log. To check the LSN at some point in time, issue the
command
SHOW ENGINE
INNODB STATUS
and look under the
LOG
heading. While planning your
backup strategy, record the LSN values periodically and
subtract the earlier value from the current one to
calculate how much redo data is generated each hour,
day, and so on.
This type of incremental backup is not so forgiving of
too-low
--start-lsn
values as the standard --incremental
option
is. For example, you cannot make a full backup and then make
a series of
--incremental-with-redo-log-only
backups
all using the same --start-lsn
value. Make
sure to specify the precise end LSN of the previous backup
as the start LSN of the next incremental backup; do not use
arbitrary values.
To ensure the LSN values match up exactly between
successive incremental backups, it is recommended that you
always use the
--incremental-base
option when you use the
--incremental-with-redo-log-only
option.
To judge whether this type of incremental backup is practical and efficient for a particular MySQL instance:
Measure how fast the data changes within the InnoDB redo log files. Check the LSN periodically to decide how much redo data accumulates over the course of some number of hours or days.
Compare the rate of redo log accumulation with the size of the redo log files. Use this ratio to see how often to take an incremental backup, in order to avoid the likelihood of the backup failing because the historical data are not available in the redo log. For example, if you are producing 1GB of redo log data per day, and the combined size of your redo log files is 7GB, you would schedule incremental backups more frequently than once a week. You might perform incremental backups every day or two, to avoid a potential issue when a sudden flurry of updates produced more redo log data than usual.
Benchmark incremental backup times using both the
--incremental
and
--incremental-with-redo-log-only
options, to confirm if the redo log backup technique
performs faster and with less overhead than the
traditional incremental backup method. The result could
depend on the size of your data, the amount of DML
activity, and the size of your redo log files. Do your
testing on a server with a realistic data volume and a
realistic workload. For example, if you have huge redo
log files, reading them in the course of an incremental
backup could take as long as reading the InnoDB data
files using the traditional incremental technique.
Conversely, if your data volume is large, reading all
the data files to find the few changed pages could be
less efficient than processing the much smaller redo log
files.
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.
By default, when the --incremental
option is used for a backup without any value specified,
mysqlbackup scans all InnoDB data files in
the server's data directory to find pages that have been changed
since the last backup was made and then saves those pages. This
is called a full-scan incremental backup, which might not be
very efficient when not many tables have been modified since the
last back up. An optimistic incremental backup, on the other
hand, only scans for changed pages in InnoDB data files that
have been modified since the last backup, thus saving some
unnecessary scan time.
An optimistic incremental backup can be performed by specifying
--incremental=optimistic
.
While an optimistic increment backup might shorten the backup
time, it has the following limitations:
Since this feature makes use of the modification times of the files in the server's data directory, two things must have remained unchanged since the previous backup: (1) the system time on the server, and (2) the location of the data directory. Otherwise, the backup might fail, or an inconsistent incremental backup might be produced.
Optimistic incremental backups cannot be performed with the
--incremental-with-redo-log-only
, for which
mysqlbackup reads the redo log files
instead of scanning the files in the data directory.
If the --start-lsn
option is
used, a full scan is performed even if
--incremental=optimistic
is specified since, in that case,
mysqlbackup cannot determine the point in
time for which the previous backup is consistent, and thus
has no time frame to determine which files have been
modified recently.
For these and other cases in which an optimistic backup is not
desirable, perform a full-scan incremental backup by using the
--incremental
option without specifying a
value, or use --incremental=full-scan
, which
has the same effect.
See Section 4.1.2, “Grant MySQL Privileges to Backup Administrator” on the privileges required for mysqlbackup to perform an optimistic incremental backup. Also see Using Optimistic Backups and Optimistic Incremental Backups Together on how to utilize the two features together in a backup schedule.
These examples use mysqlbackup 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 can do one of the following:
Tell mysqlbackup to query the
end_lsn
value from the last successful
non-TTS backup as
recorded in the backup_history
table on
the server using
--incremental-base
=history:last_backup
.
Advanced: For directory backups,
specify the directory of the previous backup (either full or
incremental) with
--incremental-base
=dir:
,
and mysqlbackup will figure 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 want to use hardcoded names or generate a
sequence of names in your own backup script, rather than
using the directory_path
--with-timestamp
option. If your
last backup was a single-file, you can still use
--incremental-base
=dir:
to provide the location of the temporary directory you
supplied with the
directory_path
--backup-dir
option during the last backup
In the following example, the
--incremental-base=history:last_backup
option is used, given which mysqlbackup
fetches the LSN of the last successful (non-TTS) full or partial
backup from the mysql.backup_history
table
and performs an incremental backup basing on that.
mysqlbackup --defaults-file=/home/dbadmin/my.cnf \ --incremental --incremental-base=history:last_backup \ --backup-dir=/home/dbadmin/temp_dir \ --backup-image=incremental_image1.bi \ backup-to-image
In the following example, an incremental backup similar to the one in the last example but optimistic in nature is performed.
mysqlbackup --defaults-file=/home/dbadmin/my.cnf \ --incremental=optimistic --incremental-base=history:last_backup \ --backup-dir=/home/dbadmin/temp_dir \ --backup-image=incremental_image1.bi \ backup-to-image
Advanced: Use the following command to
create an incremental directory backup using the
--incremental-base
=dir:
option; the backup is saved at the location specified by
directory_path
--incremental-backup-dir
:
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \ --incremental-base=dir:/incr-backup/wednesday \ --incremental-backup-dir=/incr-backup/thursday \ backup
You can also use the --start-lsn
option to specify where the incremental backup should start. You
have to record the LSN of the previous backup reported by
mysqlbackup at the end of the backup:
mysqlbackup: Was able to parse the log up to lsn 2654255716
The number is also recorded in the
meta/backup_variables.txt
file in the
folder specified by --backup-dir
during the backup. Supply then that number to
mysqlbackup using the
--start-lsn
option. The incremental backup then
includes all changes that came after the
specified LSN.
To create an incremental backup image with the
--start-lsn
option, use the following command,
specifying with --backup-dir
the
backup directory, which, in this case, is a directory for
storing the metadata for the backup and some temporary files:
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \ --start-lsn=2654255716 \ --with-timestamp \ --backup-dir=/incr-tmp \ --backup-image=/incr-backup/incremental_image.bi \ backup-to-image
In the following example though, because
--backup-image
does not provide a
full path to the image file to be created, the incremental
backup image is created under the folder specified by
--backup-dir
:
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \ --start-lsn=2654255716 \ --with-timestamp \ --backup-dir=/incr-images \ --backup-image=incremental_image1.bi \ backup-to-image
On a regular schedule determined by date or amount of database activity, take more incremental or differential 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.
On how to restore your database using the incremental backups, see Section 5.1.3, “Restoring an Incremental Backup”