How to Set Up Percona XtraBackup for MySQL Hot Backups

How to Set Up Percona XtraBackup for MySQL Hot Backups

Percona XtraBackup performs non-blocking backups of MySQL and MariaDB databases. Unlike mysqldump, it creates physical backups without locking tables, making it ideal for production databases on your Breeze.

Install Percona XtraBackup

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release enable-only tools release
sudo apt update
sudo apt install -y percona-xtrabackup-80

Create a Full Backup

# Create backup directory
sudo mkdir -p /var/backups/mysql

# Run a full backup
sudo xtrabackup --backup --target-dir=/var/backups/mysql/full \
  --user=root --password=yourpassword

# Prepare the backup for restoration
sudo xtrabackup --prepare --target-dir=/var/backups/mysql/full

Incremental Backups

# Create an incremental backup based on the full backup
sudo xtrabackup --backup --target-dir=/var/backups/mysql/inc1 \
  --incremental-basedir=/var/backups/mysql/full \
  --user=root --password=yourpassword

Restore from Backup

# Stop MySQL
sudo systemctl stop mysql

# Clear data directory and restore
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/var/backups/mysql/full
sudo chown -R mysql:mysql /var/lib/mysql

sudo systemctl start mysql

Automate with Cron

# Full backup Sunday, incremental Mon-Sat
0 2 * * 0 /usr/bin/xtrabackup --backup --target-dir=/var/backups/mysql/full_$(date +\%F)
0 2 * * 1-6 /usr/bin/xtrabackup --backup --target-dir=/var/backups/mysql/inc_$(date +\%F) --incremental-basedir=/var/backups/mysql/full_latest

Always test restoration periodically to verify your backups are valid. Store copies offsite for disaster recovery on your Breeze infrastructure.