Percona XtraBackup for MySQL Hot Backups

Percona XtraBackup is the industry-standard tool for performing non-blocking, hot backups of MySQL and MariaDB databases without locking tables or interrupting production traffic. It supports full and incremental backups, streaming to remote servers, compression, and encryption, making it the go-to solution for MySQL backup automation in production environments.

Prerequisites

MySQL 5.7+ or MySQL 8.0+ (XtraBackup 8.x for MySQL 8.x)
Root or sudo access on the MySQL server
Sufficient disk space (at least the size of your InnoDB data)
MySQL user with backup privileges

# Required MySQL privileges
mysql -u root -p <<EOF
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'backup-password';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES,
      REPLICATION CLIENT, CREATE TABLESPACE, REPLICATION SLAVE ON *.* 
      TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.* TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;
EOF

Installing XtraBackup

# Install for MySQL 8.0 (Ubuntu/Debian)
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable-only tools release
sudo apt-get update
sudo apt-get install -y percona-xtrabackup-80

# For MySQL 5.7:
sudo apt-get install -y percona-xtrabackup-24

# For CentOS/Rocky Linux
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install -y percona-xtrabackup-80

# Verify installation
xtrabackup --version

# Install qpress for compression support
sudo apt-get install -y qpress  # Ubuntu
sudo yum install -y qpress      # CentOS

Full Backup

# Create backup directory
sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

# Perform a full backup
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --target-dir=/backup/mysql/full \
  --parallel=4

# The backup is not immediately usable - must be prepared first

# Prepare the backup (applies transaction logs)
sudo xtrabackup \
  --prepare \
  --target-dir=/backup/mysql/full

# After prepare, the backup is ready for restore

# Verify backup integrity
ls -la /backup/mysql/full/
cat /backup/mysql/full/xtrabackup_checkpoints

# Expected output:
# backup_type = full-prepared
# from_lsn = 0
# to_lsn = <number>

Incremental Backups

Incremental backups capture only changes since the last backup:

# First, take a full backup (baseline)
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --target-dir=/backup/mysql/base \
  --parallel=4

# Take first incremental backup
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --target-dir=/backup/mysql/inc1 \
  --incremental-basedir=/backup/mysql/base \
  --parallel=4

# Take second incremental (based on first incremental)
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --target-dir=/backup/mysql/inc2 \
  --incremental-basedir=/backup/mysql/inc1 \
  --parallel=4

# Check LSN values to verify chain
cat /backup/mysql/base/xtrabackup_checkpoints
cat /backup/mysql/inc1/xtrabackup_checkpoints
cat /backup/mysql/inc2/xtrabackup_checkpoints

# PREPARING incremental backups (order matters!)
# Step 1: Prepare base backup (with --apply-log-only to leave open for incrementals)
sudo xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/base

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

# Step 3: Apply second incremental
sudo xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/mysql/base \
  --incremental-dir=/backup/mysql/inc2

# Step 4: Final prepare (no --apply-log-only on the last step)
sudo xtrabackup --prepare --target-dir=/backup/mysql/base

# The base directory now contains the fully restored database

Streaming to Remote Servers

Stream backups directly over SSH or to S3 without local disk space:

# Stream to remote server via SSH using xbstream
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --stream=xbstream \
  --parallel=4 \
  2>/var/log/xtrabackup.log | \
  ssh backup-server "xbstream -x -C /backup/mysql/full"

# Stream with compression
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --stream=xbstream \
  --compress \
  --compress-threads=4 \
  --parallel=4 | \
  gzip | \
  ssh backup-server "gzip -d | xbstream -x -C /backup/mysql/full"

# Stream directly to S3 using awscli
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --stream=xbstream \
  --compress \
  --parallel=4 | \
  aws s3 cp - s3://your-backup-bucket/mysql/full_$(date +%Y%m%d_%H%M%S).xbstream

# Extract from xbstream file
xbstream -x -C /restore/dir < /backup/mysql_backup.xbstream

# Restore from S3
aws s3 cp s3://your-backup-bucket/mysql/full_20240115_020000.xbstream - | \
  xbstream -x -C /restore/mysql/

# Decompress if compressed
xtrabackup --decompress --parallel=8 --target-dir=/restore/mysql/

Compression and Encryption

# Compressed backup (reduces backup size by ~60-80%)
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --compress \
  --compress-threads=8 \
  --target-dir=/backup/mysql/compressed \
  --parallel=4

# Decompress before prepare/restore
sudo xtrabackup \
  --decompress \
  --parallel=8 \
  --target-dir=/backup/mysql/compressed

# Encrypted backup (AES-256)
# Generate encryption key
openssl rand -base64 24 > /etc/mysql/backup.key
chmod 600 /etc/mysql/backup.key

sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup.key \
  --encrypt-threads=4 \
  --target-dir=/backup/mysql/encrypted \
  --parallel=4

# Decrypt before prepare/restore
sudo xtrabackup \
  --decrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup.key \
  --parallel=8 \
  --target-dir=/backup/mysql/encrypted

# Combined: compressed AND encrypted
sudo xtrabackup \
  --backup \
  --user=xtrabackup \
  --password=backup-password \
  --compress \
  --compress-threads=4 \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup.key \
  --target-dir=/backup/mysql/full \
  --parallel=4 \
  --stream=xbstream | \
  aws s3 cp - s3://backup-bucket/mysql/$(date +%Y%m%d).xbstream

Point-in-Time Recovery

# Enable binary logging (required for PITR)
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf:
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
binlog_format = ROW
EOF

sudo systemctl restart mysql

# Restore from XtraBackup + binary logs
# Step 1: Prepare and restore the backup
sudo xtrabackup --prepare --target-dir=/backup/mysql/full
sudo systemctl stop mysql
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo xtrabackup --copy-back --target-dir=/backup/mysql/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

# Step 2: Note the binary log position from backup
cat /backup/mysql/full/xtrabackup_binlog_info
# Example output: mysql-bin.000012  1234567

# Step 3: Apply binary logs up to the desired point in time
mysqlbinlog \
  --start-position=1234567 \
  --stop-datetime="2024-01-15 14:30:00" \
  /var/log/mysql/mysql-bin.000012 \
  /var/log/mysql/mysql-bin.000013 | \
  mysql -u root -p

# To skip a specific transaction that caused data loss:
mysqlbinlog \
  --start-position=1234567 \
  --stop-position=2000000 \
  /var/log/mysql/mysql-bin.000012 | mysql -u root -p

mysqlbinlog \
  --start-position=2001000 \  # Skip the bad transaction
  /var/log/mysql/mysql-bin.000012 | mysql -u root -p

Automated Backup Script

cat > /usr/local/bin/mysql-backup.sh <<'EOF'
#!/bin/bash
# MySQL XtraBackup automation script

BACKUP_USER="xtrabackup"
BACKUP_PASS="backup-password"
BACKUP_DIR="/backup/mysql"
S3_BUCKET="s3://your-backup-bucket/mysql"
RETAIN_DAYS=7
FULL_BACKUP_DAY=0  # Sunday = 0

LOG_FILE="/var/log/mysql-backup.log"
DATE=$(date +%Y%m%d_%H%M%S)
DOW=$(date +%u)  # Day of week (1=Mon, 7=Sun)

log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "Starting MySQL backup..."

# Determine backup type
if [ "$DOW" -eq 7 ]; then
  BACKUP_TYPE="full"
  TARGET="${BACKUP_DIR}/full_${DATE}"
  BACKUP_OPTS="--target-dir=${TARGET}"
else
  BACKUP_TYPE="incremental"
  # Find last backup to use as base
  LAST_FULL=$(ls -td ${BACKUP_DIR}/full_* 2>/dev/null | head -1)
  LAST_INC=$(ls -td ${BACKUP_DIR}/inc_* 2>/dev/null | head -1)
  BASE_DIR=${LAST_INC:-$LAST_FULL}

  if [ -z "$BASE_DIR" ]; then
    log "ERROR: No base backup found. Running full backup instead."
    BACKUP_TYPE="full"
    TARGET="${BACKUP_DIR}/full_${DATE}"
    BACKUP_OPTS="--target-dir=${TARGET}"
  else
    TARGET="${BACKUP_DIR}/inc_${DATE}"
    BACKUP_OPTS="--target-dir=${TARGET} --incremental-basedir=${BASE_DIR}"
  fi
fi

log "Backup type: $BACKUP_TYPE"
log "Target: $TARGET"

# Run backup
xtrabackup \
  --backup \
  --user="${BACKUP_USER}" \
  --password="${BACKUP_PASS}" \
  --compress \
  --compress-threads=4 \
  --parallel=4 \
  ${BACKUP_OPTS} >> "$LOG_FILE" 2>&1

if [ $? -ne 0 ]; then
  log "ERROR: Backup failed!"
  exit 1
fi

log "Backup completed successfully."

# Upload to S3
if [ -n "$S3_BUCKET" ]; then
  log "Uploading to S3..."
  aws s3 sync "${TARGET}" "${S3_BUCKET}/${BACKUP_TYPE}_${DATE}/" \
    --storage-class STANDARD_IA >> "$LOG_FILE" 2>&1
  log "S3 upload complete."
fi

# Remove old local backups
find "${BACKUP_DIR}" -maxdepth 1 -type d -mtime +${RETAIN_DAYS} -exec rm -rf {} \; 2>/dev/null
log "Cleaned up backups older than ${RETAIN_DAYS} days."

log "Backup process complete."
EOF

chmod +x /usr/local/bin/mysql-backup.sh

# Schedule with cron (daily at 2 AM)
echo "0 2 * * * mysql /usr/local/bin/mysql-backup.sh" | crontab -

Troubleshooting

Backup fails with "Access denied":

# Check MySQL user permissions
mysql -u xtrabackup -p -e "SHOW GRANTS;"

# For MySQL 8.x, BACKUP_ADMIN privilege is required
mysql -u root -p -e "GRANT BACKUP_ADMIN ON *.* TO 'xtrabackup'@'localhost';"

Backup fails with "Error: Original data directory /var/lib/mysql is not empty":

# Move or remove contents before copy-back
sudo mv /var/lib/mysql /var/lib/mysql.bak
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo xtrabackup --copy-back --target-dir=/backup/mysql/full

Incremental backup not applying:

# Verify LSN chain is intact
cat /backup/mysql/base/xtrabackup_checkpoints
cat /backup/mysql/inc1/xtrabackup_checkpoints

# The 'from_lsn' of inc1 must match 'to_lsn' of base

# If chain is broken, restore from the full backup only

Slow backup performance:

# Increase parallel threads
xtrabackup --backup --parallel=8 ...

# For large tables, increase the IO throttle
xtrabackup --backup --throttle=400 ...  # 400 IOs per second

# Monitor backup progress
tail -f /var/log/xtrabackup.log

Conclusion

Percona XtraBackup provides the safest and most efficient way to backup MySQL production databases with zero downtime and table locking. Combine full weekly backups with daily incrementals for an efficient backup strategy, stream to remote servers or S3 for offsite protection, and test restores regularly to verify backup integrity. With binary logging enabled alongside XtraBackup, you gain point-in-time recovery capability to restore databases to any moment in time.

Percona XtraBackup for MySQL Hot Backups

On this page

On this page

Percona XtraBackup for MySQL Hot Backups

Prerequisites

Installing XtraBackup

Full Backup

Incremental Backups

Streaming to Remote Servers

Compression and Encryption

Point-in-Time Recovery

Automated Backup Script

Troubleshooting

Conclusion

Latest Video

Get $20 Free Credit