Comparing MySQL Backup Solutions: From Tool Mechanics to Production-Ready Engineering Guide

Introduction

Most production MySQL instances have a backup plan, yet many fail when a disaster occurs. Common issues include relying on a simple mysqldump cron job without recovery testing, missing --single-transaction, forgetting to back up ibdata, redo logs, binlog positions, or storing backups on a single machine.

The goal of this guide is to turn the abstract concept of a backup plan into concrete steps: define backup dimensions, explain tool principles and parameters, compare tools, and provide data‑size‑based selection, scripts, scheduling, monitoring, and post‑mortem case studies.

1. Backup Dimensions and Types

1.1 Three Dimensions

Physical vs Logical : Physical copies data files, redo logs, and metadata; logical exports SQL statements.

Full vs Incremental vs Differential : Full copies all data; incremental copies changes since the last backup; differential copies changes since the last full backup.

Offline vs Online : Offline (cold) requires stopping the server; online (hot) runs while the server is serving traffic.

These dimensions can be combined. For example, Percona XtraBackup is "physical + incremental + online" while mysqldump is "logical + full + online".

1.2 Five Types in MySQL

Cold Backup : Stop the server and copy files.

Hot Backup : Run while the server is online.

Warm Backup : Online but briefly holds a lock or snapshot.

Logical Backup : Export SQL statements.

Physical Backup : Copy raw data files.

In InnoDB environments cold backups are rarely recommended; if needed, set innodb_fast_shutdown=0 to flush dirty pages before stopping.

2. Consistency Points

Backup consistency is essential. Inconsistent data leads to corruption after restore. MySQL provides several mechanisms: FLUSH TABLES WITH READ LOCK (FTWRL) – a global read lock. --single-transaction – starts a repeatable‑read snapshot for InnoDB only. --lock-all-tables – locks all tables for a full logical dump.

XtraBackup’s redo‑log chase – copies data files while continuously copying redo logs, then performs a short FTWRL to lock non‑InnoDB tables.

LVM/ZFS snapshots – rely on the filesystem to capture a point‑in‑time view.

Note that --single-transaction works only for InnoDB; mixed MyISAM/MEMORY tables require additional locking.

3. Metadata Required for Recovery

Every backup must record a consistency point using one of the following:

Binlog file and position (e.g., binlog.000123:456789).

GTID set (e.g., 3E11FA47-71CA-11E9-9E11-00163E0A1B22:1-123456).

InnoDB LSN values ( from_lsn, to_lsn, last_lsn) stored in xtrabackup_checkpoints.

During restore, the recorded point is used to replay binlog entries for Point‑In‑Time Recovery (PITR).

4. Tool Landscape

4.1 Logical Tools

mysqldump : Official client, works on all engines, single‑threaded, suitable for < 50 GB. Parameters include --single-transaction, --routines, --triggers, --events, --master-data=2, and --set-gtid-purged.

mysqlpump : MySQL 5.7+ concurrent version of mysqldump. Supports --parallel-schemas but has lower maintenance priority on MySQL 8.0.

mydumper / myloader : Third‑party multi‑threaded logical backup. Splits tables into chunks, supports compression, --use-savepoints, and --no-locks. Works with myloader for fast restore.

mysqlhotcopy : Legacy MyISAM‑only tool, now deprecated.

4.2 Physical Tools

Percona XtraBackup : Physical hot backup for InnoDB, supports incremental, parallel, compression, and streaming. Requires --backup and later --prepare to make the backup usable.

MariaDB Backup (mariabackup) : Equivalent for MariaDB 10.1+.

LVM / ZFS Snapshots : Filesystem‑level point‑in‑time copies. Must acquire a global read lock before snapshot, then release it quickly.

Cloud Provider Snapshots (EBS, OSS, etc.) : CoW or RoW snapshots taken via the provider API. Fast to create but unaware of MySQL internal state; must combine with FLUSH TABLES WITH READ LOCK for consistency.

5. Practical mysqldump Workflow

Typical command:

mysqldump -h127.0.0.1 -P3306 -uroot -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --databases db1 db2 \
  > /backup/db_full_$(date +%Y%m%d_%H%M%S).sql

Key points:

Use --single-transaction for InnoDB consistency.

Include --master-data=2 to embed binlog position.

Specify --set-gtid-purged=OFF for pure data backup; use =ON when initializing a new replica.

Do not rely on the default --opt lock behavior; replace with --single-transaction to avoid table‑level locks.

Recovery is performed by piping the dump into mysql or using gunzip -c … | mysql for compressed files.

6. mysqlpump vs mydumper

mysqlpump

adds parallel schema dumping but has limited community adoption and lower maintenance on MySQL 8.0. mydumper provides true multi‑threaded dumping with chunking, better performance for 50 GB–500 GB databases, and works with myloader for fast restore.

Example mydumper command:

mydumper \
  --host=127.0.0.1 \
  --port=3306 \
  --user=backup_user \
  --threads=8 \
  --rows=200000 \
  --compress \
  --outputdir=/backup/mydumper/20260608 \
  --logfile=/var/log/mydumper_$(date +%Y%m%d).log

Restore with myloader:

myloader \
  --host=127.0.0.1 \
  --port=3306 \
  --user=root \
  --threads=8 \
  --directory=/backup/mydumper/20260608 \
  --overwrite-tables \
  --enable-binlog

7. Percona XtraBackup Deep Dive

7.1 How It Works

XtraBackup starts a background thread that continuously copies InnoDB redo logs while copying all data files. When the redo log reaches a threshold, XtraBackup forces an InnoDB checkpoint to flush dirty pages. After copying finishes, it records the final LSN and binlog info, then briefly acquires FLUSH TABLES WITH READ LOCK for a few seconds.

7.2 Full, Incremental, and Compression

# Full backup
xtrabackup --user=backup_user --password=xxx \
  --target-dir=/backup/xb/20260608_full \
  --backup

# Incremental backup based on previous full backup
xtrabackup --user=backup_user --password=xxx \
  --target-dir=/backup/xb/20260609_inc \
  --incremental-basedir=/backup/xb/20260608_full \
  --backup

# Compressed parallel backup (MySQL 8.0+)
xtrabackup --user=backup_user --password=xxx \
  --target-dir=/backup/xb/20260608_full \
  --backup \
  --compress \
  --parallel=8

7.3 Prepare Phase

The --prepare step replays redo logs and simulates crash recovery, turning the copy‑only backup into a usable data directory.

# Prepare full backup
xtrabackup --prepare --target-dir=/backup/xb/20260608_full

When merging incrementals, use --apply-log-only on the full backup, then apply each incremental with --apply-log-only, and finally run a normal --prepare without the flag.

7.4 Restore Procedure

# Stop MySQL
mysqladmin -uroot -p shutdown
# Remove old datadir and copy back
rm -rf /var/lib/mysql && mkdir /var/lib/mysql
xtrabackup --copy-back --target-dir=/backup/xb/20260608_full
chown -R mysql:mysql /var/lib/mysql
# Start MySQL and apply binlog
systemctl start mysqld
mysqlbinlog --start-position=456789 \
  --stop-datetime="2026-06-09 10:00:00" \
  /var/lib/mysql/binlog.000123 | mysql -uroot -p

8. Binlog Backup and PITR

Three binlog formats exist: STATEMENT (small, but nondeterministic), ROW (precise, larger), and MIXED (default). Verify the current format with SHOW VARIABLES LIKE 'binlog_format'; Typical binlog archiving script:

#!/usr/bin/env bash
set -euo pipefail
REMOTE_HOST=127.0.0.1
MYSQL_USER=backup_user
LOCAL_DIR=/backup/binlog
TS=$(date +%Y%m%d)
mkdir -p "$LOCAL_DIR"
mysqlbinlog --read-from-remote-server \
  --host="$REMOTE_HOST" --user="$MYSQL_USER" \
  --raw --stop-never \
  --result-file="$LOCAL_DIR" binlog.000001 &

To recover to a specific point:

# Restore full backup first
mysql < /backup/xb/full_20260608.sql
# Then apply binlog up to the desired time
mysqlbinlog --start-position=456789 \
  --stop-datetime="2026-06-09 10:00:00" \
  /backup/binlog/binlog.000123 | mysql -uroot -p

In GTID mode, set GTID_PURGED before applying binlog to skip already executed transactions.

9. Filesystem Snapshots

9.1 LVM Snapshot Procedure

# 1. Global read lock
mysql -uroot -p -e "FLUSH TABLES WITH READ LOCK; FLUSH LOGS; SHOW MASTER STATUS;"
# 2. Create snapshot
lvcreate -L 10G -s -n mysql_snap /dev/vg0/mysql_data
# 3. Release lock
mysql -uroot -p -e "UNLOCK TABLES;"
# 4. Mount and copy
mkdir -p /mnt/mysql_snap
mount -o ro /dev/vg0/mysql_snap /mnt/mysql_snap
tar czf /backup/lvm_$(date +%Y%m%d).tar.gz -C /mnt/mysql_snap .
# 5. Cleanup
umount /mnt/mysql_snap
lvremove -f /dev/vg0/mysql_snap

9.2 ZFS Snapshot Procedure

# 1. Global lock
mysql -uroot -p -e "FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;"
# 2. Snapshot
zfs snapshot tank/mysql@$(date +%Y%m%d_%H%M%S)
# 3. Release lock
mysql -uroot -p -e "UNLOCK TABLES;"
# 4. Export
zfs send tank/mysql@$(date +%Y%m%d_%H%M%S) | gzip > /backup/zfs_$(date +%Y%m%d_%H%M%S).gz

Snapshots are fast but provide no compression or encryption; combine with XtraBackup for application‑level consistency.

10. Tool Capability Comparison (Summary)

Logical tools ( mysqldump, mysqlpump, mydumper) support all engines, are single‑threaded or multi‑threaded, and can compress output. Physical tools ( XtraBackup, mariabackup) work only with InnoDB, provide near‑zero lock time, support incremental and parallel execution, and produce strong cross‑platform backups. Filesystem snapshots are fast but lack MySQL‑aware metadata.

11. Size‑Based Selection Guidance

< 10 GB : Use mysqldump or mysqlpump with daily binlog archiving.

10 GB – 200 GB : Weekly full logical dump or mydumper for faster full backups; keep binlog for PITR.

200 GB – 1 TB : Adopt XtraBackup full weekly, incremental daily, plus binlog.

1 TB – 10 TB : Parallel XtraBackup per database, split large schemas, use a binlog server for continuous archiving.

> 10 TB : Partition data, use multiple backup nodes, store backups in object storage, and orchestrate with a backup manager.

Strong SLA : Combine backups with delayed replica ( CHANGE MASTER TO MASTER_DELAY=N) and a binlog server for sub‑minute RPO.

12. Scheduling, Operations, and Monitoring

12.1 Backup Windows

Run backups during low‑traffic periods (02:00‑06:00), avoid peak hours and special events.

12.2 Scheduling Options

Use crontab, systemd timers, or Kubernetes CronJob. Example crontab entry for a weekly XtraBackup full backup:

# Every Sunday 03:00 full backup
0 3 * * 0 /opt/scripts/mysql_xb_full.sh >> /var/log/mysql_backup.log 2>&1

12.3 Resource Isolation

Store backup files on a dedicated disk separate from the MySQL data directory.

Throttle XtraBackup I/O with --throttle=500 if needed.

Use cgroups or container limits for CPU and memory.

12.4 Retention Policies

Keep local backups for 7 days, remote copies for 30 days, and cold archive for 90 days or longer. Example cleanup script:

find /backup/xb -mindepth 1 -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;

12.5 Metadata Management

After each backup, generate a metadata.json containing host, instance, backup type, LSN, binlog position, size, and timestamps. This aids verification and audit.

12.6 Verification and Drill‑Down

Run xtrabackup --prepare on a copy of the backup to ensure it is usable. Perform periodic restore drills to a test instance (e.g., port 3307) and validate table checksums.

12.7 RTO / RPO Alignment

Define RPO (maximum data loss) and RTO (maximum downtime) with business owners. Choose backup frequency and replication delay to meet those targets.

13. Common Failure Cases and Post‑Mortems

13.1 Missing --single-transaction Leads to Inconsistent Logical Dumps

When MyISAM tables are mixed with InnoDB, mysqldump without --single-transaction locks each table separately, allowing writes between tables and causing data mismatch after restore. Fix by always using --single-transaction and converting all tables to InnoDB.

13.2 XtraBackup Prepare Fails Due to Disk Space

The --prepare step needs 20‑30 % extra space for temporary files. Ensure the backup disk is sized at least 1.5 × the backup size or enable compression.

13.3 Killing a Backup Process Causes Long Rollback

Terminating a mysqldump that holds a large transaction with kill -9 triggers a lengthy rollback, impacting the primary. Use graceful termination ( kill -15) and monitor long‑running backup jobs.

13.4 Binlog Not Enabled Prevents PITR

If log_bin is OFF, point‑in‑time recovery is impossible. Verify SHOW VARIABLES LIKE 'log_bin'; is ON before production rollout.

13.5 Ransomware on Backup Machines

Backup servers should be on isolated networks, use least‑privilege IAM roles, and store copies in off‑site object storage to survive local compromise.

13.6 No Recovery Drills

Without regular restore drills, teams discover missing steps only during an outage. Schedule quarterly drills, document the runbook, and track results.

14. Backup and Replication Relationship

14.1 Replica ≠ Backup

A replica is a live copy of the primary; it does not protect against accidental DDL/DML, hardware failure, or corruption. Backups complement replicas.

14.2 Delayed Replica for Quick Rollback

Configure a replica with CHANGE MASTER TO MASTER_DELAY=3600 to keep a one‑hour lag. In case of a mistake, promote the delayed replica.

14.3 Binlog Server (Pseudo‑Replica)

Run mysqlbinlog --read-from-remote-server --raw --stop-never to continuously archive binlogs without affecting primary or replicas.

14.4 GTID‑Based Position Management

When restoring in GTID mode, set SET @@GLOBAL.GTID_PURGED='uuid:1-123456'; to tell the server that those transactions have already been applied.

15. Security and Compliance

15.1 Encryption

Encrypt backup files with OpenSSL, GPG, or cloud KMS. Example OpenSSL encryption:

openssl enc -aes-256-gcm -salt -pbkdf2 \
  -in /backup/full.sql -out /backup/full.sql.enc \
  -pass file:/root/.backup_pass

15.2 Access Controls

Set file permissions to 600 and restrict ownership to a dedicated backup user.

15.3 Retention and Secure Deletion

Define retention periods (e.g., 7 days local, 30 days remote, 365 days archive) and securely delete expired files with shredding or overwrite.

15.4 Personal Data Redaction

If backups contain personal data, apply masking at dump time (e.g., replace columns with NULL or hashed values) and audit access to backup files.

16. Monitoring and Alerting

16.1 Backup Job Metrics

Expose success/failure counters and duration histograms to Prometheus. Example alerts:

increase(mysql_backup_failure_total[1h]) > 0

16.2 Disk Usage

Alert when backup volume usage exceeds 85 %:

(node_filesystem_size_bytes{mountpoint="/backup"} - node_filesystem_avail_bytes{mountpoint="/backup"}) / node_filesystem_size_bytes{mountpoint="/backup"} > 0.85

16.3 Backup Duration

Warn if the 95th percentile of backup duration exceeds a threshold (e.g., 2 hours):

histogram_quantile(0.95, rate(mysql_backup_duration_seconds_bucket[1h])) > 7200

16.4 Restore Drills

Schedule weekly automated restore to a test instance and emit a metric mysql_backup_drill_success for alerting.

17. Small‑Scale Production Template

17.1 Assumptions

Single MySQL instance, 200 GB, InnoDB, primary‑replica with binlog.

RPO 5 min, RTO 30 min.

CentOS 7, 2 CPU, 8 GB RAM, dedicated backup disk.

17.2 Backup Strategy

Weekly full XtraBackup on Sunday 03:00.

Daily incremental XtraBackup Monday‑Saturday 03:00.

Hourly binlog archiving to object storage.

Local retention 7 days, remote retention 30 days.

17.3 Full Backup Script ( /opt/scripts/mysql_xb_full.sh )

#!/usr/bin/env bash
set -euo pipefail
export XTRABACKUP_PARALLEL=4
USER=backup_user
BASE=/backup/xb
TS=$(date +%Y%m%d_%H%M%S)
TARGET=${BASE}/full_${TS}
LOG=/var/log/xb_full_${TS}.log
mkdir -p "$BASE"
exec >"$LOG" 2>&1
xtrabackup --user="$USER" \
  --target-dir="$TARGET" \
  --backup \
  --parallel=$XTRABACKUP_PARALLEL \
  --compress \
  --compress-threads=2
# Verify
xtrabackup --prepare --target-dir="$TARGET" --use-memory=1G
# Upload to S3
aws s3 sync "$TARGET" "s3://my-bucket/mysql/xb/full_${TS}/"
# Cleanup older local backups
find "$BASE" -mindepth 1 -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;

17.4 Incremental Backup Script ( /opt/scripts/mysql_xb_inc.sh )

#!/usr/bin/env bash
set -euo pipefail
USER=backup_user
BASE=/backup/xb
TS=$(date +%Y%m%d_%H%M%S)
TARGET=${BASE}/inc_${TS}
LATEST_FULL=$(ls -td ${BASE}/full_* | head -1)
LOG=/var/log/xb_inc_${TS}.log
mkdir -p "$BASE"
exec >"$LOG" 2>&1
xtrabackup --user="$USER" \
  --target-dir="$TARGET" \
  --incremental-basedir="$LATEST_FULL" \
  --backup \
  --parallel=2 \
  --compress
aws s3 sync "$TARGET" "s3://my-bucket/mysql/xb/inc_${TS}/"
find "$BASE" -mindepth 1 -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;

17.5 Binlog Archiving Script ( /opt/scripts/mysql_binlog_archive.sh )

#!/usr/bin/env bash
set -euo pipefail
USER=backup_user
BINLOG_DIR=/backup/binlog
mkdir -p "$BINLOG_DIR"
# Flush logs to start a new binlog file
mysql -u"$USER" -e "FLUSH BINARY LOGS;"
LATEST=$(mysql -u"$USER" -e "SHOW BINARY LOGS;" | tail -1 | awk '{print $1}')
mysqlbinlog --read-from-remote-server \
  --host=127.0.0.1 --port=3306 --user="$USER" \
  --raw --stop-never \
  --result-file="$BINLOG_DIR/$LATEST"
# Upload to S3
aws s3 sync "$BINLOG_DIR" "s3://my-bucket/mysql/binlog/$(date +%Y%m%d)/"

17.6 Crontab Entries

# Sunday full backup
0 3 * * 0 /opt/scripts/mysql_xb_full.sh >> /var/log/mysql_backup.log 2>&1
# Monday‑Saturday incremental backup
0 3 * * 1-6 /opt/scripts/mysql_xb_inc.sh >> /var/log/mysql_backup.log 2>&1
# Hourly binlog archive
0 * * * * /opt/scripts/mysql_binlog_archive.sh >> /var/log/mysql_backup.log 2>&1

17.7 Verification and Drill Plan

Weekly automated restore to a test instance on port 3307 using the latest full backup.

Monthly full‑cycle restore (full + incremental + binlog) to validate RTO/RPO.

Document results in the SRE runbook.

18. Conclusion

18.1 One‑Sentence Selection Mnemonic

Small, simple DB → mysqldump + binlog.

Medium, write‑heavy → mydumper + binlog.

Large InnoDB → XtraBackup + binlog.

Very large → partitioned XtraBackup + binlog server.

Strong SLA → above stack + delayed replica.

18.2 The Real Test Is the Drill

A backup plan that has never been restored is only a theoretical exercise. Regular, documented restore drills are the only way to guarantee that RPO and RTO targets are met.

18.3 Future Directions

MySQL 8.0’s LOCK INSTANCE FOR BACKUP (requires BACKUP_ADMIN) provides lock‑free consistent backups.

The CLONE plugin enables fast instance cloning for backup or scaling.

Cloud‑native backup services that integrate XtraBackup with object storage are emerging.

Appendix A – Common Commands

# mysqldump full logical backup
mysqldump -uroot -p --single-transaction --routines --triggers \
  --events --master-data=2 --set-gtid-purged=OFF --all-databases > full.sql

# mysqlpump parallel dump
mysqlpump -uroot -p --parallel-schemas=4 --all-databases > full.sql

# mydumper multi‑threaded dump
mydumper --threads=8 --rows=200000 --compress --outputdir=/backup/20260608

# myloader restore
myloader --threads=8 --directory=/backup/20260608 --overwrite-tables

# XtraBackup full
xtrabackup --user=root --target-dir=/backup/full --backup

# XtraBackup incremental
xtrabackup --user=root --target-dir=/backup/inc \
  --incremental-basedir=/backup/full --backup

# XtraBackup prepare
xtrabackup --prepare --target-dir=/backup/full

# XtraBackup copy‑back
xtrabackup --copy-back --target-dir=/backup/full

# Binlog archive
mysqlbinlog --read-from-remote-server --host=127.0.0.1 --user=backup \
  --raw --stop-never --result-file=/backup/binlog binlog.000001

# Binlog PITR
mysqlbinlog --start-position=456789 --stop-datetime="2026-06-09 10:00:00" \
  /backup/binlog/binlog.000123 | mysql -uroot -p

Appendix B – Version Differences

MySQL 5.7: log_bin OFF by default; mysqlpump introduced.

MySQL 8.0: log_bin ON, GTID ON, LOCK INSTANCE FOR BACKUP and CLONE available.

XtraBackup 2.4 works with MySQL 5.5‑5.7; XtraBackup 8.0 works with MySQL 8.0 and is not compatible with 2.4 backups.

MariaDB Backup only for MariaDB, not MySQL.

Appendix C – References

MySQL Official Documentation – mysqldump, mysqlpump, mysqlbinlog.

Percona XtraBackup Documentation.

MariaDB Backup Documentation.

Cloud provider snapshot guides (AWS, Azure, GCP, Alibaba Cloud).

mydumper GitHub repository: https://github.com/mydumper/mydumper.

Backup is not a silver bullet. A production‑ready solution must be sized, tested, and continuously verified against business RPO/RTO requirements.