Incremental Backup in MySQL Using Page Tracking

Incremental Backup in MySQL Using Page Tracking

Incremental backups of MySQL, specifically for the InnoDB engine, are taken by copying modified pages from the previous backup.

The brute force method takes backups by scanning every page in tablespace file in the server data directory is an expensive operation. The time required for incremental backups increases as the data-dir size increases.

To solve this problem, Percona Server for MySQL introduced a  “Changed Page Tracking” feature in 5.6 that enables Percona XtraBackup (PXB) to copy only the modified pages required for incremental backups. See the Percona XtraBackup Documentation for more information.

From MySQL 8.0.18, a similar feature “Changed Page Tracking” is implemented, see this blog post.  PXB 8.0.27 supports the upstream MySQL implementation of “Changed Page Tracking” and can take advantage of it for incremental backups.

Backups using Percona Server for MySQL feature “Changed Page Tracking” is deprecated and will be removed in a future release

Cases Where Page Tracking is Useful

When the incremental is small, page tracking can be many times faster; the server is not required to scan all of the database pages. The backup copies the list of pages provided by the page tracking file. 

In our test where one percent of data was changed after the full backup of 100 GB, an incremental backup took 30 seconds compared to the five minutes duration without page tracking.

As the size of incremental backup increases, the benefit of page tracking is reduced. If the incremental backup size is less than 50% of the full backup, then page tracking performed better than a full scan. The results may vary based on the type of workload. For example, if your workload consists of new page inserts or random updates of different database pages, then page tracking may take longer.

Prerequisite

To use this feature, the mysqlbackup component must be installed and enabled on the Server. The user should do this installation before using the page tracking feature.

To verify if the component was installed successfully, use:

After the component is loaded and active on the server, use the –page-tracking option with a backup and subsequent incremental backups will use the page tracking data to copy modified pages from the last backup.

Usage

You can use the –page-tracking option with full or incremental backups with PXB 8.0.27. It serves a dual purpose:

• At the start of the backup, sets the page-tracking data so that the next incremental backup can use page tracking.
• During an incremental backup, call the mysqlbackup component to get the list of modified pages from the previous backup.

Example of a Full Backup

You can see the page tracking information in the log file.

Example of an Incremental Backup

You can see the page tracking information in the log file.

Purging Page-Tracking on Server

When we use page-tracking for PXB, a file is created under the server’s datadir, which collects information about changed pages. This file keeps growing until page tracking is deactivated with SELECT mysqlbackup_page_track_set(false);. If the server is stopped and restarted a new file is opened, but the old file(s) is not purged. As long as the page tracking is not deactivated explicitly, the file(s) grow.

To get rid of old page-track data, that is not needed anymore, the following procedure is recommended before every *full* backup, do:

The big number is the highest possible LSN. It causes all page tracking files to be purged. The purge interface is added in MySQL Server version 8.0.28.

Open Issue with Page Tracking

If an index was recently added to a table after the last LSN checkpoint and it was built in place using an exclusive algorithm. You may get bad incremental backup with page tracking. More details in  PS-8032.