Software Installation

Download the appropriate Percona XtraBackup package from the official website. Ensure the XtraBackup version matches or exceeds your MySQL server version.

# Extract the downloaded archive
tar -zxvf percona-xtrabackup-8.0.34-29-Linux-x86_64.glibc2.17.tar.gz
# Move the extracted directory
mv percona-xtrabackup-8.0.34-29-Linux-x86_64.glibc2.17 /usr/local/xtrabackup
# Create symbolic links for binary files
ln -sf /usr/local/xtrabackup/bin/* /usr/bin/
# Verify the installation
xtrabackup --version

Database User Configuration

Create a dedicated database user with the necessary privileges for performing backups.

CREATE USER 'backupadmin'@'localhost' IDENTIFIED BY 'secure_password';
GRANT BACKUP_ADMIN, SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backupadmin'@'localhost';

Local Full Backup

Execute a complete backup of the database to a local directory.

xtrabackup --backup --slave-info --user=backupadmin --host=127.0.0.1 --port=3306 --password='secure_password' --parallel=4 --target-dir=/backup/full_$(date +"%Y%m%d_%H%M%S") 2>/backup/backup_process.log

Key Parameters:

• --backup: Initiates a full backup operation.
• --user, --host, --port, --password: Connection credentials for the MySQL instance.
• --slave-info<li>--target-dir: Destination path for backup files.</li><li>--parallel: Number of threads for concurrent data copy.</li><li>2>/backup/backup_process.log</li>

A successful backup concludes with a "completed OK" message in the logs.

Backup Artifacts

The backup directory contains the data files along with several metadata files:

• backup-my.cnf: Contains InnoDB parameters used during the prepare phase.
• xtrabackup_logfile: Holds copied redo log data.
• xtrabackup_binlog_info: Records binary log position and GTID information for point-in-time recovery.
• xtrabackup_slave_info: Stores primary server's log coordinates when backing up a replica (requires --slave-info).
• xtrabackup_checkpoints: Documents the backup type (e.g., full-backuped) and LSN (Log Sequence Number) range.
• xtrabackup_info: Provides comprehensive details about the backup session.
• xtrabackup_tablespaces: Lists information about tablespaces included in the backup.

Local Compressed Backup

Perform a full backup with on-the-fly compression to save disk space.

xtrabackup --backup --compress --user=backupadmin --host=127.0.0.1 --password='secure_password' --target-dir=/backup/compressed_full_$(date +%s) 2>/backup/compress.log

The --compress option enables streaming compression using the specified algorithm (default is QuickLZ).

Streaming Full Backup

Send a full backup directly to a streaming program, such as xbstream, for efficient storage or transfer.

xtrabackup --backup --stream=xbstream --user=backupadmin --password='secure_password' --target-dir=./ > /backup/stream_backup.xbstream 2>/backup/stream.log

Streaming Incremental Backup

Create an incremental backup based on a previous full backup.

Step 1: Perform a Base Full Backup

xtrabackup --backup --user=backupadmin --password='secure_password' --target-dir=/backup/base_full

Step 2: Perform an Incremental Backup

xtrabackup --backup --incremental-basedir=/backup/base_full --user=backupadmin --password='secure_password' --target-dir=/backup/incr_one --stream=xbstream > /backup/incr_one.xbstream 2>/backup/incr.log

The --incremental-basedir must point to the directory of the previous full or incremental backup.

Full Backup Restoration

Restore a database from a full backup. This process requires two main phases: Prepare and Restore.

Phase 1: Prepare the Backup

Apply the redo logs to make the backup files consistent and ready for restoration.

xtrabackup --prepare --target-dir=/backup/full_backup_directory

Phase 2: Restore the Data

Stop the MySQL service and replace the existing data directory with the prepared backup. Ensure the MySQL data directory is empty or backed up.

systemctl stop mysql
# Ensure the datadir is backed up or empty
rsync -avrP /backup/full_backup_directory/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Incremental Backup Restoration

Restore a database using a base full backup and subsequent incremental backups.

Step 1: Prepare the Base Full Backup

xtrabackup --prepare --apply-log-only --target-dir=/backup/base_full

The --apply-log-only option is crucial for preparing incremental backups.

Step 2: Prepare the Incremental Backup

Apply the incremental changes to the base backup.

xtrabackup --prepare --apply-log-only --target-dir=/backup/base_full --incremental-dir=/backup/incr_one

Step 3: Final Prepare and Restore

Perform a final prepare without --apply-log-only, then restore the consolidated data.

xtrabackup --prepare --target-dir=/backup/base_full
# Stop MySQL, copy the prepared data from /backup/base_full to the MySQL datadir, adjust permissions, and restart.