MySQL Enterprise Backup User's Guide (Version 8.0.20)

4.3.3 Making a Differential or Incremental Backup

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:

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; you should also ignore any instructions that only apply to the handling of multiple incremental backups.

Note

For MySQL Enterprise Backup 8.0.17 and later, you can create a differential backup easily using the option --incremental-base=history:last_full_backup.

See Section 19.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.

Creating Incremental Backups Using Only the Redo Log

The --incremental-with-redo-log-only might offer some benefits over the --incremental option for creating an incremental backup:

Incremental Backup Using Page Tracking

For MySQL Enterprise Backup 8.0.18 and later: mysqlbackup supports creating incremental backups using the page tracking functionality of the MySQL Server, by which mysqlbackup looks for changed pages in the InnoDB data files that have been modified since the last backup and then copies them. In general, incremental backups using page tracking are faster than other kinds of incremental backups performed by mysqlbackup if the majority of the data in the database has not been modified. Using this feature requires the following to be done on the server before the base backup for the incremental backup is made:

Note

The above-mentioned UDFs regarding page tracking require the BACKUP_ADMIN privilege to run.

When the --incremental option is used without any value specified, mysqlbackup performs an incremental backup using the page tracking functionality. User can also specifies --incremental=page-track to make mysqlbackup use the page tracking functionality. However, the prerequisites for making use of the page tracking functionality for incremental backups are:

Note

mysqlbackup needs to be started with enough memory to process all the tracked pages in memory. If there is not enough memory, mysqlbackup throws an error and then exits. A rough guideline is as follows: the default value of 300 [MB] for the --limit-memory option allows mysqlbackup to handle about 600GB of changed data.

Full-scan versus Optimistic Incremental Backup

When the --incremental option is set to full-scan, mysqlbackup performs a full-scan incremental backup, in which it 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 copies those pages. A full-scan incremental backup 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:

For these and other cases in which an optimistic incremental backup is not desirable, perform a full-scan incremental backup, or an incremental backup using page tracking (for MySQL Enterprise Backup 8.0.18 and later). 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.

For MySQL Enterprise Backup 8.0.17 and earlier, full-scan backup is the default method for incremental backups, which is utilized if no value is specified for --incremental.

Other 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. 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 included in an incremental backup if that file has changed since the previous backup, which means the savings for backup resources are less significant when comparing with the case with InnoDB tables.

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.

Examples of Incremental Backups

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:

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:directory_path option; the backup is saved at the location specified by --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

Maintaining a backup schedule:

On how to restore your database using the incremental backups, see Section 5.1.3, “Restoring an Incremental Backup”