Complete Guide to MySQL 8.0 Physical Backup: Percona XtraBackup 8.x and InnoDB Hot Backup Architecture

MySQL 8.0 introduces major internal architecture changes — including a new data dictionary, redesigned redo/undo logging, and transactional DDL — that make it incompatible with legacy backup tools.
In other words:

• MySQL 8.0 → Percona XtraBackup 8.x (PXB 8.x)
• MySQL 5.7 or earlier → Percona XtraBackup 2.4.x

This post explains how to build a non-disruptive (Hot) physical backup system for MySQL 8.0 using Percona XtraBackup 8.x, covering installation, permissions, scripting, log rotation, cron scheduling, recovery, and verification.

---

1. Why You Need XtraBackup 8.x

✅ Compatibility

MySQL 8.0 integrates the data dictionary (DD tables) into InnoDB and changes redo/undo log formats.
Only XtraBackup 8.x can read and back up this structure.
PXB 2.4.x supports MySQL 5.7 and below.

MySQL Version	Supported XtraBackup Version
MySQL 5.7 and below	Percona XtraBackup 2.4.x
MySQL 8.0	Percona XtraBackup 8.x

---

✅ InnoDB Hot Backup Mechanism

PXB leverages InnoDB’s MVCC (Multi-Version Concurrency Control) and redo-log replay (prepare phase) to produce a crash-consistent snapshot.
Even while transactions are writing to the database, PXB copies data pages and then replays redo logs to bring the snapshot up to a consistent point in time.

This enables hot, non-blocking backups — consistent even while reads and writes are ongoing.

---

✅ Minimizing FTWRL with Backup Locks

MySQL 8.0 replaces the traditional FLUSH TABLES WITH READ LOCK (FTWRL) with LOCK INSTANCE FOR BACKUP, allowing PXB 8.x to take short metadata locks while the majority of backup operations remain fully online.

• InnoDB-only environments: essentially zero downtime
• Mixed engines (MyISAM, CSV, etc.): still require locks — don’t use --no-lock

---

2. Installation (Ubuntu 22.04 LTS)

① 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

② Enable the MySQL 8.0 repository

sudo percona-release setup ps80
sudo apt-get update

③ Install XtraBackup 8

sudo apt-get install -y percona-xtrabackup-80

④ Disable the default MySQL repository (optional)

If MySQL is already installed, you can safely disable its APT repo to avoid GPG warnings:

sudo mv /etc/apt/sources.list.d/mysql.list /etc/apt/sources.list.d/mysql.list.disabled 2>/dev/null || true
sudo apt-get update

⑤ Resolve dependency issues

sudo apt --fix-broken install -y
sudo apt install -y libdbd-mysql-perl libcurl4-openssl-dev libev4
sudo apt install -y percona-xtrabackup-80

---

3. Verify Installation and Configuration

xtrabackup --version
# xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 ...

PXB automatically reads MySQL configuration parameters such as
--datadir, --log_bin, and --innodb_buffer_pool_size from my.cnf.
Any updates to my.cnf are automatically reflected in subsequent backups.

To specify a configuration file explicitly:

xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --target-dir=/backup/mysql

---

4. Creating a Backup Account (MySQL 8.0 Recommended)

✅ MySQL 8.0 — single modern privilege

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_pw';
GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

BACKUP_ADMIN automatically includes sub-privileges such as
LOCK INSTANCE FOR BACKUP, RELOAD, PROCESS, and REPLICATION CLIENT.

✅ Legacy (MySQL 5.7 and older)

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_pw';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

---

5. Full Backup Script (with daily log rotation)

/usr/local/bin/mysql_full_backup.sh

#!/bin/bash
set -euo pipefail

# === Config ===
BACKUP_ROOT="/backup/mysql"
FULL_DIR="${BACKUP_ROOT}/full/$(date +%F_%H-%M-%S)"
LOG_DIR="${BACKUP_ROOT}/logs"
LOG_FILE="${LOG_DIR}/full_backup_$(date +%F).log"
PARALLEL=4
NOLOCK="--no-lock"   # use only for InnoDB-only setups
XB_USER="backup_user"
XB_PASS="strong_pw"
# ===============

mkdir -p "$FULL_DIR" "$LOG_DIR"

{
  echo "[$(date +%F_%T)] START full backup -> $FULL_DIR"

  xtrabackup --backup \
    --target-dir="$FULL_DIR" \
    --user="$XB_USER" \
    --password="$XB_PASS" \
    --parallel="$PARALLEL" \
    $NOLOCK

  echo "[$(date +%F_%T)] PREPARE start"
  xtrabackup --prepare --target-dir="$FULL_DIR" --use-memory=4G
  echo "[$(date +%F_%T)] PREPARE done"

  cp -a /etc/mysql/my.cnf "$FULL_DIR"/my.cnf 2>/dev/null || true

  echo "[$(date +%F_%T)] SUCCESS"
} >> "$LOG_FILE" 2>&1

💡 --use-memory controls how much RAM is allocated for redo-log replay during the prepare phase.
Use roughly 10–20 % of system memory, or at least 4 GB on servers with ≥ 32 GB RAM.

Grant execute permission:

chmod +x /usr/local/bin/mysql_full_backup.sh

---

6. Automating with cron

If your server timezone is KST (UTC +9), schedule directly at 03:00.
If the server runs in UTC, KST 03:00 = 18:00 UTC (previous day).

crontab -e
# KST servers
0 3 * * * /usr/local/bin/mysql_full_backup.sh
# UTC servers
# 0 18 * * * /usr/local/bin/mysql_full_backup.sh

---

7. Backup Retention and Log Cleanup

/usr/local/bin/cleanup_mysql_backups.sh

#!/bin/bash
set -euo pipefail
find /backup/mysql/full/ -mindepth 1 -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;
find /backup/mysql/logs/ -type f -name "full_backup_*.log" -mtime +30 -delete

chmod +x /usr/local/bin/cleanup_mysql_backups.sh
crontab -e
0 4 * * * /usr/local/bin/cleanup_mysql_backups.sh

---

8. Restore Procedure

1️⃣ Prepare the backup (if not already prepared)

xtrabackup --prepare --target-dir=/backup/mysql/full/2025-10-08_03-00-02 --use-memory=4G

2️⃣ Stop MySQL

sudo systemctl stop mysql
sudo mv /var/lib/mysql /var/lib/mysql.bak.$(date +%s)

3️⃣ Copy Back and Fix Permissions

xtrabackup --copy-back --target-dir=/backup/mysql/full/2025-10-08_03-00-02
sudo chown -R mysql:mysql /var/lib/mysql

4️⃣ Start MySQL

sudo systemctl start mysql

5️⃣ Finalize

SET GLOBAL innodb_fast_shutdown=0;

Ensures the restored instance performs a clean shutdown and skip crash-recovery on next start.

---

9. InnoDB Hot Backup Summary

Concept	Description
Consistency	InnoDB’s MVCC keeps transaction snapshots; redo logs replay changes to a consistent point.
Why “Hot” Works	Writes continue during backup; redo/undo are applied for crash-consistent recovery.
Backup Lock	MySQL 8.0’s LOCK INSTANCE FOR BACKUP replaces FTWRL to protect metadata with minimal downtime.
--no-lock	Safe only in InnoDB-only environments; mixed engines risk corruption.
Prepare phase	Re-applies redo and finalizes checkpoint for faster restoration.
innodb_fast_shutdown=0	Flushes dirty pages for a clean shutdown and stable restart.

---

10. Operational Best Practices

• Throttle I/O: use --parallel and --throttle to limit disk impact
• Separate storage: store backups on NFS/SAN or a different disk than data
• Compression: use --compress=zstd or OS-level compression if CPU allows
• Incrementals: combine full + incremental (--incremental) to reduce size
• Monitoring: track duration and success via Zabbix / Prometheus
• Recovery drills: regularly perform restore tests — backups are only proven when restored

---

✅ Quick Checklist

Item	Status
Install PXB 8.x (percona-xtrabackup-80)	☐
Create backup user (BACKUP_ADMIN)	☐
Full-backup script + log rotation	☐
Include prepare phase	☐
Cron schedule (KST/UTC verified)	☐
7-day backup / 30-day log retention	☐
Recovery test & integrity check	☐

---

By combining Percona XtraBackup 8.x’s hot-backup engine, MySQL 8.0’s BACKUP_ADMIN privilege, and a properly tuned prepare phase, you can maintain consistent, crash-safe backups with virtually zero disruption to production workloads.

This setup delivers a fully automated, resilient physical-backup system tailored for modern MySQL 8.0 environments.

🛠 마지막 수정일: 2025.10.08