Mastering MySQL Backups: From Cold Copies to Xtrabackup Strategies

Cold Backup

The simplest backup method is to stop the MySQL server and copy the entire data directory. Restoration is just copying the files back. In production this is rarely usable because services must remain online, making cold backup impractical.

Snapshot Backup (LVM)

Snapshot backup uses the file‑system’s snapshot capability, typically LVM on Linux. LVM’s copy‑on‑write creates a consistent point‑in‑time copy of the logical volume that holds the data and log files. Only InnoDB is supported because the snapshot works at the block level. Snapshots are local, so they do not protect against disk failure, but they enable rapid recovery to the snapshot moment and, combined with binary logs, allow point‑in‑time recovery.

Logical Backup

Logical backup extracts data via SQL statements. MySQL provides mysqldump, which is single‑threaded and can be slow. The community tool mydumper offers multi‑threaded dumping for faster performance.

Mysqldump

Key options: --single-transaction: Starts a transaction to obtain a consistent snapshot (effective for InnoDB only). --master-data=2: Records the binary‑log position for point‑in‑time recovery.

Backup workflow:

Execute FLUSH TABLES WITH READ LOCK (FTWRL) to block writes.

Start a snapshot read for InnoDB tables.

Dump non‑InnoDB tables ( *.frm, *.myi, *.myd, etc.).

Release the FTWRL lock after non‑InnoDB tables are copied.

Dump InnoDB tables one by one.

Finish the backup.

During the process, InnoDB’s MVCC ensures a consistent view even after the global lock is released, because the snapshot read captures the state at the transaction start.

Mydumper

Mydumper follows the same consistency principles as mysqldump but introduces multiple threads, each handling a subset of tables, potentially down to row‑level granularity. Consistency is still achieved by holding FTWRL while non‑InnoDB tables are fully dumped and by ensuring all InnoDB threads obtain the same snapshot point.

Physical Backup (Xtrabackup)

Physical backup copies the raw database files and logs, offering higher speed. Percona’s innobackupex script wraps xtrabackup to handle both InnoDB and non‑InnoDB objects.

Typical workflow:

Start a redo‑log copy thread from the latest checkpoint.

Start a thread copying InnoDB data files.

When the data copy finishes, invoke FTWRL to obtain a consistent point.

Backup non‑InnoDB system tables and .frm files.

Wait for redo‑log copying to finish (no new transactions are committing).

After the latest redo logs are copied, both InnoDB and non‑InnoDB data are up‑to‑date.

Capture the binary‑log position to mark a consistent recovery point.

Release the lock and finish the backup.

Percona Backup Lock Improvements

Traditional FTWRL blocks all writes and can be delayed by long queries, affecting both primary and replica servers. Percona introduced a lighter‑weight backup lock mechanism:

LOCK TABLES FOR BACKUP : Prevents updates to non‑InnoDB tables and blocks DDL on all tables, but does not block InnoDB reads/writes.

LOCK BINLOG FOR BACKUP : Captures a consistent binary‑log position while still allowing DDL and updates until the binlog is written.

These locks reduce service disruption during backup, especially when all tables are InnoDB, allowing DML operations to continue uninterrupted.

Overall, the article provides a comprehensive comparison of MySQL backup strategies, their operational steps, pros and cons, and practical tips for achieving reliable RPO and RTO targets.