For an overview of incremental backups and usage information about these options, see Section 3.3.2, “Making an Incremental Backup”.
To take an incremental backup, specify the
--incremental
or
--incremental-with-redo-log-only
,
along with the
--incremental-backup-dir
. All InnoDB
data modified after the specified
LSN is copied in the incremental
backup. Depending on the choice of
--incremental
or
--incremental-with-redo-log-only
other options
are required or recommended.
--incremental
Specifies that the associated
backup
orbackup-to-image
operation is incremental. Also requires either the--incremental-base
option, or the combination of the--start-lsn
and--incremental-backup-dir
options.The incremental aspect applies only to InnoDB tables. By default, all non-InnoDB and
.frm
files are also included in incremental backup. To exclude non-InnoDB data in an incremental backup, use the--only-innodb
option.
--incremental-with-redo-log-only
Specifies an alternative form of incremental backup for a
backup
orbackup-to-image
operation. Also requires either the--incremental-base
option, or the combination of the--start-lsn
and--incremental-backup-dir
options.The incremental backup performed by this option has different performance characteristics and operational limitations than with the
--incremental
option: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 that depends on 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 this case, the mysqlbackup command quickly determines it cannot proceed and returns an error. Your backup script can catch the error and do an incremental backup with the
--incremental
option instead. options.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 theinnodb_log_file_size
setting byinnodb_log_files_in_group
. To compute redo log size at the physical level, look in thedatadir
directory of the MySQL instance and sum the sizes of the files matching the patternib_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 theLOG
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.
Prior to MySQL 5.5, it was common practice to keep the redo logs fairly small to avoid long startup times when the MySQL server was killed rather than shut down normally. In MySQL 5.5 and higher, the performance of crash recovery is significantly improved, as described in Optimizing InnoDB Configuration Variables. With those releases, you can make your redo log files bigger if that helps your backup strategy and your database workload.
This type of incremental backup is not so forgiving of too-low
--start-lsn
values as the standard--incremental
option. 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.NoteTo ensure the LSN values match up exactly between successive incremental backups using this option, Oracle recommends always using 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 see 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, to avoid the likelihood of the backup failing due to historical data 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 if a sudden flurry of updates produced more redo 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, amount of DML activity, and size of your redo log files; do your testing on a server with a realistic data volume and running 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.
As with the
--incremental
option, the incremental aspect applies only to InnoDB tables. By default, all non-InnoDB and.frm
files are also included in incremental backup. To exclude non-InnoDB data in an incremental backup, use the--only-innodb
option.
--incremental-base
=mode
:argument
Property Value Command-Line Format --incremental-base=mode:argument
Type String With this option, the mysqlbackup retrieves the information needed to perform incremental backups from the metadata inside the backup directory rather than from the
--start-lsn
option. It saves you from having to specify an ever-changing, unpredictable LSN value when doing a succession of incremental backups. Instead, you specify a way to locate the previous backup directory through the combination ofmode
andargument
in the option syntax. The alternatives are:dir:
directory_path
You specify the prefix
dir:
followed by a directory path. The path argument points to the root directory where the data from the previous backup is stored. With the first incremental backup, you specify the directory holding the full backup; with the second incremental backup, you specify the directory holding the first incremental backup, and so on.history:last_backup
You specify the prefix
history:
followed bylast_backup
, the only valid argument for this mode. This makes mysqlbackup query theend_lsn
value from the last successful non-TTS backup as recorded in thebackup_history
table of the server instance that is being backed up.NoteIf the last full or partial backup made was a TTS backup, mysqlbackup skips it, and keeps searching the backup history until it finds the last non-TTS backup and then returns its
end_lsn
value.WarningDo not use the
history:
mode if the previous backup was a full backup taken with the--no-connection
option, which always turns off the recording of backup history and might cause errors in a subsequent incremental backup using this mode of the--incremental-base
option.
--start-lsn
=LSN
Property Value Command-Line Format --start-lsn=LSN
Type Numeric In an incremental backup, specifies the highest LSN value included in a previous backup. You can get this value from the output of the previous backup operation, or from the
backup_history
table'send_lsn
column for the previous backup operation. Always used in combination with the--incremental
option; not needed when you use the--incremental-base
option; not recommended when you use the--incremental-with-redo-log-only
mechanism for incremental backups.NoteNo 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, instead of--start-lsn
, use the--incremental-base
option, 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.
--incremental-backup-dir
=PATH
Property Value Command-Line Format --incremental-backup-dir=PATH
Type Directory name Specifies the location under which to store data from an incremental backup. This is the same location you specify with
--incremental-base
if you use that option in thedir:
mode for a subsequent incremental backup.directory_path
Example 5.4 Incremental Backup
These examples show typical combinations of options used for incremental backups.
mysqlbackup --incremental \
--incremental-backup-dir=/var/mysql/backup/latest \
--incremental-base=dir:/var/mysql/backup/previous \
... backup
mysqlbackup --incremental-with-redo-log-only \
--incremental-backup-dir=/var/mysql/backup/latest \
--incremental-base=dir:/var/mysql/backup/previous \
... backup
mysqlbackup --incremental --start-lsn=12345 \
--incremental-backup-dir=/var/mysql/backup/inc \
... backup
mysqlbackup --incremental-with-redo-log-only --start-lsn=12345 \
--incremental-backup-dir=/var/mysql/backup/inc \
... backup