MySQL Backup

Use logical dumps when portability matters, physical hot backups when restore speed matters, and binlogs when the business expects point-in-time recovery instead of "some time today".

Logical backups with mysqldump

mysqldump gives you portable SQL or logical export data. It is useful for application databases, schema review, and cross-version restore paths. For multi-terabyte servers, it is often too slow for primary recovery but still worth keeping for object-level restore.

# One application database
mysqldump \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --set-gtid-purged=OFF \
  appdb | gzip > /srv/backups/mysql/appdb-$(date +%F_%H%M).sql.gz

# Whole instance
mysqldump \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --set-gtid-purged=OFF \
  | gzip > /srv/backups/mysql/full-$(date +%F_%H%M).sql.gz

Physical backups with mariabackup or XtraBackup

For hot physical backups, use the tool that matches your server family: mariabackup for MariaDB and XtraBackup for MySQL or Percona Server. These copy InnoDB data files while the server is running and are much faster to restore than logical dumps.

CREATE USER 'backup'@'10.20.%' IDENTIFIED BY 'redacted';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'10.20.%';

# MariaDB
mariabackup \
  --backup \
  --target-dir=/srv/backups/mariadb/full-$(date +%F_%H%M) \
  --user=backup \
  --password-file=/etc/backup/mysql.pass

mariabackup --prepare --target-dir=/srv/backups/mariadb/full-2026-04-23_0100

# MySQL / Percona Server
xtrabackup \
  --backup \
  --target-dir=/srv/backups/mysql/base-$(date +%F_%H%M) \
  --user=backup \
  --password-file=/etc/backup/mysql.pass \
  --parallel=4

xtrabackup --prepare --target-dir=/srv/backups/mysql/base-2026-04-23_0100

--prepare is not optional. It replays redo and makes the physical copy restorable. "Backup completed OK" is not the same thing as "backup is prepared for restore".

Binary logs

Binlogs are the incremental history between full backups. If you want PITR, you need binlogs retained long enough to cover the gap from your last full backup to the incident.

# /etc/my.cnf.d/backup.cnf
[mysqld]
server_id = 101
log_bin = /var/lib/mysql/binlog
binlog_format = ROW
binlog_row_image = MINIMAL
binlog_expire_logs_seconds = 604800
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

SHOW MASTER STATUS;
SHOW BINARY LOGS;

Do not let automatic binlog expiration undercut your recovery window. If full backups run daily but your restore path depends on two weeks of history, seven days of binlogs is not enough.

Point-in-time recovery

Point-in-time recovery is usually: restore the latest full backup, then replay binlogs until the second before bad data was written. Exact steps depend on whether you restored from a dump or a physical backup, but the principle is the same.

# 1. Restore the base backup or import the logical dump
gunzip -c /srv/backups/mysql/full-2026-04-23_0100.sql.gz | mysql

# 2. Replay binlogs up to the stop time
mysqlbinlog \
  --start-datetime="2026-04-23 01:00:00" \
  --stop-datetime="2026-04-23 13:17:42" \
  /srv/backups/mysql/binlog.000123 \
  /srv/backups/mysql/binlog.000124 | mysql

If GTID is enabled and you are replaying into a replica or a server with existing executed GTIDs, be explicit about which transactions should be skipped or applied. Blind replay is how operators duplicate changes.

Compression and encryption

Compress logical dumps to reduce transfer time. Encrypt anything leaving the host or crossing trust boundaries. Keep decryption material in the same DR plan as the data itself.

# Compressed logical dump
mysqldump --single-transaction appdb | gzip > /srv/backups/mysql/appdb.sql.gz

# Encrypted physical backup with XtraBackup
xtrabackup \
  --backup \
  --target-dir=/srv/backups/mysql/base-$(date +%F_%H%M) \
  --user=backup \
  --password-file=/etc/backup/mysql.pass \
  --compress \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/backup/xtrabackup.key

Version your keys and document rotation. An unreadable encrypted backup is operationally identical to no backup at all.

Restore drills and validation

Run restore drills on an isolated host. Measure time to usable service, not just time to copy files. Validate the application path that matters: schema objects, row counts, grants, and a few business-critical queries.

systemctl stop mysqld
rm -rf /var/lib/mysql/*

# Use the tool that created the backup
xtrabackup --copy-back --target-dir=/srv/backups/mysql/base-2026-04-23_0100
# or
mariabackup --copy-back --target-dir=/srv/backups/mariadb/full-2026-04-23_0100

chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

mysqlcheck --all-databases
mysql -e "SELECT COUNT(*) FROM appdb.orders;"

• Restore on the same major version when possible. Cross-version restores add variables you do not want during an outage.
• Validate users, grants, routines, events, and triggers, not just tables.
• Record the restore duration and compare it against the declared RTO from Backup & Restore.

Capture the exact steps in DR Runbook Template so an operator can follow them under pressure.

Monitoring

Track last successful full backup time, presence of recent binlogs, backup repository size, and age of the last successful restore drill.

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

stamp=$(stat -c %Y /srv/backups/mysql/latest.ok)
printf 'mysql_backup_last_success_unixtime{cluster="main"} %s\n' "$stamp" \
  > /var/lib/node_exporter/textfile_collector/mysql_backup.prom

mysql -NBe "SHOW BINARY LOGS;" > /var/lib/node_exporter/textfile_collector/mysql_binlogs.txt

groups:
  - name: mysql-backup
    rules:
      - alert: MySQLBackupStale
        expr: time() - mysql_backup_last_success_unixtime{cluster="main"} > 86400
        for: 10m
        labels:
          severity: page
        annotations:
          summary: "MySQL full backup is older than one day"

Graph these together with replication lag and disk usage from MySQL Ops. Binlogs filling the disk or expiring too early are both backup incidents.

Troubleshooting and common restore failures

See also: Backup & Restore, MySQL Ops, and DR Runbook Template.