Understanding XtraBackup Full Backup Process for MySQL

MySQL backup tools can be broadly divided into physical backups, which copy the database's data files and are suited for large volumes and fast recovery, and logical backups, which consist of SQL statements representing schema and data and are appropriate for smaller datasets or cross‑version migrations.

This article focuses on XtraBackup, a physical hot‑backup utility that supports online backups by copying all MySQL data files and relevant transaction logs, leveraging InnoDB's crash‑recovery mechanism and applying redo logs during restoration to guarantee consistency.

Full backup workflow:

Copy existing redo log and continuously monitor and copy its changes.

Copy InnoDB data files.

Wait until data‑file copying completes.

Acquire a global read lock.

Backup non‑transactional engine files and other necessary files.

Capture binlog position and other metadata.

Stop copying redo log.

Release the global read lock.

Copy the buffer‑pool dump.

Backup completes.

Tip: When many non‑transactional files exist, the global read‑lock duration may be long.

FAQ: The redo log is copied first because XtraBackup relies on InnoDB's crash‑recovery; the redo log records all transaction changes, allowing missing or modified pages in data files to be reconstructed during restore, ensuring consistency between transactional and non‑transactional data.

The full backup process summary reiterates the ten steps above, emphasizing the importance of redo‑log handling and global read‑lock coordination.

XtraBackup operates on InnoDB's crash‑recovery mechanism: during restore, redo logs are replayed to produce complete data files, and the global read lock guarantees that both InnoDB and non‑InnoDB data remain consistent.

Full restore workflow:

Run xtrabackup --prepare to simulate MySQL recovery, replaying redo logs into data files.

Wait for the recovery to finish.

Rebuild redo logs to prepare for database startup.

Run xtrabackup --copy-back (or move‑back) to copy data files back to the MySQL data directory.

Restoration completes.

FAQ: After recovery, InnoDB data reflects the state at the moment the global read lock was taken, and non‑InnoDB data, copied during that lock, is consistent with the restored InnoDB data.