✏️ Explanatory Question

mysqldump vs Percona XtraBackup — logical vs physical backups

👁 7 Views
📘 Detailed Answer
🟢 Easy
← Previous Functional, invisible, and descending indexes — MySQL 8.0 index features
💡

Answer with Explanation

Section 22: Backup & Recovery

129

mysqldump vs Percona XtraBackup — logical vs physical backups

Level: Hard — A core DBA responsibility; choosing the wrong backup method can mean hours of downtime during a restore.

Scenario: You must set up backups for a 500 GB production database. Using mysqldump takes 6 hours to back up and even longer to restore, locking things up. The interviewer asks: "What is the difference between logical and physical backups, and which would you use for a large, busy database?"

The Two Categories of Backup

  • Logical backup (mysqldump): Exports data as SQL statements (CREATE TABLE + INSERTs). Portable and human-readable, but slow to create and restore because the server re-executes every statement.
  • Physical backup (XtraBackup): Copies the actual data files (InnoDB pages) at the filesystem level. Much faster for large databases and restores by copying files back.

Logical vs Physical Backup

Aspectmysqldump (Logical)XtraBackup (Physical)
What it copiesSQL statementsRaw data files
Speed (large DB)SlowFast
Restore speedSlow (re-runs SQL)Fast (file copy)
Locking impactCan lock (without care)Hot / non-blocking
PortabilityHigh (any version/engine)Lower (version-specific)
Selective restoreEasy (single table)Harder (whole instance)
Best forSmall DBs, migrationsLarge, busy production DBs

The rule of thumb: Use mysqldump for small databases, migrations, or when you need portability and single-table restores. Use a physical backup (XtraBackup / MySQL Enterprise Backup / clone) for large production databases where speed and low locking matter.

mysqldump — Logical Backup (do it safely)

# Consistent backup of one database WITHOUT locking (InnoDB)
mysqldump --single-transaction --routines --triggers --events \
          -u root -p shop > shop_backup.sql

# --single-transaction: consistent snapshot via one transaction (no table locks)
# --routines/--triggers/--events: include stored programs

# Backup all databases + binlog position (for point-in-time recovery)
mysqldump --single-transaction --master-data=2 --all-databases \
          -u root -p > full_backup.sql

# Restore = feed the SQL back to the server
mysql -u root -p shop < shop_backup.sql

Critical flag — --single-transaction: Without it, mysqldump can lock tables and block writes for the whole dump. With it (on InnoDB), it takes a single consistent snapshot using a transaction, so the database keeps serving traffic. Always use it for InnoDB.

Percona XtraBackup — Physical Backup (hot, non-blocking)

# Take a hot physical backup (server keeps running, no locking of InnoDB)
xtrabackup --backup --target-dir=/backups/full \
           --user=root --password=secret

# Prepare it (apply redo logs to make it consistent)
xtrabackup --prepare --target-dir=/backups/full

# Restore: stop MySQL, copy files back, fix permissions, start
xtrabackup --copy-back --target-dir=/backups/full
# chown -R mysql:mysql /var/lib/mysql && start mysqld

Also Worth Knowing

  • MySQL 8.0 CLONE plugin: built-in physical clone of an instance (great for provisioning replicas).
  • Filesystem/LVM snapshots: very fast, but need FLUSH + consistency care.
  • Incremental backups: XtraBackup can back up only changes since the last backup, saving time and space.

Interviewer follow-up: "A backup is useless if you have never restored it — how do you ensure yours works?" → Regularly test restores on a separate server (automated restore drills). Verify row counts and checksums, measure the actual restore time against your RTO (Recovery Time Objective), and store backups off-site/encrypted. An untested backup is a hope, not a strategy.

← Previous Functional, invisible, and descending indexes — MySQL 8.0 index features