Context & Why: The Limits of Logical Dumps in Production
Early in my career, running a simple mysqldump cron job felt like enough. That changed the moment our main production database grew past 150 GB. The nightly dump caused severe query spikes, trashed the InnoDB buffer pool, and locked tables during peak batch operations. Worse yet, restoring that single dump file during a disaster recovery drill took over six hours.
For high-throughput MySQL workloads, logical dumps simply do not scale. A logical backup rebuilds your entire dataset line by line using millions of SQL INSERT statements. In contrast, physical backups operate directly at the storage layer, copying raw data files, tablespaces, and indexes at the block level.
Percona XtraBackup solves the downtime problem by performing true non-blocking hot backups. While your database handles thousands of live queries per second, XtraBackup copies the InnoDB data pages and tails incoming changes from the redo log in real time. This guarantees a point-in-time, crash-consistent snapshot without locking tables or degrading user traffic.
Installation and Repository Setup
For MySQL 8.0 deployments, install Percona XtraBackup 8.0. Keep in mind that major versions must match: XtraBackup 8.0 cannot back up MySQL 5.7, and XtraBackup 2.4 cannot back up MySQL 8.0.
1. Install the Percona Repository
On Debian and Ubuntu systems, register the official Percona release package:
# Download and register the Percona repository
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
# Enable the tools repository for XtraBackup 8.0
sudo percona-release enable-only tools release
sudo apt-get update
# Install the package
sudo apt-get install -y percona-xtrabackup-80
On RHEL, Rocky Linux, or AlmaLinux systems:
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
2. Create a Dedicated Database Backup User
Never run automated backups using the MySQL root superuser. Instead, provision a dedicated account with the minimum privileges required to read data pages, inspect replication states, and flush logs:
sudo mysql -u root -p
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'StrongBackupPass123!';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT
ON *.* TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;
EXIT;
Configuration: Building Full and Incremental Backup Pipelines
A solid backup strategy typically pairs a weekly base backup with daily incremental runs. Incremental backups only copy data pages modified since the last backup. On a 200 GB database where only 5 GB changes daily, this approach slashes backup windows from 40 minutes down to under 3 minutes.
1. Set Up the Storage Directory
Place your backup directory on a dedicated disk or network mount point to avoid exhausting OS drive capacity:
sudo mkdir -p /var/backups/mysql/{full,inc}
sudo chown -R mysql:mysql /var/backups/mysql
sudo chmod -R 700 /var/backups/mysql
2. Run a Manual Full Backup
Execute your initial full backup to test permissions and baseline performance:
xtrabackup --backup \
--user=xtrabackup \
--password='StrongBackupPass123!' \
--target-dir=/var/backups/mysql/full/base_$(date +%F)
When the command finishes, check the final line of output for completed OK!. The target folder will contain your raw data files alongside a metadata file named xtrabackup_checkpoints, which stores the Log Sequence Number (LSN) checkpoints.
3. Run an Incremental Backup
Incremental backups inspect the target directory against a base backup, copying only pages with an LSN higher than the previous checkpoint:
xtrabackup --backup \
--user=xtrabackup \
--password='StrongBackupPass123!' \
--target-dir=/var/backups/mysql/inc/inc_$(date +%F) \
--incremental-basedir=/var/backups/mysql/full/base_2026-10-01
4. Production Automation Script
Production environments need reliable automation. The following Bash script runs a full backup every Sunday and incremental backups Monday through Saturday. It maintains directory symlinks and purges backups older than 14 days.
Save this script to /usr/local/bin/mysql-backup.sh:
#!/usr/bin/env bash
set -euo pipefail
BACKUP_ROOT="/var/backups/mysql"
FULL_DIR="${BACKUP_ROOT}/full"
INC_DIR="${BACKUP_ROOT}/inc"
USER="xtrabackup"
PASS="StrongBackupPass123!"
TODAY=$(date +%Y-%m-%d)
DAY_OF_WEEK=$(date +%u) # 7 = Sunday
mkdir -p "${FULL_DIR}" "${INC_DIR}"
if [ "$DAY_OF_WEEK" -eq 7 ] || [ ! -d "${FULL_DIR}/latest" ]; then
echo "[$(date)] Starting Full Backup..."
TARGET="${FULL_DIR}/full_${TODAY}"
xtrabackup --backup \
--user="${USER}" \
--password="${PASS}" \
--target-dir="${TARGET}"
ln -sfn "${TARGET}" "${FULL_DIR}/latest"
rm -rf "${INC_DIR}"/*
echo "[$(date)] Full Backup Completed: ${TARGET}"
else
echo "[$(date)] Starting Incremental Backup..."
LAST_INC=$(find "${INC_DIR}" -maxdepth 1 -mindepth 1 -type d | sort | tail -n 1)
if [ -z "${LAST_INC}" ]; then
BASE_DIR="${FULL_DIR}/latest"
else
BASE_DIR="${LAST_INC}"
fi
TARGET="${INC_DIR}/inc_${TODAY}"
xtrabackup --backup \
--user="${USER}" \
--password="${PASS}" \
--target-dir="${TARGET}" \
--incremental-basedir="${BASE_DIR}"
echo "[$(date)] Incremental Backup Completed against base ${BASE_DIR}: ${TARGET}"
fi
# Purge full backups older than 14 days
find "${FULL_DIR}" -maxdepth 1 -mindepth 1 -type d -mtime +14 -exec rm -rf {} +
Set execution permissions and add the job to crontab:
sudo chmod +x /usr/local/bin/mysql-backup.sh
# Schedule to run every night at 02:00 AM
(crontab -l 2>/dev/null; echo "0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1") | crontab -
Verification & Recovery: Testing Restores Before Disaster Strikes
An untested backup is not a backup—it is just wishful thinking. Because physical backups capture live data pages while transactions are in-flight, the files contain uncommitted changes. You must prepare the backup before MySQL can safely use it.
1. Preparing the Backup Chain
To restore a base backup with incremental changes, roll forward committed transactions from each incremental folder into the base directory. Use the --apply-log-only flag on all intermediate steps to keep the base backup open for subsequent diffs:
# Step 1: Prepare the full base backup (keep log open for increments)
xtrabackup --prepare \
--apply-log-only \
--target-dir=/var/backups/mysql/full/full_2026-10-01
# Step 2: Apply the incremental backup to the base directory
xtrabackup --prepare \
--apply-log-only \
--target-dir=/var/backups/mysql/full/full_2026-10-01 \
--incremental-dir=/var/backups/mysql/inc/inc_2026-10-02
# Step 3: Final prepare on base (rolls back uncommitted transactions)
xtrabackup --prepare \
--target-dir=/var/backups/mysql/full/full_2026-10-01
2. Restoring Files to the MySQL Data Directory
Once the base backup is fully prepared, copy the raw data files back to the MySQL directory:
# Stop the database service
sudo systemctl stop mysql
# Back up the current data directory and recreate an empty target
sudo mv /var/lib/mysql /var/lib/mysql.bak_$(date +%F)
sudo mkdir -p /var/lib/mysql
# Restore prepared data files
xtrabackup --copy-back \
--target-dir=/var/backups/mysql/full/full_2026-10-01
# Fix file ownership and permissions
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
# Start MySQL service and verify status
sudo systemctl start mysql
sudo systemctl status mysql
3. Monitoring and Health Checks
Integrate these four operational checks into your monitoring stack (Prometheus node-exporter, Datadog, or Zabbix):
- Process Exit Codes: Alert immediately if the
xtrabackupcommand returns any exit code other than0. - Log String Validation: Parse
/var/log/mysql-backup.logto confirm the presence of thecompleted OK!confirmation string. - LSN Continuity Checks: Inspect
xtrabackup_checkpointsto verify that thefrom_lsnof each incremental run matches theto_lsnof its preceding backup. - Automated Sandbox Restores: Schedule a weekly CI/CD runner to restore the latest backup chain onto an isolated staging instance and run validation queries against critical tables.
