Backup & Recovery

Intermediate

Administration

mysqldump for logical backups; Percona XtraBackup for hot physical backups; binary log point-in-time recovery complements full backups to minimise data loss on failure.

Overview

MySQL backup strategies fall into two categories: logical backups (exported SQL statements, readable by any MySQL version) and physical backups (raw InnoDB data files, faster for large databases). mysqldump produces logical backups; Percona XtraBackup produces hot physical backups without locking the database. For production databases, a full backup alone is insufficient — binary log (binlog) based point-in-time recovery (PITR) lets you restore to any moment between backups by replaying binlog events. A complete strategy combines nightly full backups with continuous binlog archiving and regular restore drills. Recovery Time Objective (RTO) and Recovery Point Objective (RPO) drive the backup frequency and strategy choice.

mysqldump — logical backups

mysqldump exports tables as SQL INSERT statements. Use --single-transaction for InnoDB tables (consistent snapshot without locking), --master-data=2 to record the binlog position (enables PITR from this backup), and --routines --triggers --events for complete schema export. For large databases, mysqldump is too slow for daily use — use it for smaller databases or for schema-only exports.

Shell — mysqldump backup and restore with binlog position recording

# Full logical backup — InnoDB consistent snapshot
mysqldump   --single-transaction   --master-data=2   --routines --triggers --events   --all-databases   --user=backup_user --password   | gzip > backup_$(date +%Y%m%d).sql.gz

# Single database backup
mysqldump --single-transaction --master-data=2 \
  --databases shop \
  -u backup_user -p > shop_$(date +%Y%m%d).sql

# Restore
gunzip < backup_20240101.sql.gz | mysql -u root -p

# Check binlog position recorded in backup
head -50 backup_20240101.sql | grep CHANGE_MASTER
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=4567;
# Use this position for PITR replay starting point

Percona XtraBackup — hot physical backups

XtraBackup copies InnoDB data files while the database is running, then applies the redo log to bring the backup to a consistent state (--apply-log). Full backup + incremental backups reduce backup window and storage. It is the standard tool for large production databases (100 GB+).

Shell — XtraBackup full + incremental backup and restore

# Full backup
xtrabackup --backup \
  --target-dir=/backup/full \
  --user=backup_user --password=secret

# Incremental backup (since last full)
xtrabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=backup_user --password=secret

# Prepare (apply redo log — makes backup consistent)
xtrabackup --prepare --target-dir=/backup/full

# Apply incremental on top of full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# Restore (stop MySQL first)
systemctl stop mysql
rsync -avrP /backup/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

Point-in-time recovery (PITR) with binary logs

Binary logs record every data change as events. After restoring a backup, replay binlog events up to the target timestamp to recover data lost since the backup. Requires log_bin=ON in my.cnf. Use mysqlbinlog to decode and filter binlog files. Keep binlogs for at least as long as your longest recovery window.

Shell — binary log PITR workflow with position-based replay

# my.cnf — enable binary logging
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW            # ROW format is safer for PITR
expire_logs_days = 14          # keep 2 weeks of binlogs
sync_binlog = 1                # flush to disk on every commit (durability)

# PITR workflow:
# 1. Restore last full backup
mysql -u root -p < backup_20240101.sql

# 2. Find binlog position from backup (recorded by --master-data=2)
# MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=4567

# 3. Replay binlogs from backup position to target time
mysqlbinlog \
  --start-position=4567 \
  --stop-datetime="2024-01-02 14:30:00" \
  /var/log/mysql/mysql-bin.000123 \
  /var/log/mysql/mysql-bin.000124 \
  | mysql -u root -p

# Skip a bad event (e.g., accidental DELETE)
mysqlbinlog \
  --start-position=4567 \
  --stop-position=98765 \   # stop before the bad event
  mysql-bin.000123 | mysql -u root -p
# then continue from after the bad event position

Key Points to Remember

1mysqldump --single-transaction provides InnoDB consistent logical backup without locking; --master-data=2 records binlog position
2Percona XtraBackup creates hot physical backups without locking — the standard for large production InnoDB databases
3PITR requires binlog_format=ROW, log_bin enabled, and binlogs retained for the full recovery window
4Full backup alone gives you an RPO of "since last backup"; adding binlog replay improves RPO to near zero
5Always test restores regularly — a backup that has never been restored is an untested backup
6sync_binlog=1 + innodb_flush_log_at_trx_commit=1 is the safest durability setting but has a write throughput cost

Interview Questions

What is the difference between a logical and physical backup in MySQL?

EasyOracle

Why is --single-transaction needed for consistent mysqldump backups of InnoDB tables?

MediumThoughtworks

How would you perform point-in-time recovery to restore a database to 2 PM after an accidental DELETE at 2:30 PM?

HardAmazon

What is RPO and RTO, and how do they influence backup strategy decisions?

MediumBooking.com

Why does XtraBackup need a --prepare step and what does it do?

MediumFlipkart

Ask Aria about Backup & Recovery

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Connection Pooling

Performance Schema