Skip to content
Starting November 2023 Percona XtraBackup 2.4 has reached EOL status. If you have 5.7 databases, we encourage you to upgrade to 8.0 and then install Percona XtraBackup 8.0. Learn more

logo
Percona XtraBackup
Incremental Backup
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup Works
      • Understand version numbers
      • Installing Percona XtraBackup 2.4
      • Installing Percona XtraBackup on Debian and Ubuntu
      • Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
      • Installing Percona XtraBackup from a Binary Tarball
      • Compiling and Installing from Source Code
      • Running Percona XtraBackup in a Docker container
      • Connection and Privileges Needed
      • Configuring xtrabackup
      • The Backup Cycle - Full Backups
      • Incremental Backup
        • Creating an Incremental Backup
        • Preparing the Incremental Backups
      • Compressed Backup
      • Encrypted Backup
      • Percona XtraBackup User Manual
      • Throttling Backups
      • Lockless binary log information
      • Encrypted InnoDB Tablespace Backups
      • lock-ddl-per-table Option Improvements
      • How-tos and Recipes
        • Release notes index
        • Percona XtraBackup 2.4.29 (2023-12-18)
        • Percona XtraBackup 2.4.28 (2023-04-04)
        • Percona XtraBackup 2.4.27 (2022-12-06)
        • Percona XtraBackup 2.4.26
        • Percona XtraBackup 2.4.25
        • Percona XtraBackup 2.4.24
        • Percona XtraBackup 2.4.23
        • Percona XtraBackup 2.4.22
        • Percona XtraBackup 2.4.21
        • Percona XtraBackup 2.4.20
        • Percona XtraBackup 2.4.19
        • Percona XtraBackup 2.4.18
        • Percona XtraBackup 2.4.17
        • Percona XtraBackup 2.4.16
        • Percona XtraBackup 2.4.15
        • Percona XtraBackup 2.4.14
        • Percona XtraBackup 2.4.13
        • Percona XtraBackup 2.4.12
        • Percona XtraBackup 2.4.11
        • Percona XtraBackup 2.4.10
        • Percona XtraBackup 2.4.9
        • Percona XtraBackup 2.4.8
        • Percona XtraBackup 2.4.7-2
        • Percona XtraBackup 2.4.7
        • Percona XtraBackup 2.4.6
        • Percona XtraBackup 2.4.5
        • Percona XtraBackup 2.4.4
        • Percona XtraBackup 2.4.3
        • Percona XtraBackup 2.4.2
        • Percona XtraBackup 2.4.1
      • The xtrabackup Option Reference
      • The innobackupex Option Reference
      • The xbcloud Binary
      • Exponential Backoff
      • Using the xbcloud binary with Microsoft Azure Cloud Storage
      • The xbcrypt binary
      • The xbstream binary
      • Known issues and limitations
      • Frequently Asked Questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version Checking

    • Creating an Incremental Backup
    • Preparing the Incremental Backups

    Incremental Backup¶

    Both the xtrabackup tool and the innobackupex tool support incremental backups. An incremental backup backs up only data that has changed since the last backup.

    You can take multiple incremental backups between each full backup. For example, you can take a full backup once a week and an incremental backup every day, or a full backup every day and incremental backups each hour.

    Incremental backups work because each InnoDB page contains a log sequence number (LSN). The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.

    An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN. There are two algorithms in use to find the set of such pages to be copied. The first one, available with all the server types and versions, checks the page LSN directly by reading all the data pages. The second one, available with Percona Server for MySQL, enables the changed page tracking feature on the server, which will note the pages as they are being changed. This information will be then written out in a compact separate so-called bitmap file. The xtrabackup binary uses that file to read only the data pages it needs for the incremental backup. This features potentially saves many read requests. The latter algorithm is enabled by default if the xtrabackup binary finds the bitmap file. It is possible to specify xtrabackup --incremental-force-scan to read all the pages even if the bitmap data is available.

    Important

    Incremental backups do not compare the data files to the previous backup’s data files. For this reason, running an incremental backup after a partial backup may lead to inconsistent data.

    Incremental backups read the pages and compare their LSN to the last backup’s LSN. You must have a full backup to recover the incremental changes. Without a full backup to act as a base, the incremental backups are useless.

    You can use the --incremental-lsn option to perform an incremental backup without even having the previous backup, if you know its LSN.

    See also: Partial Backups

    Creating an Incremental Backup¶

    To make an incremental backup, begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup. Create the full backup with a following command:

    $ xtrabackup --backup --target-dir=/data/backups/base
    

    If you look at the xtrabackup_checkpoints file, you should see similar content depending on your LSN nuber:

    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 1626007
    last_lsn = 1626007
    compact = 0
    recover_binlog_info = 1
    

    Now that you have a full backup, you can make an incremental backup based on it. Use the following command:

    $ xtrabackup --backup --target-dir=/data/backups/inc1 \
    --incremental-basedir=/data/backups/base
    

    The /data/backups/inc1/ directory should now contain delta files, such as ibdata1.delta and test/table1.ibd.delta. These represent the changes since the LSN 1626007. If you examine the xtrabackup_checkpoints file in this directory, you should see similar content to the following:

    backup_type = incremental
    from_lsn = 1626007
    to_lsn = 4124244
    last_lsn = 4124244
    compact = 0
    recover_binlog_info = 1
    

    from_lsn is the starting LSN of the backup and for incremental it has to be the same as to_lsn (if it is the last checkpoint) of the previous/base backup.

    It’s now possible to use this directory as the base for yet another incremental backup:

    $ xtrabackup --backup --target-dir=/data/backups/inc2 \
    --incremental-basedir=/data/backups/inc1
    

    This folder also contains the xtrabackup_checkpoints:

    backup_type = incremental
    from_lsn = 4124244
    to_lsn = 6938371
    last_lsn = 7110572
    compact = 0
    recover_binlog_info = 1
    

    Note

    In this case you can see that there is a difference between the to_lsn (last checkpoint LSN) and last_lsn (last copied LSN), this means that there was some traffic on the server during the backup process.

    Preparing the Incremental Backups¶

    The xtrabackup --prepare step for incremental backups is not the same as for full backups. In full backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing an incremental backup, because transactions that were uncommitted at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the xtrabackup --apply-log-only option to prevent the rollback phase.

    Note

    If you do not use the xtrabackup --apply-log-only option to prevent the rollback phase, then your incremental backup is useless.After the transactions have been rolled back, further incremental backups cannot be applied.

    Beginning with the full backup you created, you can prepare it, and then apply the incremental differences to it. Recall that you have the following backups:

    /data/backups/base
    /data/backups/inc1
    /data/backups/inc2
    

    To prepare the base backup, you need to run xtrabackup –prepare as usual, but prevent the rollback phase:

    $ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
    

    The output should end with text similar to the following:

    InnoDB: Shutdown completed; log sequence number 1626007
    161011 12:41:04 completed OK!
    

    The log sequence number should match the to_lsn of the base backup, which you saw previously.

    Note

    This backup is safe to restore, even though the operation skipped the rollback phase. If you restore it and start MySQL, InnoDB detects that the rollback phase was not performed, and it will do that in the background. This operation is the same as a crash recovery upon start. In addition, MySQL notifies you that the database was not shut down normally.

    To apply the first incremental backup to the full backup, run the following command:

    $ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
    --incremental-dir=/data/backups/inc1
    

    This applies the delta files to the files in /data/backups/base, which rolls them forward in time to the time of the incremental backup. It then applies the redo log as usual to the result. The final data is in /data/backups/base, not in the incremental directory. You should see an output similar to:

    incremental backup from 1626007 is enabled.
    xtrabackup: cd to /data/backups/base
    xtrabackup: This target seems to be already prepared with --apply-log-only.
    xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(4124244)
    ...
    xtrabackup: page size for /tmp/backups/inc1/ibdata1.delta is 16384 bytes
    Applying /tmp/backups/inc1/ibdata1.delta to ./ibdata1...
    ...
    161011 12:45:56 completed OK!
    

    Again, the LSN should match what you saw from your earlier inspection of the first incremental backup. If you restore the files from /data/backups/base, you should see the state of the database as of the first incremental backup.

    Warning

    Percona XtraBackup does not support using the same incremental backup directory to prepare two copies of backup. Do not run xtrabackup --prepare with the same incremental backup directory (the value of --incremental-dir) more than once.

    Preparing the second incremental backup is a similar process: apply the deltas to the (modified) base backup, and you will roll its data forward in time to the point of the second incremental backup:

    $ xtrabackup --prepare --target-dir=/data/backups/base \
    --incremental-dir=/data/backups/inc2
    

    Note

    xtrabackup --apply-log-only should be used when merging all incrementals except the last one. That’s why the previous line doesn’t contain the xtrabackup --apply-log-only option. Even if the xtrabackup --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.

    Once prepared, incremental backups are the same as the full backups and they can be restored in the same way.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.

    2022-11-10
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs