DevOps Knowledge Hub
DevOps Knowledge Hub

Configuring Incremental MySQL Backups with Percona XtraBackup

Master the art of high-efficiency MySQL backups using Percona XtraBackup (PXB) incremental snapshots. This comprehensive guide details the crucial steps for configuring, executing, and restoring sequential incremental backups based on LSN tracking. Learn how to perform the initial full base backup, correctly chain subsequent increments using `--incremental-basedir`, and navigate the multi-phase restoration process, ensuring minimal downtime and optimal storage management for large databases.

44 views

Configuring Incremental MySQL Backups with Percona XtraBackup

Percona XtraBackup (PXB) is the industry standard for performing non-blocking, hot backups of MySQL, MariaDB, and Percona Server instances. While full backups are essential, large databases require an efficient strategy to minimize downtime and storage usage. Incremental backups provide this efficiency by only capturing the data changes that have occurred since the last successful backup.

This guide explores how to leverage PXB to implement a robust incremental backup strategy. We will cover the core concepts, the necessary commands for performing sequential incremental snapshots, and the multi-step process required for successful restoration.

Understanding Percona XtraBackup's Incremental Mechanism

PXB relies on the internal workings of the InnoDB storage engine, specifically tracking changes using the Log Sequence Number (LSN). Every InnoDB page is tagged with an LSN. When PXB performs an incremental backup, it records only those pages whose LSN is greater than the LSN recorded at the time of the previous backup.

Crucially, incremental backups require a full base backup to establish the starting point. All subsequent incremental backups are chained, referencing the immediate preceding backup (which can be the base backup or another incremental).

Key Files and Concepts

  • LSN (Log Sequence Number): The marker used to determine where changes begin and end.
  • xtrabackup_checkpoints: A file created during every backup containing the to_lsn (the LSN reached at the end of the backup) and, for incremental backups, the from_lsn (the starting LSN).
  • --incremental-basedir: The essential flag used during incremental runs, pointing PXB to the directory of the previous backup (which determines the starting LSN).

Step 1: Performing the Full Base Backup

Before taking any incremental snapshots, a full, consistent base backup must be created. This establishes the foundation for the entire backup chain.

We will define our directory structure first:

# Define the base directory for the full backup
BASE_DIR="/data/backups/2023-10-01_FULL"

# Create the directory
mkdir -p $BASE_DIR

# Execute the full backup
xtrabackup --backup \
  --target-dir=$BASE_DIR \
  --user=root --password=secret_password \
  --datadir=/var/lib/mysql

# Verify the LSN of the full backup
cat $BASE_DIR/xtrabackup_checkpoints | grep 'to_lsn'

Note: The target-dir must be empty or non-existent when starting the backup process. Always use credentials with appropriate permissions (e.g., RELOAD, LOCK TABLES, PROCESS, SUPER privileges).

Step 2: Taking the First Incremental Backup

To perform the first incremental backup, we must reference the full base backup directory using the --incremental-basedir flag. PXB will read the to_lsn from the base backup and only copy pages that have changed since that point.

# Define the directory for the first incremental backup
INCREMENTAL_1_DIR="/data/backups/2023-10-02_INC1"

# Execute the incremental backup, referencing the full base
xtrabackup --backup \
  --target-dir=$INCREMENTAL_1_DIR \
  --incremental-basedir=/data/backups/2023-10-01_FULL \
  --user=root --password=secret_password

Step 3: Chaining Subsequent Incremental Backups

For any subsequent incremental backup (INC2, INC3, etc.), the --incremental-basedir must point to the immediate preceding incremental backup directory.

If we want to create INC2 based on the changes since INC1:

# Define the directory for the second incremental backup
INCREMENTAL_2_DIR="/data/backups/2023-10-03_INC2"

# Execute the incremental backup, referencing the previous incremental (INC1)
xtrabackup --backup \
  --target-dir=$INCREMENTAL_2_DIR \
  --incremental-basedir=/data/backups/2023-10-02_INC1 \
  --user=root --password=secret_password

This chaining continues for as long as you maintain the sequence. If any link in the chain is broken (i.e., a directory is missing), the restore process will fail.

Restoration: Applying Incremental Changes

Restoring an incremental backup set is a multi-step process involving applying the changes sequentially to the base backup. Unlike a full backup, incremental backups cannot be copied directly to the data directory.

Restoration Phase 1: Preparing the Base Backup

First, the base backup must be prepared. This is typically done in a temporary staging area to ensure data safety.

When preparing the base backup, PXB performs two essential actions:
1. Applies committed transactions (the redo log).
2. Rolls back uncommitted transactions.

We use the --prepare flag, but critically, we add the --apply-log-only flag. This prevents PXB from rolling back uncommitted transactions, which is necessary because the subsequent incremental logs need those transaction fragments to complete the restore.

# Define staging directory
RESTORE_TARGET="/data/restore_staging"

# Copy the full base backup files to the staging area
rsync -avr /data/backups/2023-10-01_FULL/ $RESTORE_TARGET

# Prepare the base backup, keeping it ready for increments
xtrabackup --prepare --target-dir=$RESTORE_TARGET --apply-log-only

# Look for confirmation: 'completed OK!'

Restoration Phase 2: Applying Incremental Backups Sequentially

Next, apply each incremental backup in the exact chronological order they were taken. Each command must reference the staging directory (the base backup currently being modified) and use the --incremental-dir flag pointing to the specific incremental snapshot.

# Apply Incremental 1
xtrabackup --prepare --target-dir=$RESTORE_TARGET \
  --incremental-dir=/data/backups/2023-10-02_INC1 \
  --apply-log-only

# Apply Incremental 2
xtrabackup --prepare --target-dir=$RESTORE_TARGET \
  --incremental-dir=/data/backups/2023-10-03_INC2 \
  --apply-log-only

# ... continue for all required increments ...

Restoration Phase 3: Finalizing the Preparation

Once the last desired incremental snapshot has been applied, run the final preparation step without the --apply-log-only flag. This allows PXB to perform the final cleanup, rolling back any transactions that were still uncommitted across the entire backup sequence, resulting in a consistent state.

# Finalize the preparation (no --apply-log-only)
xtrabackup --prepare --target-dir=$RESTORE_TARGET

# IMPORTANT: Look for the final confirmation that indicates 'xtrabackup: Recovery completed OK!'

Restoration Phase 4: Copying Data Back to MySQL

After successful preparation, the data in the staging directory ($RESTORE_TARGET) is consistent and ready for use by the MySQL server.

  1. Stop the MySQL service.
  2. Ensure the MySQL data directory is empty or backed up (optional: use the --move-back option if allowed, but --copy-back is safer for recovery).
  3. Use xtrabackup --copy-back to move the files.
  4. Fix permissions.
  5. Restart MySQL.
# Stop MySQL service
service mysql stop

# Copy prepared files back to the MySQL data directory
xtrabackup --copy-back --target-dir=$RESTORE_TARGET

# Ensure proper ownership for the files
chown -R mysql:mysql /var/lib/mysql

# Start MySQL
service mysql start

Best Practices for Incremental Backup Management

Automation and Scripting

Incremental backup strategies are most effective when fully automated. Scripts should manage the LSN chaining automatically, dynamically identifying the most recent backup directory to use for the --incremental-basedir flag.

Retention Policy

Incremental chains can become very long, making restoration slow. A common best practice is the Grandfather-Father-Son (GFS) strategy or differential incremental backups (where all increments reference the full base). Periodically, roll over the chain by performing a new full backup to start a fresh, short chain.

Compression and Throttling

For very busy servers, consider using the --throttle option during the backup phase to limit I/O operations. Use --compress to reduce backup size, which is highly beneficial for the smaller data sets generated by incremental backups.

# Example with compression and throttling
xtrabackup --backup \
  --target-dir=$INCREMENTAL_3_DIR \
  --incremental-basedir=$INCREMENTAL_2_DIR \
  --compress \
  --throttle=100

Verification and Monitoring

Always test your restoration procedures regularly. A backup that cannot be restored is useless. Monitor the disk space consumed by your incremental chain, especially before initiating a new full base backup.