Backup and Recovery: mysqldump / xtrabackup with Point‑In‑Time Recovery

Overview

The article presents a complete workflow for MySQL backup and restoration, targeting the three goals of being able to back up, restore, and recover to any arbitrary point in time. It is written for junior to intermediate operators, DBAs and DevOps engineers.

Typical Failure Scenarios

Full logical dump taken at night, then a DROP TABLE is executed at 15:00, losing a whole day of data.

Physical backup with xtrabackup but the binlog_pos field is missing, making point‑in‑time recovery impossible.

Binlog retention set to the default 30 days, causing automatic purge of needed logs.

Backup performed only on the primary server in a master‑slave setup, so a mistake replicates to the replica.

Backup files stored on the same disk as the database and lost when the disk fails.

Key Requirements

Choose a backup method that matches data size and RPO/RTO requirements.

Enable binary logging and keep it long enough for the desired recovery window.

Validate backups regularly (restore‑test, checksum, binlog start line).

Treat restoration as a high‑risk operation: use a separate instance, stop writes, and verify before cutting over.

Backup Methods

Logical Backup – mysqldump

Best for databases smaller than 50 GB, when you need cross‑version compatibility or want a human‑readable SQL file.

mysqldump \
  --host=127.0.0.1 \
  --port=3306 \
  --user=root \
  --password \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --set-gtid-purged=AUTO \
  --default-character-set=utf8mb4 \
  --hex-blob --quick --all-databases \
  > /data/backup/mysql/full_$(date +%F).sql

Important flags: --single-transaction creates a consistent snapshot without locking tables (InnoDB only). --master-data=2 (or --source-data=2 on 8.0) writes the binlog position as a comment – the PITR start point. --set-gtid-purged=AUTO handles GTID correctly; use --set-gtid-purged=OFF when restoring to a server that already has GTID history. --hex-blob prevents binary data corruption.

Physical Backup – xtrabackup

Suitable for large data sets (TB‑scale) and fast restores. The version of XtraBackup must match the MySQL major version (e.g., percona‑xtrabackup‑80 for MySQL 8.0).

# Full backup
xtrabackup --backup \
  --user=backup_user --password='YOUR_PASSWORD' \
  --host=127.0.0.1 --port=3306 \
  --target-dir=/data/backup/mysql/xtrabackup/full_$(date +%F) \
  --parallel=4 --compress --compress-threads=4

Key files generated: xtrabackup_info – contains binlog_file, binlog_pos, gtid (the PITR start point). xtrabackup_checkpoints – records LSN values for incremental backups. xtrabackup_logfile – redo logs that must be applied with --prepare.

Prepare the backup (apply redo logs) before restoring:

xtrabackup --prepare --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22

Restore to a fresh data directory (do not overwrite a running instance):

systemctl stop mysqld
mv /data/mysql/data /data/mysql/data.broken_$(date +%F_%H%M)
mkdir -p /data/mysql/data
xtrabackup --copy-back --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22 --datadir=/data/mysql/data
chown -R mysql:mysql /data/mysql/data
systemctl start mysqld

Incremental Backup with XtraBackup

After a full backup, create incremental backups based on the previous backup’s LSN:

# First incremental
xtrabackup --backup \
  --user=backup_user --password='YOUR_PASSWORD' \
  --target-dir=/data/backup/mysql/xtrabackup/inc_2026-06-22_10 \
  --incremental-basedir=/data/backup/mysql/xtrabackup/full_2026-06-22 \
  --parallel=4

# Second incremental (based on the first)
xtrabackup --backup \
  --user=backup_user --password='YOUR_PASSWORD' \
  --target-dir=/data/backup/mysql/xtrabackup/inc_2026-06-22_11 \
  --incremental-basedir=/data/backup/mysql/xtrabackup/inc_2026-06-22_10 \
  --parallel=4

Merge them before restore:

# Apply base backup (no rollback)
xtrabackup --prepare --apply-log-only \
  --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22

# Apply first incremental
xtrabackup --prepare --apply-log-only \
  --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22 \
  --incremental-dir=/data/backup/mysql/xtrabackup/inc_2026-06-22_10

# Apply second incremental
xtrabackup --prepare --apply-log-only \
  --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22 \
  --incremental-dir=/data/backup/mysql/xtrabackup/inc_2026-06-22_11

# Final prepare (roll back uncommitted transactions)
xtrabackup --prepare \
  --target-dir=/data/backup/mysql/xtrabackup/full_2026-06-22

Binary Log (Binlog) and Point‑In‑Time Recovery

Enable binlog with row format for reliable PITR:

[mysqld]
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1          # 1 = safest, higher values improve performance
expire_logs_days = 7    # increase to 30 for longer RPO

Find the start position from the mysqldump file:

grep -m1 "CHANGE MASTER TO" /data/backup/mysql/full_2023-09-01.sql
# Example output:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;

Extract binlog events up to the moment before the failure:

mysqlbinlog --start-position=456789 \
  --stop-position=460000 \
  /data/mysql/binlog/mysql-bin.000123 \
  > /tmp/replay.sql
mysql -u root -p < /tmp/replay.sql

When GTID is enabled, you can exclude the offending transaction instead of using positions:

mysqlbinlog --skip-gtids \
  --exclude-gtids='3a3a3a3a-aaaa-bbbb-cccc-1234567890ab:990-995' \
  /data/mysql/binlog/mysql-bin.000123 \
  > /tmp/replay.sql

Automation Script Example

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/data/backup/mysql"
REMOTE_DIR="backup@nas01:/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="backup_user"
# password is read from /root/.my.cnf.backup (600 permissions)
MYSQL_DEFAULTS="/root/.my.cnf.backup"
DATE=$(date +%F_%H%M)

DUMP_FILE="$BACKUP_DIR/full_$DATE.sql"
GZ_FILE="$DUMP_FILE.gz"

log(){ printf "%s %s
" "$(date '+%F %T')" "$*" | tee -a "$LOG_FILE"; }

log "===== Starting full logical backup $DATE ====="
mysqldump --defaults-file="$MYSQL_DEFAULTS" \
  --host=$MYSQL_HOST --port=$MYSQL_PORT \
  --single-transaction --source-data=2 \
  --routines --triggers --events \
  --set-gtid-purged=AUTO \
  --default-character-set=utf8mb4 --hex-blob --quick \
  --all-databases > "$DUMP_FILE" 2>>"$LOG_FILE"

if [[ ! -s "$DUMP_FILE" ]]; then
  log "ERROR: Backup file empty, aborting"
  exit 1
fi

POS_LINE=$(grep -m1 "CHANGE MASTER TO\|CHANGE REPLICATION SOURCE TO" "$DUMP_FILE" || true)
if [[ -z "$POS_LINE" ]]; then
  log "WARN: No binlog position found in dump – PITR start missing"
else
  log "Binlog start point: $POS_LINE"
fi

gzip "$DUMP_FILE"
log "Compressed backup: $GZ_FILE (size $(du -h "$GZ_FILE" | cut -f1))"

gzip -t "$GZ_FILE" || { log "ERROR: Corrupt gzip file"; exit 1; }

if command -v rsync >/dev/null; then
  rsync -az --timeout=600 "$GZ_FILE" "$REMOTE_DIR/" 2>>"$LOG_FILE" && log "Remote copy succeeded" || log "WARN: Remote copy failed"
fi

find "$BACKUP_DIR" -maxdepth 1 -name "full_*.gz" -mtime +7 -type f -print -exec rm -f {} \; 2>>"$LOG_FILE"
log "===== Backup finished $DATE ====="

Verification and Monitoring

Check that the dump is non‑empty and contains a CHANGE MASTER TO line.

Validate gzip integrity with gzip -t.

Periodically run SHOW BINARY LOGS and monitor mysql_binlog_files, mysql_binlog_size_bytes, and mysql_slave_status_seconds_behind_master metrics.

Alert on backup failures, excessive backup duration, binlog directory usage > 80 %, or replica lag > 300 s.

Risk & Recovery Checklist

Never run DROP TABLE on production without a recent full backup.

Keep binlog retention longer than the maximum RPO (recommended ≥ 30 days).

Store backups off‑site (local + NAS + object storage) with encrypted transfer.

Grant the backup user only read‑only privileges plus the minimal set required for SHOW MASTER STATUS and REPLICATION CLIENT.

Test restores on a separate instance before cutting over.

For accidental DDL, restore a fresh instance from the latest full backup, replay binlog up to the point before the DDL, verify data, then switch traffic.

Conclusion

By combining regular logical or physical backups with continuous binary‑log archiving, a MySQL environment can achieve near‑zero data loss and meet strict RPO/RTO requirements. The provided scripts, configuration snippets, and step‑by‑step recovery procedures give operators a reliable, repeatable process for both routine maintenance and emergency disaster recovery.