Essential MySQL Backup and Recovery Process Every Ops Engineer Must Master

Background

Database backup is the last line of defense for operations. Hardware failures, software bugs, or human errors can cause irreversible data loss. A 2024 incident at a major cloud provider highlighted the risks of missing backups.

Backup Importance and RPO/RTO

Before designing a backup plan, define Recovery Point Objective (RPO) and Recovery Time Objective (RTO). For example, an RPO of 1 hour means no more than one hour of data loss is acceptable; an RTO of 4 hours means the system must be restored within four hours.

Core transaction system – RPO < 5 min, RTO < 30 min

User data (accounts, orders) – RPO < 1 hour, RTO < 4 hours

Log data – RPO < 24 hours, RTO < 24 hours

Archived historical data – RPO < 1 week, RTO < 1 week

Backup Strategy Decision Tree

Business tolerance for data loss?
├─ Extremely low (RPO <5 min) → xtrabackup incremental (5‑15 min) + binlog
├─ Low (RPO ≤ 1 hour) → xtrabackup incremental (hourly) + daily full
├─ Medium (RPO 1‑24 h) → daily mysqldump full + binlog archive
└─ Loose (RPO > 24 h) → daily mysqldump full

Backup Method Comparison

Logical vs Physical

Logical backup (mysqldump, mydumper) exports SQL statements and table structures.

Advantages: cross‑version compatibility, readable files, flexible restoration.

Disadvantages: slower backup/restore, cannot guarantee consistency during live writes.

Physical backup (xtrabackup, mysqlbackup) copies raw data files.

Advantages: fast backup/restore, maintains consistency via FTWRL or internal mechanisms.

Disadvantages: larger files, same‑version requirement, harder cross‑platform use.

Tool Comparison

mysqldump – Type: logical – Backup speed: slow – Restore speed: slow – Consistency: requires --single-transaction – Incremental: no – Recommended for: <10 GB, small workloads

mydumper – Type: logical – Backup speed: medium – Restore speed: medium – Consistency: supports transactions – Incremental: no – Recommended for: medium‑large databases

xtrabackup – Type: physical – Backup speed: fast – Restore speed: fast – Consistency: automatic – Incremental: yes – Recommended for: large, mission‑critical databases

mysqlbackup – Type: physical – Backup speed: fast – Restore speed: fast – Consistency: supported – Incremental: yes – Recommended for: MySQL Enterprise Edition

mysqldump Details

Basic usage:

mysqldump -u root -p -h localhost mydb > /backup/mydb_$(date +%Y%m%d).sql

Key options for a consistent backup: --single-transaction: creates a snapshot for InnoDB tables. --routines, --triggers, --events: include stored procedures, triggers, and events. --master-data=2: records binlog position for point‑in‑time recovery. --flush-logs: rotates binlogs before backup.

Incorrect usage (e.g., omitting --single-transaction) can produce inconsistent dumps, especially for MyISAM tables.

xtrabackup Details

Installation (Percona repository):

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-80

Full backup script (simplified):

#!/bin/bash
BACKUP_DIR="/backup/xtrabackup"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="BackupPass2026!"
BACKUP_PATH="$BACKUP_DIR/full/full_$(date +%Y%m%d_%H%M%S)"

xtrabackup --user=$MYSQL_USER --password=$MYSQL_PASSWORD \
    --backup --target-dir=$BACKUP_PATH --datadir=/var/lib/mysql

xtrabackup --prepare --target-dir=$BACKUP_PATH

Incremental backup uses --incremental-basedir pointing to the latest full backup.

mydumper Details

Installation via package manager or source:

apt-get install -y mydumper   # Debian/Ubuntu
yum install -y mydumper       # CentOS/RHEL
# From source
git clone https://github.com/mydumper/mydumper.git
cmake . && make -j$(nproc) && make install

Parallel dump example:

mydumper -u root -p 'MyPassword2026!' -h localhost -B mydb \
    -o /backup/mydb_$(date +%Y%m%d) -t 8 -v 3

Restore with myloader using matching options.

Incremental & Binlog Backup

MySQL binlog records every data change, enabling point‑in‑time recovery (PITR). A simple binlog backup script copies /var/lib/mysql/mysql-bin.* files, compresses older logs, and retains them for a configurable number of days.

# binlog_backup.sh
MYSQL_USER="backup_admin"
MYSQL_PASSWORD="SecureBackupPass2026!"
BACKUP_DIR="/backup/binlog"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"
CURRENT_BINLOG=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -S /var/lib/mysql/mysql.sock -N -e "SHOW MASTER STATUS;" | awk '{print $1}')
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -S /var/lib/mysql/mysql.sock -e "FLUSH BINARY LOGS;"
cp -n /var/lib/mysql/mysql-bin.* $BACKUP_DIR/
find $BACKUP_DIR -name "mysql-bin.*" ! -name "*.gz" -mtime +1 -exec gzip {} \;
find $BACKUP_DIR -name "*.gz" -mtime +$RETENTION_DAYS -delete

Backup Encryption & Compression

GPG Symmetric Encryption

# encrypt_backup_gpg.sh
PASS_FILE=$(mktemp)
openssl rand -base64 32 > $PASS_FILE

gpg --batch --yes --symmetric --passphrase-file $PASS_FILE \
    --cipher-algo AES256 --output ${BACKUP_FILE}.gpg $BACKUP_FILE
# Encrypt the password with recipient's public key
gpg --batch --yes --encrypt --recipient [email protected] \
    --output ${PASS_FILE}.gpg $PASS_FILE
rm -f $PASS_FILE

OpenSSL AES‑256‑CBC Encryption

# encrypt_backup_openssl.sh
PASSWORD=$(openssl rand -base64 32)
openssl enc -aes-256-cbc -salt -pbkdf2 -in $BACKUP_FILE -out ${BACKUP_FILE}.enc -pass pass:$PASSWORD
# Store password in file header (for demo only)
echo $PASSWORD | head -c 64 > ${BACKUP_FILE}.enc.key
cat ${BACKUP_FILE}.enc.key ${BACKUP_FILE}.enc > ${BACKUP_FILE}.combined
rm -f ${BACKUP_FILE}.enc.key ${BACKUP_FILE}.enc

Recovery Procedures & Drills

mysqldump Restore

# restore_mysqldump.sh
if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip -c $BACKUP_FILE > /tmp/decompressed.sql
    BACKUP_FILE=/tmp/decompressed.sql
fi
mysql -u root -p'RootPassword2026!' -h localhost $TARGET_DB < $BACKUP_FILE

xtrabackup Full Restore

# restore_xtrabackup_full.sh
systemctl stop mysql || service mysql stop
xtrabackup --prepare --target-dir=$BACKUP_DIR
xtrabackup --copy-back --target-dir=$BACKUP_DIR --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chmod -R 750 /var/lib/mysql
systemctl start mysql || service mysql start

xtrabackup Incremental Restore

# restore_xtrabackup_incremental.sh
xtrabackup --prepare --target-dir=$FULL_BACKUP
for inc in ${INC_BACKUPS//,/ }; do
    xtrabackup --prepare --target-dir=$FULL_BACKUP --incremental-dir=$inc
 done
systemctl stop mysql
xtrabackup --copy-back --target-dir=$FULL_BACKUP --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Backup Drill Script

A monthly drill creates an isolated MySQL instance on a non‑standard port, restores the latest mysqldump or xtrabackup backup, validates table counts, and generates a short report.

# backup_drill.sh
# (initialization, restore, validation, cleanup omitted for brevity)

Common Issues & Troubleshooting

mysqldump Problems

Large backup files → use streaming compression ( mysqldump … | gzip > backup.sql.gz) or split per‑database.

Access denied (error 1045) → verify user privileges; use --defaults-extra-file for special characters.

Inconsistent dumps → always use --single-transaction on InnoDB.

xtrabackup Problems

Permission or SELinux blocks → ensure /var/lib/mysql owned by mysql and SELinux is permissive or configured.

Version mismatch → run xtrabackup --prepare on the same MySQL version that created the backup.

Diagnostic Script

# backup_troubleshoot.sh
# Checks mysqldump, xtrabackup, disk space, backup integrity, and binlog status.

Backup Management Best Practices

Checklist Script

# backup_checklist.sh
# Verifies latest full/incremental backups, size, binlog count, retention policy, and remote sync status.

Configuration Template (YAML)

backup:
  type: "xtrabackup"
  schedule:
    full: "0 2 * * 0"   # weekly full backup
    incr: "0 2 * * 1-6" # daily incremental
    binlog: "*/15 * * * *"
  retention:
    full: 30
    incr: 7
    binlog: 7
  compression:
    enabled: true
    algorithm: "gzip"
    level: 6
  encryption:
    enabled: true
    method: "openssl"
    key_store: "/etc/backup/aes_keyfile"
  remote_sync:
    enabled: true
    method: "rsync"
    target: "backup-server:/backup/mysql"
  verify:
    enabled: true
    method: "checksum"
    restore_test_interval: "monthly"
  alert:
    enabled: true
    on_failure: true
    channels: ["email", "wechat"]

Prometheus Alert Rules

# prometheus_backup_alerts.yml
groups:
- name: MySQL备份告警规则
  rules:
  - alert: MySQLBackupMissing
    expr: (time() - file_exists("/backup/mysql/$(date +%Y-%m-%d)/metadata.txt")) > 86400
    for: 1h
    labels:
      severity: critical
    annotations:
      summary: "MySQL 全量备份缺失"
      description: "超过24小时未执行全量备份"
  - alert: MySQLBackupFailing
    expr: increase(mysql_backup_errors_total[1h]) > 0
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 备份失败"
      description: "备份任务在过去1小时内发生错误"
  - alert: MySQLBackupTooLarge
    expr: (mysql_backup_size_bytes / mysql_backup_size_bytes offset 1d) > 1.5
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 备份文件异常增长"
      description: "备份文件大小比昨天增长超过50%"
  - alert: MySQLBinlogMissing
    expr: (time() - file_modified("/var/lib/mysql/mysql-bin.index")) > 3600
    for: 30m
    labels:
      severity: warning
    annotations:
      summary: "MySQL binlog 未更新"
      description: "binlog 文件超过1小时未更新，可能存在写入问题"

Conclusion

MySQL backup and recovery is a core competency for any operations engineer. A robust backup system must be:

Reliable : integrity‑checked, regular restore drills.

Timely : meet defined RPO/RTO.

Secure : encrypted at rest and in transit.

Recoverable : documented, scripted procedures.

Observable : monitoring and alerts for failures or anomalies.

Many teams focus on taking backups but neglect verification; when a real outage occurs, corrupted files or broken scripts cause costly downtime. Conduct monthly restore drills, measure actual RTO, and treat backup health as a first‑class KPI.