Understanding Incremental Backup and Recovery in MySQL with XtraBackup

In the previous article we introduced the process of full backup and restoration ( [Principle Analysis] XtraBackup Full Backup and Restore ), let’s review the key points of full backup and recovery:

1. At the start of a full backup, the system must listen to and record changes in the redo log.

2. While copying InnoDB data files, the database may still write data, causing inconsistencies between old and new pages.

3. After copying the InnoDB files, a global read lock must be applied before copying non‑transactional information such as binlog positions and non‑transactional engine files.

4. During full recovery, replaying the redo‑log changes recorded in step 1 eliminates the inconsistency introduced in step 2.

When discussing incremental backup and recovery, we need to focus on the following questions:

1. How to identify which InnoDB data pages are incremental.

2. How to solve the new‑old inconsistency problem for incremental data files, similar to full backup.

3. Whether non‑transactional information can be incrementally backed up.

Below is a diagram illustrating the steps of incremental backup:

Discussion 1: When performing an incremental backup, how do we identify which InnoDB data is incremental?

From the full‑backup diagram we can see that each data page carries an LSN number (the concept of LSN was introduced in the previous article). The LSN acts as a logical timestamp for a page.

By comparing this timestamp, we can determine whether a page has been modified after the full backup; thus, LSN allows us to recognize incremental data.

In the next figure, only pages with LSN > 400 are backed up during the incremental backup.

Discussion 2: If a page was not originally in the incremental range but gets updated during the incremental backup, will it be included?

The essence of this problem is the same as the new‑old inconsistency issue in full backup, and the solution is also the same: replay the redo log during recovery to resolve the inconsistency.

In other words, during incremental backup, if a page is updated, the updated page may or may not be copied into the backup, but the change will definitely be recorded in the redo log, which is included in the backup. During recovery, the redo log is safely replayed, ensuring data consistency.

Below is the workflow for incremental backup recovery:

1. Restore the full backup to a temporary directory (see the previous article for detailed steps).

2. Apply the incremental backup by overwriting the temporary directory with the incremental data files.

3. Replay the incremental redo log into the temporary directory.

4. Overwrite other files into the temporary directory.

5. Incremental backup restoration is complete.

6. Rebuild the redo log to prepare for database startup.

7. Copy the files from the temporary directory back to MySQL’s data directory.

Discussion 3: From the diagram we can see that non‑transactional information is directly overwritten into the temporary directory during incremental backup, meaning MySQL does not provide an incremental mechanism for non‑transactional data; it is always fully copied and overwritten.

Consequently, even though the diagram does not show it, a global read lock is still required during incremental backup to guarantee consistency of non‑transactional information.

Key takeaways from this illustration:

1. How to identify which InnoDB data is incremental: the LSN on each page serves as a logical timestamp.

2. How to resolve the new‑old inconsistency for incremental backups: the same solution as full backup, i.e., replaying the redo log.

3. Whether non‑transactional information can be incrementally backed up: it cannot; the process relies on a global read lock, full copy, and full redo‑log replay.

Recent Community Updates

MySQL Principle Illustrated

[Principle Analysis] XtraBackup Full Backup and Restore

[Principle Analysis] MySQL Data Loss Caused by Fixed server_id

[Principle Analysis] MySQL Group Commit

[Principle Analysis] How Character Set Parameters Control Behavior

Drink more hot water, try restarting