Understanding MySQL Backup Tools and the Percona XtraBackup Backup Process

Database backups are essential for protecting production data, and the choice of backup tool and strategy directly impacts recovery efficiency.

Backup tool overview : MySQL offers three categories of backup tools – logical (mysqldump, Mydumper), physical (Mysqlbackup, Percona XtraBackup), and binlog backups (mysqlbinlog). Logical backups export SQL statements, while physical backups copy InnoDB files.

mysqldump workflow :

Lock all tables for reading.

Iterate over each table and dump its data.

Release the read lock.

During a mysqldump, tables remain read‑only, which can be unacceptable for online environments. Options like --master-data or --dump-slave add global locks. mysqldump’s SQL output enables cross‑platform migration but requires careful handling of time zones, character sets, and accidental data loss.

Percona XtraBackup workflow (based on InnoDB crash‑recovery):

Start a redo‑log copy thread, record the current LSN, and continuously copy changed redo logs.

Copy ibdata1, undo tablespaces, and all .ibd files.

Backup non‑InnoDB files, applying a global read lock unless --no-lock is set; optional --safe‑slave‑backup stops the slave SQL thread first.

Backup slave and binlog information (using --slave-info and --binlog-info ).

After file copy, run apply‑log to verify backup usability, compress the backup, and record metadata in xtrabackup_info .

Key log excerpt:

InnoDB: Number of pools: 1
# 下面的3028328就是开始拷贝的lsn
211214 09:56:09 >> log scanned up to (3028328)
InnoDB: Opened 4 undo tablespaces
xtrabackup: Generating a list of tablespaces

Lock‑related options :

--lock-ddl : Executes LOCK TABLES FOR BACKUP to block DDL before copying redo logs.

--lock-ddl-per-table : Locks each InnoDB table individually via MDL.

--no-lock : Skips global read lock, risking inconsistency.

Lock handling functions such as lock_tables_maybe decide whether to use backup‑specific locks, set lock_wait_timeout , and issue FLUSH TABLES WITH READ LOCK when needed.

Q&A highlights :

Restoration returns to the point when LOCK BINLOG FOR BACKUP or FLUSH TABLES WITH READ LOCK was taken.

Binlog is not separately backed up because the lock guarantees consistency between redo logs and binlog.

Redo log LSN after backup is slightly ahead of the binlog position due to timing of lock release.

Lock duration depends on the number and size of non‑InnoDB tables and redo‑log generation speed.

For full‑instance backups, Percona XtraBackup is preferred; mysqldump suits small, selective backups.

The article concludes with a visual summary of the XtraBackup process and emphasizes using a replica with minimal traffic for backup operations.