Hot MySQL backup with Percona XtraBackup

Unlike mysqldump, Percona XtraBackup creates InnoDB backups without locking tables and without stopping the server. It is indispensable for databases of 10 GB and larger.

Installing XtraBackup

# For Ubuntu/Debian (MySQL 8.0)
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
percona-release setup pdps-80
apt update && apt install percona-xtrabackup-80

Full backup

# Creating a full backup
xtrabackup --backup \
  --user=root \
  --password='YourPass' \
  --target-dir=/backup/full

# Preparing the backup for restore
xtrabackup --prepare --target-dir=/backup/full

Incremental backup

# Incremental backup based on the full one
xtrabackup --backup \
  --user=root \
  --password='YourPass' \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

# Prepare: the base one first, then the incremental
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# Final prepare
xtrabackup --prepare --target-dir=/backup/full

Restoring from a backup

# Stop MySQL
systemctl stop mysql

# Clear datadir
rm -rf /var/lib/mysql/*

# Copy the data back
xtrabackup --copy-back --target-dir=/backup/full

# Set permissions
chown -R mysql:mysql /var/lib/mysql

# Start MySQL
systemctl start mysql

Automation via cron

#!/bin/bash
# /etc/cron.d/xtrabackup — full every Sunday, incremental on the other days
DATE=$(date +%Y%m%d)
BACKUP_DIR=/backup

if [ $(date +%u) -eq 7 ]; then
  # Full backup
  xtrabackup --backup --user=root --password='Pass' \
    --target-dir=$BACKUP_DIR/full_$DATE
else
  # Incremental
  LAST_FULL=$(ls -td $BACKUP_DIR/full_* | head -1)
  xtrabackup --backup --user=root --password='Pass' \
    --target-dir=$BACKUP_DIR/inc_$DATE \
    --incremental-basedir=$LAST_FULL
fi

Tip: Compress backups with the --compress flag and encrypt them with --encrypt=AES256 before shipping them to remote storage.

Common XtraBackup Errors

Error	Cause	Fix
Failed to connect to MySQL server: Access denied for user	The user lacks RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT privileges	Grant `GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO 'backup_user'@'localhost';`
innodb_log_file_size in backup config differs from server	During --prepare the server compares the backup's log size to the current my.cnf	Do not change `innodb_log_file_size` between backup and restore, or prepare the backup with the parameter set explicitly
PXB will not copy .frm files for non-InnoDB tables	The database has MyISAM tables, which XtraBackup copies differently (not streamed, with a brief lock)	Account for this when estimating backup time for mixed databases, see the engine difference below

Why XtraBackup Does Not Lock InnoDB but Does Lock MyISAM

XtraBackup copies InnoDB files directly from disk while simultaneously reading the redo log, and at the end "replays" the changes accumulated during the copy — tables stay writable the whole time the backup runs. MyISAM tables (and other non-InnoDB engines) have no such log, so XtraBackup takes a brief FLUSH TABLES WITH READ LOCK at the end of the process to copy them consistently. On databases where almost every table is InnoDB, this lock lasts seconds; if the database has many large MyISAM tables, the lock can be noticeable to the application.

When XtraBackup Is Not a Good Fit

For databases under a few gigabytes, the performance difference versus plain mysqldump is not decisive, and mysqldump is easier to restore partially — at the level of individual tables or rows, which XtraBackup cannot do: it always restores the entire data directory as a whole. XtraBackup also requires the server version and the tool version to be compatible (Percona supports matching major versions of MySQL/MariaDB) — after a server upgrade, verify that the installed percona-xtrabackup supports the new version, or --prepare will fail.

Verifying Backup Integrity After Restore

# Make sure the server came up and sees all databases
mysql -u root -p -e "SHOW DATABASES;"

# Compare table counts against the source server
mysql -u root -p -e "
SELECT table_schema, COUNT(*) AS tables
FROM information_schema.tables
GROUP BY table_schema;"

# Check InnoDB state after restore — there should be no recovery warnings
SHOW ENGINE INNODB STATUS\G

# Run a sanity query with the expected row count
mysql -u root -p mydb -e "SELECT COUNT(*) FROM orders;"

Comparing row and table counts against the pre-backup numbers is a required step, not a formality: XtraBackup will silently exit with code 0 even if one of the files was copied incompletely due to a disk error, and that will only surface the first time the corrupted table is accessed. The logical alternative using a row-by-row dump is covered in the article on mysqldump and restore, and restoring changes after a full backup is covered in the article on point-in-time recovery with binlog.