5.1.8 Incremental Backup Options

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 or backup-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 or backup-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 the innodb_log_file_size setting by innodb_log_files_in_group. To compute redo log size at the physical level, look in the datadir directory of the MySQL instance and sum 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.

    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.

    Note

    To 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

  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 of mode and argument 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 by last_backup, the only valid argument for this mode. This makes mysqlbackup query the end_lsn value from the last successful non-TTS backup as recorded in the backup_history table of the server instance that is being backed up.

    Note

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

    Warning

    Do 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

  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's end_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.

  Note

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

  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 the dir:directory_path mode for a subsequent incremental backup.

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