XtraBackup: Physical MySQL Backups for Production

Running mysqldump on a 200 GB production database during business hours is a gamble most teams eventually lose — table locks stall writes, the process drags on for hours, and the resulting SQL file is slow to restore when you need it most. Physical backups copy the raw InnoDB data files directly, which means no locks on InnoDB tables, dramatically faster backup windows, and restores that are measured in minutes rather than hours. Percona XtraBackup is the de-facto open-source tool for this job, and it integrates cleanly into any MySQL 8.0 production environment. This guide walks through everything from installation to incremental backups to a tested restore procedure.

What is XtraBackup — and Why Not Just Use mysqldump?

mysqldump is a logical backup tool: it reads rows, generates INSERT statements, and writes them to a text file. It is straightforward and portable, but it has two serious production limitations. First, without --single-transaction, it acquires a global read lock that blocks writes. Even with --single-transaction, non-InnoDB tables (MyISAM, system tables) still get locked. Second, restoring a logical dump means replaying every INSERT statement, which is brutally slow at scale.

Percona XtraBackup takes a physical backup — it copies the actual .ibd files from the InnoDB tablespace while MySQL is running. It achieves consistency by tracking the InnoDB redo log during the copy and replaying any changes that occurred mid-backup during the prepare phase. The result is a byte-for-byte snapshot of the database that can be restored by simply swapping data directories.

How XtraBackup Works Internally

Understanding the internals prevents surprises at restore time. XtraBackup operates in three phases:

1. Copy phase. XtraBackup opens the InnoDB data files directly (bypassing MySQL's buffer pool) and begins streaming them to the target directory. Simultaneously, it tails the InnoDB redo log (ib_logfile*) and copies every log record written while the copy is in progress.
2. FTWRL phase. Once all InnoDB files are copied, XtraBackup issues FLUSH TABLES WITH READ LOCK to quiesce non-InnoDB tables, records the binary log position and GTID set, then immediately releases the lock.
3. Prepare phase (run separately after backup). XtraBackup replays the redo log against the copied data files, rolling forward committed transactions and rolling back uncommitted ones. After prepare, the files are in a consistent state identical to a clean MySQL shutdown.

This means a raw backup directory cannot be restored directly — the prepare step is mandatory.

Installation

XtraBackup 8.0 targets MySQL 8.0. Use XtraBackup 8.4 for MySQL 8.4+. The examples below use Ubuntu 22.04 and MySQL 8.0.

# Add the Percona repository
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 update
sudo apt install -y percona-xtrabackup-80

# Verify
xtrabackup --version
# xtrabackup version 8.0.35-30 based on MySQL 8.0.35 Linux (x86_64)

On RHEL / 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

Creating a Dedicated Backup User

Never run backups as root or as your application user. Create a minimal-privilege backup user:

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'StrongPassw0rd!';

GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES,
      REPLICATION CLIENT, CREATE TABLESPACE, SYSTEM_VARIABLES_ADMIN
      ON *.* TO 'xtrabackup'@'localhost';

-- Required for encrypted tablespaces
GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'xtrabackup'@'localhost';

FLUSH PRIVILEGES;

Taking a Full Backup

BACKUP_DIR="/var/backups/mysql/full/$(date +%Y-%m-%d_%H-%M-%S)"

xtrabackup \
  --backup \
  --user=xtrabackup \
  --password='StrongPassw0rd!' \
  --target-dir="$BACKUP_DIR" \
  --parallel=4 \
  --compress \
  --compress-threads=4

Key flags:

• --backup: instructs XtraBackup to take a backup (as opposed to prepare or copy-back).
• --target-dir: destination directory. Must not exist or must be empty.
• --parallel: number of threads for copying files. Match to your disk I/O capacity.
• --compress: compress with qpress. Reduces backup size by 60–70% on typical OLTP datasets.

When the backup finishes successfully, the last line of output will be:

completed OK!

If you do not see completed OK!, treat the backup as failed.

Streaming a Backup Directly to S3

Avoid writing to local disk by streaming through xbstream and piping to aws s3 cp:

S3_PATH="s3://your-bucket/mysql-backups/full/$(date +%Y-%m-%d_%H-%M-%S).xbstream"

xtrabackup \
  --backup \
  --user=xtrabackup \
  --password='StrongPassw0rd!' \
  --stream=xbstream \
  --compress \
  --compress-threads=4 \
  --target-dir=/tmp | \
  aws s3 cp - "$S3_PATH"

To restore from this stream, download the file and extract with xbstream -x:

mkdir -p /var/backups/mysql/restore
aws s3 cp "$S3_PATH" - | xbstream -x -C /var/backups/mysql/restore

Incremental Backups

Incremental backups copy only the pages that have changed since the last backup, identified by the LSN (Log Sequence Number) recorded in xtrabackup_checkpoints.

Day 0: Full backup (base)

FULL_DIR="/var/backups/mysql/base"

xtrabackup --backup --user=xtrabackup --password='StrongPassw0rd!' \
  --target-dir="$FULL_DIR" --parallel=4

Day 1: First incremental

INC1_DIR="/var/backups/mysql/inc1"

xtrabackup --backup --user=xtrabackup --password='StrongPassw0rd!' \
  --target-dir="$INC1_DIR" \
  --incremental-basedir="$FULL_DIR" \
  --parallel=4

Day 2: Second incremental (based on inc1)

INC2_DIR="/var/backups/mysql/inc2"

xtrabackup --backup --user=xtrabackup --password='StrongPassw0rd!' \
  --target-dir="$INC2_DIR" \
  --incremental-basedir="$INC1_DIR" \
  --parallel=4

Preparing Backups for Restore

Prepare must be done in a specific order. The critical rule: apply incrementals with --apply-log-only until the very last one.

Step 1: Prepare the full backup (apply-log-only)

xtrabackup --prepare --apply-log-only --target-dir="$FULL_DIR"

--apply-log-only prevents XtraBackup from rolling back uncommitted transactions. This is essential because those transactions may be present in the incremental backups — rolling them back now would break the chain.

Step 2: Apply each incremental (apply-log-only for all but the last)

# Apply inc1 — still use --apply-log-only because inc2 follows
xtrabackup --prepare --apply-log-only \
  --target-dir="$FULL_DIR" \
  --incremental-dir="$INC1_DIR"

# Apply inc2 — this is the last incremental, omit --apply-log-only
xtrabackup --prepare \
  --target-dir="$FULL_DIR" \
  --incremental-dir="$INC2_DIR"

After the final prepare, $FULL_DIR contains a fully consistent, restored-ready snapshot.

Restoring the Backup

Restoration involves replacing the MySQL data directory. Always test this on a non-production server first.

# 1. Stop MySQL
sudo systemctl stop mysql

# 2. Clear the existing data directory (or move it as a safety net)
sudo mv /var/lib/mysql /var/lib/mysql.bak

# 3. Copy back using rsync (XtraBackup's --copy-back does the same thing)
sudo xtrabackup --copy-back --target-dir="$FULL_DIR" --datadir=/var/lib/mysql

# Alternative: use rsync directly for more control
sudo rsync -avrP "$FULL_DIR/" /var/lib/mysql/ --exclude='xtrabackup_*'

# 4. Fix ownership
sudo chown -R mysql:mysql /var/lib/mysql

# 5. Start MySQL
sudo systemctl start mysql

# 6. Verify
mysql -u root -p -e "SHOW DATABASES; SHOW GLOBAL STATUS LIKE 'Uptime';"

Verifying Backups

A backup you have never tested is not a backup — it is a hope. Build verification into your backup workflow.

Check xtrabackup_info

cat "$BACKUP_DIR/xtrabackup_info"

Look for:

• tool_version: confirms which version of XtraBackup ran.
• start_time / end_time: the backup window.
• binlog_pos: the binary log file and position at backup time — essential for point-in-time recovery.
• innodb_from_lsn / innodb_to_lsn: LSN range for this backup.
• partial: should be N for a full backup.
• compressed: compressed or N.

Check xtrabackup_checkpoints

cat "$BACKUP_DIR/xtrabackup_checkpoints"
# backup_type = full-backuped
# from_lsn = 0
# to_lsn = 123456789
# last_lsn = 123456800
# compact = 0
# recover_binlog_info = 0

Automated restore test (weekly)

#!/bin/bash
# restore-test.sh — run on a staging MySQL instance

BACKUP_DIR="$1"
STAGING_DATADIR="/var/lib/mysql-test"
STAGING_PORT=3307

# Prepare
xtrabackup --prepare --target-dir="$BACKUP_DIR"

# Stop staging instance
mysqladmin -u root -p --port=$STAGING_PORT shutdown || true

# Restore
rm -rf "$STAGING_DATADIR"
mkdir -p "$STAGING_DATADIR"
xtrabackup --copy-back --target-dir="$BACKUP_DIR" --datadir="$STAGING_DATADIR"
chown -R mysql:mysql "$STAGING_DATADIR"

# Start staging MySQL
mysqld_safe --datadir="$STAGING_DATADIR" --port=$STAGING_PORT \
  --socket=/tmp/mysql-test.sock --daemonize

sleep 10

# Sanity checks
mysql --socket=/tmp/mysql-test.sock -u root -p \
  -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','performance_schema','information_schema','sys');" \
  && echo "RESTORE_TEST: PASSED" || echo "RESTORE_TEST: FAILED"

Key Takeaways

Managed MySQL Backups Without the Overhead

Configuring XtraBackup, managing retention policies, verifying restore integrity, and keeping incremental chains healthy is meaningful engineering work — but it is not core to your product. JusDB handles physical backup infrastructure for MySQL, PostgreSQL, and other databases as part of a fully managed database service. Automated backups, point-in-time restore, restore testing, and monitoring are included out of the box.

If you would rather ship features than manage backup pipelines, talk to the JusDB team about offloading your database operations.