How to Handle Backups and Recovery in MySQL

There’s a specific kind of panic that comes from someone messaging you “I think I just dropped the wrong table in prod” — and the only thing that turns that panic into a five-minute fix instead of a career-defining disaster is whether your backup and recovery strategy was actually sound before that moment. I’ve been on both sides of that message, and this article is everything I’ve learned about doing MySQL backups and recovery properly, not just theoretically.

Why Backup Strategy Has to Match Architecture

Before picking tools, I always think about what I’m actually protecting against, because different failure modes need different strategies:

• Human error (dropped table, bad UPDATE without a WHERE clause) — needs point-in-time recovery, not just periodic snapshots.
• Hardware failure (disk failure, host loss) — needs replicas and/or frequent physical backups.
• Data corruption — needs backups retained long enough to predate when corruption was introduced, plus checksumming.
• Regional disaster — needs backups stored off-site/cross-region.
• Ransomware/malicious deletion — needs immutable, access-isolated backup copies.

MySQL Backup Types: The Fundamentals

flowchart TB
    A[MySQL Backup Strategies] --> B[Logical Backups]
    A --> C[Physical Backups]
    B --> B1[mysqldump]
    B --> B2[mysqlpump]
    B --> B3[MySQL Shell Dump Utilities]
    C --> C1[Percona XtraBackup - Hot Physical Backup]
    C --> C2[Filesystem/Volume Snapshots]
    A --> D[Binary Log Backups for Point-in-Time Recovery]

Logical backups export data as SQL statements (or another portable format). They’re human-readable, portable across MySQL versions and even other databases to some degree, but slower to restore for large datasets since every row has to be re-inserted and every index rebuilt.

Physical backups copy the actual data files (InnoDB tablespaces, etc.) directly. They’re much faster to restore for large databases since there’s no re-insertion or index rebuilding involved, but they’re tied to the same MySQL version/architecture and aren’t human-readable.

Binary logs aren’t a backup by themselves, but they’re essential for point-in-time recovery (PITR) — replaying every transaction that happened after your last full backup.

Logical Backups with mysqldump

This is still my go-to for smaller databases (roughly under 50–100GB, though that threshold depends on your restore time requirements) or when I need portability.

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=ON \
  --master-data=2 \
  -u backup_user -p \
  --databases ecommerce_db > ecommerce_db_backup_$(date +%F).sql

Key flags I always use:

• --single-transaction — takes a consistent snapshot of InnoDB tables using MVCC, without locking the whole database (crucial for a live production system).
• --routines --triggers --events — otherwise stored procedures, triggers, and scheduled events are silently excluded, which has bitten teams I’ve worked with before.
• --set-gtid-purged=ON — captures the GTID state, which I need if this backup will ever seed a new replica.

I compress and store it immediately:

gzip ecommerce_db_backup_$(date +%F).sql
aws s3 cp ecommerce_db_backup_$(date +%F).sql.gz s3://company-db-backups/ecommerce_db/

Restoring a logical backup:

gunzip < ecommerce_db_backup_2026-07-30.sql.gz | mysql -u root -p

Physical Backups with Percona XtraBackup

For larger production databases, I switch to Percona XtraBackup, which performs a hot physical backup of InnoDB data files without locking tables for the bulk of the operation — critical for databases where I can’t afford downtime.

xtrabackup --backup \
  --target-dir=/backups/full_$(date +%F) \
  --user=backup_user --password='BackupP@ss1'

Prepare the backup (applies the redo log to make it consistent, since files were copied while writes were still happening):

xtrabackup --prepare --target-dir=/backups/full_2026-07-30

Restore onto a stopped MySQL instance:

systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backups/full_2026-07-30
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

I also use XtraBackup’s incremental backup support to reduce backup window and storage costs on large databases:

# Full backup (base)
xtrabackup --backup --target-dir=/backups/base --user=backup_user --password='BackupP@ss1'

# Incremental backup, capturing only changes since the base
xtrabackup --backup --target-dir=/backups/inc1 \
  --incremental-basedir=/backups/base \
  --user=backup_user --password='BackupP@ss1'

flowchart LR
    Full[Sunday: Full Backup] --> Inc1[Monday: Incremental]
    Inc1 --> Inc2[Tuesday: Incremental]
    Inc2 --> Inc3[Wednesday: Incremental]
    Inc3 --> Restore[Restore = Full + Inc1 + Inc2 + Inc3, replayed in order]

Point-in-Time Recovery (PITR) with Binary Logs

This is the piece that turns “I have last night’s backup” into “I can restore to exactly 3:47:12pm, one second before the bad DELETE ran.” I always ensure binary logging is enabled for this to be possible:

[mysqld]
log_bin=mysql-bin
binlog_format=ROW
binlog_expire_logs_seconds=604800

Recovery process I follow:

1. Restore the most recent full backup (logical or physical).
2. Identify the exact binlog position or timestamp right before the incident.

SHOW BINLOG EVENTS IN 'mysql-bin.000045' FROM 4 LIMIT 20;

3. Replay binlog events from the backup’s position up to (but not including) the damaging statement:

mysqlbinlog \
  --start-position=4 \
  --stop-datetime="2026-07-30 15:47:12" \
  mysql-bin.000045 mysql-bin.000046 | mysql -u root -p

Or excluding a specific known-bad statement by position:

mysqlbinlog \
  --start-position=4 \
  --stop-position=88234512 \
  mysql-bin.000045 | mysql -u root -p

mysqlbinlog \
  --start-position=88235102 \
  mysql-bin.000045 mysql-bin.000046 | mysql -u root -p

(Here I’ve skipped the byte range 88234512–88235102, which is where the accidental DROP TABLE or bad UPDATE lived.)

Backup Scheduling Strategy

My typical production schedule:

flowchart TB
    subgraph Daily Cycle
    A[00:00 Full Backup] --> B[06:00 Incremental]
    B --> C[12:00 Incremental]
    C --> D[18:00 Incremental]
    end
    E[Continuous Binlog Archiving] -.enables PITR at any point.-> A

Automating Backups

I never rely on manually running backup commands. A cron-scheduled script I’d actually use:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/backups/full_$(date +%F_%H%M)"
S3_BUCKET="s3://company-db-backups/prod/"

xtrabackup --backup --target-dir="$BACKUP_DIR" \
  --user=backup_user --password="$BACKUP_PASSWORD" \
  --compress --compress-threads=4

xtrabackup --prepare --target-dir="$BACKUP_DIR"

tar -czf "${BACKUP_DIR}.tar.gz" "$BACKUP_DIR"
aws s3 cp "${BACKUP_DIR}.tar.gz" "$S3_BUCKET"

# Verify backup integrity before declaring success
xtrabackup --decompress --target-dir="$BACKUP_DIR"
if [ $? -ne 0 ]; then
  echo "Backup verification failed!" | mail -s "MySQL Backup FAILED" dba-team@company.com
  exit 1
fi

# Cleanup local backups older than 14 days
find /backups -maxdepth 1 -type d -mtime +14 -exec rm -rf {} \;

I always include a verification step. A backup that hasn’t been tested for restorability isn’t a backup — it’s a hope.

Testing Recovery — The Step Everyone Skips

I run a scheduled recovery drill — not just a backup integrity check, but an actual full restore into an isolated environment, at least monthly, and after any major schema or infrastructure change. The drill checklist I use:

1. Provision a clean, isolated MySQL instance (never restore-test against anything shared).
2. Restore the latest full backup + apply available incrementals.
3. Apply binlogs to reach a specific target timestamp.
4. Run data integrity checks (row counts, checksums on key tables) against expected values.
5. Time the entire process and record it — this becomes my actual, evidence-based RTO (Recovery Time Objective), not a guess.
6. Document any gaps found and fix them before the next drill.

-- Simple integrity spot-check after restore
CHECKSUM TABLE orders, customers, payments;
SELECT COUNT(*) FROM orders WHERE created_at > '2026-07-29 00:00:00';

Recovery Scenarios and How I Handle Them

For innodb_force_recovery, I treat it strictly as a data-extraction tool:

[mysqld]
innodb_force_recovery=4

I start at the lowest level (1) and increase cautiously only if needed, extract what data I can with mysqldump, then fully rebuild the instance from a clean backup — I never leave a server running long-term with force recovery enabled.

Security Considerations for Backups

• I encrypt backups at rest (xtrabackup supports --encrypt) and in transit to remote storage.
• I store backups in a separate account/region from production, with strict IAM policies, ideally with object-lock/immutability enabled (e.g., S3 Object Lock) as protection against ransomware or malicious deletion.
• I use a dedicated backup_user account with only BACKUP_ADMIN, SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, and PROCESS privileges — never a full admin account for scheduled backup jobs.

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupP@ss1';
GRANT BACKUP_ADMIN, SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT
  ON *.* TO 'backup_user'@'localhost';

• I periodically audit who has access to the backup storage location — backups often contain the entire dataset including sensitive fields, so they need the same access controls as production itself.

Troubleshooting Common Backup/Recovery Issues

Best Practices I Follow

• Combine physical backups (for speed) with continuous binlog archiving (for point-in-time precision) — I don’t rely on one alone.
• Automate backups completely; never depend on someone remembering to run a script.
• Store backups off-site/cross-region, encrypted, with immutability where the storage layer supports it.
• Actually test restores on a schedule — an untested backup is a liability disguised as a safety net.
• Track and document real, measured RTO and RPO (Recovery Point Objective) from drills, not assumptions.
• Scope backup account privileges tightly and rotate credentials regularly.

Interview Questions

1. What’s the difference between a logical and a physical MySQL backup, and when would you choose each?
2. How does --single-transaction in mysqldump avoid locking a live InnoDB database?
3. Walk through how you’d perform point-in-time recovery to a moment 10 minutes before an accidental DROP TABLE.
4. What’s the difference between RTO and RPO, and how does backup frequency relate to each?
5. Why is testing a restore just as important as taking the backup itself?
6. When would you use innodb_force_recovery, and what are the risks?
7. How would you design a backup strategy for a database that can’t tolerate more than 5 minutes of data loss?

FAQs

How often should I take full backups versus incrementals? It depends on data change rate and acceptable recovery time, but a common pattern I use is daily fulls with several incrementals throughout the day, supplemented by continuous binlog archiving for minute-level (or better) point-in-time recovery.

Is mysqldump good enough for a large production database? For very large databases, mysqldump‘s single-threaded logical export and the row-by-row restore process usually make it too slow to meet realistic RTOs. I switch to XtraBackup (or cloud-native snapshotting) once restore time becomes the binding constraint.

Do I still need backups if I have replication set up? Yes, absolutely. Replication protects against hardware failure, but it faithfully replicates human error too — a bad DELETE on the primary replicates straight to every replica within moments. Backups and PITR are what protect you from that.

How long should I retain backups? This is driven by compliance requirements as much as technical ones — some industries require years of retention. Technically, I retain enough full backups plus continuous binlogs to cover my organization’s realistic “how far back might we need to recover” window, which is usually 30–90 days for operational recovery, with longer cold-storage retention for compliance.

Summary and Key Takeaways

Backup and recovery in MySQL isn’t just about running mysqldump on a cron job and hoping for the best — it’s a layered strategy combining physical or logical full backups, incrementals, and continuous binary log archiving to support true point-in-time recovery. The single biggest gap I see teams have isn’t the backup itself, it’s never testing the restore, which means the first real test of your strategy happens during an actual incident — the worst possible time to discover a gap.

Key takeaways:

• Use physical backups (XtraBackup) for speed at scale; logical backups (mysqldump) for portability and smaller datasets.
• Enable binary logging and archive it continuously to support point-in-time recovery.
• Automate the entire backup pipeline, including integrity verification.
• Test full restores on a real schedule and measure your actual RTO.
• Store backups encrypted, off-site, and access-controlled as strictly as production data.

References

• MySQL 8.0 Reference Manual — Backup and Recovery: https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html
• MySQL 8.0 Reference Manual — Point-in-Time Recovery: https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html
• MySQL 8.0 Reference Manual — mysqldump: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
• Percona XtraBackup Documentation: https://docs.percona.com/percona-xtrabackup/8.0/
• MySQL 8.0 Reference Manual — innodb_force_recovery: https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html