MySQL Backups with Percona XtraBackup

Introduction¶

mysqldump produces SQL text — portable, but slow to take and very slow to restore on a large database (it replays every INSERT and rebuilds every index). Percona XtraBackup copies the InnoDB data files while the server runs, producing a physical backup you can restore in minutes.

Physical vs logical¶

Use physical for the routine full/incremental backup of a large DB; keep an occasional logical dump for portability and single-table recovery.

A full backup¶

xtrabackup --backup \
  --target-dir=/backups/full-$(date +%F) \
  --user=backup --password="$BACKUP_PW" \
  --parallel=4 --compress --compress-threads=4

XtraBackup copies the InnoDB files, tracking pages that change during the copy via the redo log, so the result is crash-consistent. A short LOCK INSTANCE FOR BACKUP (MySQL 8) covers non-InnoDB bits and DDL.

The xtrabackup_binlog_info / xtrabackup_info files in the target dir record the binlog position and GTID set — needed to set up a replica from this backup.

Prepare, then restore¶

A raw backup isn't directly usable — the redo log must be applied ("prepared"):

# Decompress first if you used --compress
xtrabackup --decompress --parallel=4 --target-dir=/backups/full-2026-08-27

# Apply redo -> consistent data files
xtrabackup --prepare --target-dir=/backups/full-2026-08-27

Restore to a stopped MySQL:

systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backups/full-2026-08-27
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

--move-back instead of --copy-back if you don't need to keep the backup dir.

Incrementals¶

# Base = the last full (or previous incremental)
xtrabackup --backup --target-dir=/backups/inc-1 \
  --incremental-basedir=/backups/full-2026-08-27 ...

# Prepare: full first (with --apply-log-only), then each incremental in order
xtrabackup --prepare --apply-log-only --target-dir=/backups/full-2026-08-27
xtrabackup --prepare --apply-log-only \
  --target-dir=/backups/full-2026-08-27 --incremental-dir=/backups/inc-1
# final prepare WITHOUT --apply-log-only on the last one
xtrabackup --prepare --target-dir=/backups/full-2026-08-27 --incremental-dir=/backups/inc-2

Order matters and --apply-log-only on all but the last is mandatory — get it wrong and the backup is unusable.

Streaming to another host¶

xtrabackup --backup --stream=xbstream --parallel=4 --compress | \
  ssh backup@dr-host "xbstream -x -C /backups/incoming"

Point-in-time recovery¶

Restore the last full+incrementals, then replay binlogs from xtrabackup_binlog_info up to the moment before the mistake:

mysqlbinlog --start-position=<pos> --stop-datetime="2026-08-27 14:29:59" \
  /var/lib/mysql/mysql-bin.0001{42,43} | mysql

This requires binary logging enabled on the source and the binlogs retained.

Verification and troubleshooting¶

A backup you haven't restored is not a backup. Automate a weekly restore to a scratch host and run mysqlcheck / a checksum:

# On the restored instance
mysqlcheck --all-databases --check
pt-table-checksum        # if comparing against a live source

• --prepare fails "cannot find ... redo log" — backup was interrupted, or disk filled mid-backup. The backup is incomplete; take a new one.
• Restored DB won't start, "Table doesn't exist in engine" / tablespace errors — you skipped --prepare, mixed incrementals out of order, or forgot --apply-log-only. Also check file ownership is mysql:mysql.
• Version mismatch — restoring an 8.0.35 backup onto 8.0.30 can fail; restore onto the same or newer patch release.
• LOCK INSTANCE FOR BACKUP denied — the backup user needs BACKUP_ADMIN (8.0). Grant it.
• Backup huge despite --compress — compression is per-file; a mostly-empty but pre-allocated tablespace still copies. Consider --compress + filesystem-level dedup, or logical for very sparse data.
• Incremental larger than expected — a big OPTIMIZE/ALTER rewrote whole tables, so every page "changed". Expected; take a fresh full.

Related tools and reading¶

• On-site: Crontab generator.
• Related posts: MySQL replication basics, Disaster recovery: RPO and RTO.