How to Back Up and Restore MySQL with Percona XtraBackup

How to Back Up and Restore MySQL with Percona XtraBackup

Percona XtraBackup is an open-source tool for performing hot backups of MySQL and MariaDB databases without locking tables or interrupting transactions. Unlike mysqldump, XtraBackup creates physical copies of database files, making it significantly faster for large databases on your Breeze server.

Why Use XtraBackup Over mysqldump

• Non-blocking — no table locks during backup (InnoDB hot backup)
• Faster backups — copies raw data files instead of generating SQL
• Faster restores — copy files back instead of replaying SQL statements
• Incremental support — only back up changed pages since the last backup
• Compressed and encrypted backups — built-in support for both

Installing Percona XtraBackup

# Add Percona repository (Ubuntu/Debian)
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup ps-80
sudo apt update && sudo apt install -y percona-xtrabackup-80

# RHEL/AlmaLinux/Rocky
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup ps-80
sudo yum install -y percona-xtrabackup-80

# Verify installation
xtrabackup --version

Full Backup

Create a complete backup of all databases:

# Create the backup
sudo xtrabackup --backup \
  --user=root \
  --password=YourMySQLPass \
  --target-dir=/backup/mysql/full-$(date +%Y%m%d)

# Prepare the backup (apply transaction logs)
sudo xtrabackup --prepare \
  --target-dir=/backup/mysql/full-$(date +%Y%m%d)

Incremental Backups

After a full backup, create incremental backups that only contain changed data:

# First, create a full base backup
sudo xtrabackup --backup \
  --user=root --password=YourMySQLPass \
  --target-dir=/backup/mysql/base

# Create first incremental backup
sudo xtrabackup --backup \
  --user=root --password=YourMySQLPass \
  --target-dir=/backup/mysql/inc1 \
  --incremental-basedir=/backup/mysql/base

# Create second incremental (based on first incremental)
sudo xtrabackup --backup \
  --user=root --password=YourMySQLPass \
  --target-dir=/backup/mysql/inc2 \
  --incremental-basedir=/backup/mysql/inc1

Preparing Incremental Backups for Restore

Apply incremental backups to the full base backup sequentially:

# Prepare the base backup (do NOT use --apply-log-only on the last step)
sudo xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/mysql/base

# Apply first incremental
sudo xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/mysql/base \
  --incremental-dir=/backup/mysql/inc1

# Apply last incremental (without --apply-log-only)
sudo xtrabackup --prepare \
  --target-dir=/backup/mysql/base \
  --incremental-dir=/backup/mysql/inc2

Restoring a Backup

# Stop MySQL
sudo systemctl stop mysql

# Remove existing data directory
sudo rm -rf /var/lib/mysql/*

# Copy backup files to data directory
sudo xtrabackup --copy-back \
  --target-dir=/backup/mysql/base

# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Start MySQL
sudo systemctl start mysql

# Verify the restore
mysql -u root -p -e "SHOW DATABASES; SELECT COUNT(*) FROM your_database.your_table;"

Compressed and Encrypted Backups

# Compressed backup
sudo xtrabackup --backup \
  --user=root --password=YourMySQLPass \
  --target-dir=/backup/mysql/compressed \
  --compress

# Encrypted backup
sudo xtrabackup --backup \
  --user=root --password=YourMySQLPass \
  --target-dir=/backup/mysql/encrypted \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup.key

Automated Backup Script

Create /usr/local/bin/xtrabackup-daily.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/backup/mysql"
FULL_DIR="$BACKUP_DIR/full"
INC_DIR="$BACKUP_DIR/inc-$(date +%Y%m%d_%H%M)"
LOG="/var/log/xtrabackup.log"
DAY_OF_WEEK=$(date +%u)

echo "$(date): Starting XtraBackup" >> "$LOG"

if [ "$DAY_OF_WEEK" -eq 1 ] || [ ! -d "$FULL_DIR" ]; then
    # Full backup on Monday or if no full backup exists
    rm -rf "$FULL_DIR"
    xtrabackup --backup --user=root --password=YourMySQLPass \
      --target-dir="$FULL_DIR" --compress 2>&1 >> "$LOG"
    echo "$(date): Full backup completed" >> "$LOG"
else
    # Incremental backup on other days
    LAST_INC=$(ls -td "$BACKUP_DIR"/inc-* 2>/dev/null | head -1)
    BASE=${LAST_INC:-$FULL_DIR}
    xtrabackup --backup --user=root --password=YourMySQLPass \
      --target-dir="$INC_DIR" \
      --incremental-basedir="$BASE" --compress 2>&1 >> "$LOG"
    echo "$(date): Incremental backup completed (base: $BASE)" >> "$LOG"
fi

# Clean up incrementals older than 14 days
find "$BACKUP_DIR" -maxdepth 1 -name "inc-*" -mtime +14 -exec rm -rf {} \;

echo "$(date): Backup process finished" >> "$LOG"

chmod +x /usr/local/bin/xtrabackup-daily.sh
# Schedule nightly at 1 AM
echo "0 1 * * * /usr/local/bin/xtrabackup-daily.sh" | sudo crontab -

Best Practices

• Always run --prepare on backups before attempting a restore
• Test restores regularly on a separate Breeze instance to verify backup integrity
• Use incremental backups daily with a weekly full backup to save space
• Store backups on a separate volume or remote server from your database
• Encrypt backups that are stored off-site or transferred over the network
• Monitor backup logs for errors and verify completion each morning
• Keep at least two weeks of backup history for point-in-time recovery options