Mastering MySQL Backups: From Cold Copies to Xtrabackup and Beyond

Backup is the final line of defense for data safety; while it may not guarantee 100% recovery depending on backup frequency, it minimizes loss. Two key metrics evaluate backup recovery: Recovery Point Objective (RPO) – how recent the restored data is, and Recovery Time Objective (RTO) – how quickly the system can be brought back online.

Cold Backup

The simplest approach is to stop the MySQL server, copy the entire data directory, and later restore by copying it back. This method is impractical for production because services must remain available, making hot (online) backup the focus of this article.

Snapshot Backup (Hot Backup)

Snapshot backup leverages the file‑system’s snapshot capability, typically LVM (Logical Volume Manager) on Linux. LVM uses copy‑on‑write to create a point‑in‑time logical copy of a volume, similar to InnoDB’s MVCC but at the file‑system level. The data and log files must reside on the same logical volume; a snapshot is then taken, providing a consistent view of the database. Snapshots are local, so disk failure destroys them, but they enable rapid restoration to the snapshot time and, combined with binary logs, allow point‑in‑time recovery.

Logical Backup

MySQL provides logical backup tools that export data as SQL statements. The built‑in mysqldump is reliable but single‑threaded and slow for large datasets. The community tool mydumper offers multi‑threaded dumping for higher throughput.

mysqldump

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

Backup workflow (simplified):

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

Start a snapshot read for InnoDB tables.

Backup non‑InnoDB files ( *.frm, *.myi, *.myd etc.).

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

Dump InnoDB tables one by one.

Backup completes.

Mydumper

Mydumper follows the same logical‑backup principles but introduces multiple threads, each handling a subset of tables (even down to row level). Consistency still relies on FTWRL: non‑InnoDB tables must be fully copied before the lock is released, and all threads must obtain the same consistency point while the global lock is held.

Physical Backup (Xtrabackup)

Physical backup copies the raw database files and logs, offering faster backup speeds. The commonly used wrapper innobackupex (part of Percona Xtrabackup) handles both InnoDB and non‑InnoDB objects.

Typical innobackupex workflow:

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

Start an InnoDB data‑file copy thread.

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

Backup non‑InnoDB tables and .frm files.

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

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

Record the binary‑log position, confirming a consistent database state.

Release the lock and finish the backup.

Improving Backup Locks

Both logical and physical backups rely heavily on FTWRL, which blocks all writes and can cause significant service disruption, especially with large non‑InnoDB tables or heavy queries. Percona introduced the BACKUP LOCK mechanism to reduce impact:

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

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

These locks minimize downtime and avoid the long wait times associated with traditional FTWRL.

The article concludes with a reminder that understanding each backup method’s trade‑offs and lock behavior is essential for designing reliable MySQL backup strategies.