Percona XtraBackup is an open-source hot backup utility for MySQL-based servers that doesn't interrupt your database operations during the backup process. It supports both full and incremental backups, making it an ideal solution for database administrators who need to maintain data integrity while ensuring reliable backup systems.
In this comprehensive guide, I'll walk you through the complete process of installing Percona XtraBackup, performing full and incremental backups, and crucially - restoring your database when needed.
The installation process is straightforward. Here's how I set it up:
# Download the Percona XtraBackup bundle
# Download the rpm file based on your Operating System from https://www.percona.com/downloads.
#I have Downloded for Red Hat linux 10
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-4/binary/redhat/10/x86_64/Percona-XtraBackup-8.4.0-4-rc584cb20-el10-x86_64-bundle.tar
# Extract the package
tar -xvf Percona-XtraBackup-8.4.0-4-rc584cb20-el10-x86_64-bundle.tar
# Install using yum localinstall
sudo yum localinstall percona-xtrabackup-84-*.rpm
# Verify installation
xtrabackup --version
Creating a Dedicated Backup User
For security best practices, create a dedicated user with the necessary privileges:
CREATE USER 'backup_admin'@'localhost' IDENTIFIED BY 'your_password';
GRANT SELECT, PROCESS ON *.* TO 'backup_admin'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'backup_admin'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'backup_admin'@'localhost';
GRANT RELOAD, LOCK TABLES ON *.* TO 'backup_admin'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup_admin'@'localhost';
FLUSH PRIVILEGES;
Creating a full backup is the foundation of your backup strategy:
xtrabackup --backup --user=backup_admin --password=******** --socket=/var/lib/mysql/mysql.sock --target-dir=/tmp/backup_dir/
Incremental Backup Strategy
Incremental backups save storage space and time by only backing up changed data since the last backup. Here's my incremental backup sequence:
# First incremental backup based on the full backup
xtrabackup --backup --user=backup_admin --password=******** --socket=/var/lib/mysql/mysql.sock --target-dir=/tmp/inc1 --incremental-basedir=/tmp/backup_dir
# Second incremental backup based on the first incremental
xtrabackup --backup --user=backup_admin --password=******** --socket=/var/lib/mysql/mysql.sock --target-dir=/tmp/inc2 --incremental-basedir=/tmp/inc1
This is the most critical part - restoring your database from backups. The restoration process varies depending on whether you're restoring from a full backup only or from a full backup with incremental backups.
Before starting the restoration process, ensure you:
Stop the MySQL service:
sudo systemctl stop mysql
# or
sudo systemctl stop mysqld
Preserve your original data (recommended):
sudo mv /var/lib/mysql /var/lib/mysql_old
Create a new data directory:
sudo mkdir /var/lib/mysql
If you only have a full backup (no incrementals), the process is straightforward:
# 1. Prepare the backup (if not already done)
xtrabackup --prepare --target-dir=/tmp/backup_dir
# 2. Copy the backup back to MySQL datadir
xtrabackup --copy-back --target-dir=/tmp/backup_dir
# 3. Fix permissions (crucial step!)
sudo chown -R mysql:mysql /var/lib/mysql
# 4. Start MySQL
sudo systemctl start mysql
When restoring from a chain of backups (full + incrementals), you need to prepare them in a specific sequence. Based on your backup setup, here's the restoration process:
Step 1: Prepare the Base Backup
Start by preparing the full backup with --apply-log-only:
xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup_dir
The --apply-log-only option is crucial as it prevents the rollback phase, which is necessary when you plan to apply incremental backups.
Step 2: Apply the First Incremental Backup
Apply the first incremental backup to the prepared full backup:
xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup_dir --incremental-dir=/tmp/inc1
Step 3: Apply the Final Incremental Backup
For the last incremental backup in your chain, you can omit the --apply-log-only option. This final step allows the rollback phase to complete, making the backup ready for restoration:
xtrabackup --prepare --target-dir=/tmp/backup_dir --incremental-dir=/tmp/inc2
Step 4: Restore the Prepared Backup
Now that all backups are prepared and merged, restore them to the MySQL data directory:
# Copy back the consolidated backup
xtrabackup --copy-back --target-dir=/tmp/backup_dir
# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql
# Start MySQL service
sudo systemctl start mysql
If you're short on disk space, you can use --move-back instead of --copy-back:
xtrabackup --move-back --target-dir=/tmp/backup_dir
Note: This option moves rather than copies the files, removing them from the source directory.
Post-Restoration Verification
After restoring your database, it's crucial to verify that everything is working correctly:
Check MySQL Status:
sudo systemctl status mysql
Verify Data Integrity:
mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -p -e "SELECT COUNT(*) FROM your_critical_table;"
Check MySQL Error Logs:
sudo tail -f /var/log/mysql/error.log
If MySQL fails to start due to permission issues:
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
Always check available space before restoring:
df -h /var/lib/mysql
df -h /tmp