Backup and Restore #
No system is immune to data loss. Hardware fails. Migration scripts run with the wrong WHERE clause. Ransomware encrypts entire disks. A developer runs DELETE FROM orders on the production database because they forgot to change the connection string. All of these scenarios are real — and all have happened in production systems considered safe.
The difference between an incident resolved in hours and a disaster that can’t be recovered is one thing: whether a backup exists, is correct, and has ever been restore-tested.
A backup that’s never been restore-tested is an illusion of security. You don’t know whether the backup is valid until you actually need it — and that’s not the right time to discover the backup file is corrupt or the restore procedure isn’t documented. This article covers backup and restore from the angle that’s often missed: not just how to create them, but how to make sure they actually work when needed.
Two Concepts to Understand Before Designing a Strategy #
Before deciding which backup strategy to use, two concepts must be defined first because they determine everything: RPO and RTO.
RPO — Recovery Point Objective #
RPO is the answer to the question: “How much data loss is acceptable?” More precisely — if a disaster happens now, how far back are you able and willing to go?
RPO illustration:
Time: 08:00 12:00 16:00 20:00 ← disaster happens
│ │ │ │
Backup: [FULL] [incr] [incr] ✗ system down
If RPO = 4 hours:
→ The 16:00 backup is sufficient
→ Data between 16:00–20:00 may be lost
If RPO = 1 hour:
→ Need more frequent backups, or continuous WAL/binlog shipping
→ No more than 1 hour of data may be lost
The smaller the RPO → the more frequent the backups → the more expensive
RPO isn’t a technical decision — it’s a business decision. How much is an hour’s worth of data worth? How much is lost if an hour of transactions must be manually re-entered? The answers determine how aggressive the backup strategy must be.
RTO — Recovery Time Objective #
RTO is the answer to the question: “How long can the system stay down during recovery?” This is the maximum allowed time between the disaster happening and the system returning to operation.
RTO illustration:
flowchart LR
Event["Disaster happens"] -->|"5 min"| Detect["Detection"]
Detect -->|"10 min"| Decide["Decision"]
Decide --> Restore["Restore starts"]
Restore --> Online["System online"]
style Event stroke:#e74c3c,stroke-width:2px
style Online stroke:#2ecc71,stroke-width:2px- If RTO = 1 hour:
- The restore must finish within ~45 minutes (the rest is for detection and decisions).
- A full restore from a 100GB mysqldump might take 2 hours → doesn’t meet the RTO.
- Need a physical backup or a standby database.
- If RTO = 4 hours:
- A logical backup + restore might be enough.
- More strategy options available.
- The smaller the RTO → the faster the restore solution needed → the more expensive.
Strategy matrix based on RPO and RTO:
RPO / RTO │ RTO < 15 minutes │ RTO < 1 hour │ RTO < 4 hours
───────────┼───────────────────┼───────────────────┼───────────────────
RPO < 1 min│ Standby DB + │ Physical backup │ Physical backup
│ PITR │ + WAL streaming │ + WAL/binlog
───────────┼───────────────────┼───────────────────┼───────────────────
RPO < 1 hour│ Physical backup │ Physical backup │ Logical backup
│ + WAL/binlog │ + incremental │ + incremental
───────────┼───────────────────┼───────────────────┼───────────────────
RPO < 1 day│ Logical backup │ Logical backup │ Logical backup
│ daily │ daily │ daily
Five Backup Types and When to Use Them #
Full Backup #
A full backup copies the entire database contents at one point in time. This is the foundation of every backup strategy — almost all other strategies depend on a full backup as their starting point.
# Full logical backup with mysqldump (MySQL)
mysqldump \
--single-transaction \ # consistent backup without table locks (InnoDB)
--routines \ # include stored procedures and functions
--triggers \ # include triggers
--events \ # include scheduled events
--hex-blob \ # binary data stored as hex
-u root -p mydb \
| gzip > backup_full_$(date +%Y%m%d_%H%M%S).sql.gz
# Full logical backup with pg_dump (PostgreSQL)
pg_dump \
--format=custom \ # custom format: more compact, supports parallel restore
--compress=9 \ # compression level 9
--verbose \
mydb > backup_full_$(date +%Y%m%d_%H%M%S).dump
# Full physical backup with Percona XtraBackup (MySQL)
xtrabackup \
--backup \
--target-dir=/var/backup/full \
--user=root \
--password=secret
When to use: as a weekly or daily backup for small to medium databases. For large databases (> 100GB), full backups are often done weekly and combined with incrementals for the other days.
Trade-off: full backups are the easiest to restore, but the slowest to run and produce the largest files.
Incremental Backup #
An incremental backup only copies the changes since the last backup — whether the previous one was full or incremental. Much faster and smaller than a full backup, but restoring is more complex because it needs all backups in that chain.
# Incremental backup with Percona XtraBackup (MySQL)
# First incremental backup (base: the full backup)
xtrabackup \
--backup \
--target-dir=/var/backup/inc1 \
--incremental-basedir=/var/backup/full
# Second incremental backup (base: the first incremental)
xtrabackup \
--backup \
--target-dir=/var/backup/inc2 \
--incremental-basedir=/var/backup/inc1
Incremental backup chain diagram:
Monday Tuesday Wednesday Thursday Friday
[FULL] ← [INC1] ← [INC2] ← [INC3] ← [INC4]
Restoring to Thursday's state:
Restore FULL → Apply INC1 → Apply INC2 → Apply INC3 → done
(INC4 isn't needed because we're restoring to Thursday)
If INC2 is corrupt → can't restore to Thursday or Friday
→ Must go back to FULL → loses Tuesday and Wednesday data
→ This is the risk of long incremental chains
Long incremental chains increase risk: if one backup in the middle of the chain is corrupt, all backups after it become unusable. A common strategy is limiting the incremental chain to a maximum of 6 days, then starting over with a new full backup on the seventh day.
Differential Backup #
A differential backup copies all changes since the last full backup — not since any last backup. This means each differential backup grows larger over time, but restoring only needs two files: the full backup + the latest differential.
Incremental vs differential comparison:
Incremental:
Monday [FULL] → Tuesday [+1MB] → Wednesday [+1MB] → Thursday [+1MB]
Restoring Thursday: FULL + inc_Tuesday + inc_Wednesday + inc_Thursday
Differential:
Monday [FULL] → Tuesday [+1MB] → Wednesday [+2MB] → Thursday [+3MB]
Restoring Thursday: FULL + diff_Thursday (only two files!)
Differential: simpler restore, but backup size grows every day
Incremental: more complex restore, but backup size stays small every day
Logical Backup #
A logical backup produces human-readable SQL statements or dump formats — CREATE TABLE, INSERT INTO, and so on. This is the most portable backup type: it can be moved between database versions, across platforms, even inspected with a text editor.
# Logical backup of a single table (MySQL)
mysqldump --single-transaction mydb orders \
> backup_orders_$(date +%Y%m%d).sql
# Logical backup of all databases (MySQL)
mysqldump --single-transaction --all-databases \
| gzip > backup_all_$(date +%Y%m%d).sql.gz
# Restore from a logical backup (MySQL)
gunzip < backup_full_20250601_020000.sql.gz | mysql -u root -p mydb
# Parallel logical backup with pg_dump (PostgreSQL — faster for large DBs)
pg_dump \
--format=directory \ # directory format enables parallel restore
--jobs=4 \ # use 4 parallel workers
--file=/var/backup/mydb \
mydb
# Parallel restore with pg_restore
pg_restore \
--dbname=mydb \
--jobs=4 \ # 4 parallel workers
--verbose \
/var/backup/mydb
When to use: small to medium databases (< 50GB), portability needs across versions, or when you need to restore only part of the data (one table, one schema).
Limitations: for large databases, mysqldump can take hours. Also, it can’t be used directly for point-in-time recovery.
Physical Backup #
A physical backup copies the physical files the database uses: data files, log files, and configuration. The result is an exact copy of the database state at a specific point in time.
# Physical backup with Percona XtraBackup (MySQL — hot backup, no lock needed)
xtrabackup --backup --target-dir=/var/backup/physical --user=root --password=secret
# Prepare the backup before restoring (apply uncommitted logs)
xtrabackup --prepare --target-dir=/var/backup/physical
# Restore a physical backup
# 1. Stop MySQL
systemctl stop mysql
# 2. Remove the old data directory (careful!)
rm -rf /var/lib/mysql/*
# 3. Copy the backup into the data directory
xtrabackup --copy-back --target-dir=/var/backup/physical
# 4. Fix ownership
chown -R mysql:mysql /var/lib/mysql
# 5. Start MySQL
systemctl start mysql
When to use: large databases (> 50GB), small RTO needs, or as the basis for point-in-time recovery.
Point-in-Time Recovery: Back to the Exact Second #
Point-in-time recovery (PITR) lets you restore the database to a very specific state — not just the last backup point, but a particular second before the incident happened. This is an extremely valuable capability when human error occurs and you know exactly when the mistake was made.
PITR works by combining a full backup with continuously recorded change logs:
The Point-in-Time Recovery flow:
Sunday 00:00 Monday 14:00 Monday 14:23
[FULL BACKUP] → ... all changes ... → [DROP TABLE orders] ← incident!
PITR to Monday 14:22 (one minute before the incident):
Step 1: Restore the Sunday 00:00 full backup
Step 2: Apply binlog/WAL from Sunday 00:00 until Monday 14:22:59
Step 3: Stop — don't apply logs after 14:22:59
Step 4: The database returns to the state just before DROP TABLE executed
PITR with MySQL Binlogs #
# Enable binary logging in my.cnf (mandatory for PITR)
# [mysqld]
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_format = ROW
# expire_logs_days = 14
# View the list of available binlogs
SHOW BINARY LOGS;
# Inspect binlog contents to find the incident position
mysqlbinlog /var/log/mysql/mysql-bin.000042 | grep -A5 "DROP TABLE"
# Restore: apply binlogs until BEFORE the incident (based on timestamps)
mysqlbinlog \
--start-datetime="2025-06-02 00:00:00" \
--stop-datetime="2025-06-02 14:22:59" \ # exactly before the incident
/var/log/mysql/mysql-bin.000040 \
/var/log/mysql/mysql-bin.000041 \
/var/log/mysql/mysql-bin.000042 \
| mysql -u root -p mydb
# Or based on binlog positions (more precise than timestamps)
mysqlbinlog \
--start-position=4 \
--stop-position=156789 \ # the position just before the problematic statement
/var/log/mysql/mysql-bin.000042 \
| mysql -u root -p mydb
PITR with PostgreSQL WAL #
# WAL archiving configuration in postgresql.conf
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /var/backup/wal/%f'
# recovery.conf (PostgreSQL < 12) or postgresql.conf (PostgreSQL >= 12)
# restore_command = 'cp /var/backup/wal/%f %p'
# recovery_target_time = '2025-06-02 14:22:59'
# recovery_target_action = 'promote'
For PITR to work, binary logs (MySQL) or WAL archives (PostgreSQL) must be continuously available since the last full backup. If there’s a gap in the logs — for example, logs deleted before the latest backup was taken, or the log disk filled up and logs couldn’t be written — PITR can’t be done to any point after that gap. Make sure log availability is monitored and log retention isn’t shorter than the full backup interval.
The Layered Backup Strategy: The 3-2-1 Rule #
The most battle-tested backup strategy is the 3-2-1 rule: keep 3 copies of the data, on 2 different media, with 1 copy in a physically different location.
The 3-2-1 Backup Rule:
- 3 Copies:
- Copy 1: The production database (live data)
- Copy 2: A backup on a backup server (local/same location)
- Copy 3: A backup in remote object storage (S3/GCS)
- 2 Different Media:
- Media 1: Server disk (local)
- Media 2: Object storage (cloud)
- 1 Offsite:
- The S3/GCS/Azure Blob copy sits in a different region. If the main data center burns down, the backup stays safe.
A simple implementation using cron and AWS S3:
#!/bin/bash
# backup_and_upload.sh — run via cron daily at 02:00
BACKUP_DIR="/var/backup/db"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp"
S3_BUCKET="s3://my-company-db-backups"
RETENTION_DAYS=7
# Step 1: create a local backup
mysqldump \
--single-transaction \
--routines --triggers --events \
-u backup_user -p"$DB_PASSWORD" \
"$DB_NAME" \
| gzip > "$BACKUP_DIR/backup_${TIMESTAMP}.sql.gz"
# Step 2: encrypt before uploading
gpg --symmetric \
--cipher-algo AES256 \
--passphrase "$BACKUP_ENCRYPTION_KEY" \
"$BACKUP_DIR/backup_${TIMESTAMP}.sql.gz"
# Step 3: upload to S3
aws s3 cp \
"$BACKUP_DIR/backup_${TIMESTAMP}.sql.gz.gpg" \
"$S3_BUCKET/daily/" \
--storage-class STANDARD_IA # cheaper for rarely accessed data
# Step 4: delete local backups older than RETENTION_DAYS
find "$BACKUP_DIR" -name "*.sql.gz*" -mtime +$RETENTION_DAYS -delete
# Step 5: delete S3 backups older than 30 days (a lifecycle policy is better)
# Or configure S3 Lifecycle Rules to automate this
echo "Backup complete: backup_${TIMESTAMP}.sql.gz.gpg"
Restore Testing: The Only Way to Prove a Backup Is Valid #
A backup that’s never been restored is an assumption, not a guarantee. Backup files can be corrupt without detection. Restore procedures can be outdated due to database version changes. Restore time estimates can be wrong. All of this is only discovered when a restore is actually performed.
Recommended restore test schedule:
Weekly tests:
→ Restore one table from the latest backup into a staging database
→ Verify row counts, spot-check a few rows
→ Time estimate: 30 minutes
Monthly tests:
→ Full restore into a separate environment
→ Run application smoke tests against the restored database
→ Verify integrity: checksums, foreign keys, data consistency
→ Time estimate: 2–4 hours depending on DB size
Quarterly tests (disaster recovery drills):
→ Simulate a real disaster scenario
→ Measure the actual RTO from start until the system is back online
→ Involve on-call engineers to validate the runbook
→ Update the runbook based on findings
# Example automated restore test script (can run via CI/CD)
#!/bin/bash
BACKUP_FILE="$1" # path to the backup file to test
TEST_DB="restore_test_$(date +%Y%m%d)"
echo "Creating test database: $TEST_DB"
mysql -u root -p"$ROOT_PASSWORD" -e "CREATE DATABASE $TEST_DB;"
echo "Restore started: $(date)"
START_TIME=$(date +%s)
gunzip < "$BACKUP_FILE" | mysql -u root -p"$ROOT_PASSWORD" "$TEST_DB"
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
echo "Restore finished in ${DURATION} seconds"
echo "Table verification:"
mysql -u root -p"$ROOT_PASSWORD" "$TEST_DB" -e "SHOW TABLES;"
echo "Row count verification:"
mysql -u root -p"$ROOT_PASSWORD" "$TEST_DB" -e "
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = '$TEST_DB'
ORDER BY table_rows DESC;"
echo "Cleaning up test database:"
mysql -u root -p"$ROOT_PASSWORD" -e "DROP DATABASE $TEST_DB;"
echo "Restore test complete. Actual restore time: ${DURATION} seconds"
Backup Encryption and Security #
Backups often contain the most sensitive data in the system — the entire database, including hashed passwords, encrypted credit card numbers, medical data, and personal information. An unencrypted backup falling into the wrong hands is a security disaster, not just a technical problem.
Backup security layers:
1. Encryption in transit
→ Use HTTPS/TLS when uploading to object storage
→ Never transfer backups via HTTP or FTP
2. Encryption at rest
→ Encrypt backup files before storing
→ Use AES-256 or GPG
→ Encryption keys stored separately from the backups
3. Access control
→ Only a dedicated backup account can create backups
→ That account only has SELECT and LOCK TABLES rights (no WRITE)
→ S3 bucket policy: only certain IPs or roles can read
4. Encryption key rotation
→ Change encryption keys periodically
→ Re-encrypt old backups if an old key is compromised
5. Audit trails
→ Log who accessed backups, when, and from where
→ Alert on unusual backup access
-- Create a dedicated backup user with minimal rights (MySQL)
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password_here';
-- Rights needed for mysqldump with --single-transaction
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
GRANT RELOAD ON *.* TO 'backup_user'@'localhost'; -- for FLUSH TABLES
GRANT PROCESS ON *.* TO 'backup_user'@'localhost'; -- for SHOW PROCESSLIST
-- Don't grant INSERT, UPDATE, DELETE, DROP, or SUPER rights
FLUSH PRIVILEGES;
Backup from a Replica, Not the Primary #
Running backups directly against the primary database can create significant IO load — especially for physical backups or mysqldump without --single-transaction which needs locks. A better solution is running backups from a replica dedicated to that purpose.
Backup-from-replica architecture:
flowchart TD
Primary["Primary DB<br/>(Serving queries)"] -->|"Replication"| Replica["Backup Replica<br/>(Dedicated to backups)"]
Replica -->|"mysqldump / xtrabackup"| Job["Backup Job"]
Job -->|"Upload"| S3["Object Storage<br/>(S3 / GCS)"]Advantages:
- The primary isn’t burdened with backup IO.
- User queries aren’t impacted while backups run.
- The replica can pause replication first before backing up for consistency.
# Backing up from a MySQL replica with mysqldump
# Make sure the replica is synced before backing up
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"
# Make sure the result is 0 or very small
# Back up from the replica
mysqldump \
--single-transaction \
--master-data=2 \ # include the binlog position for PITR
--routines --triggers \
-h replica-host \
-u backup_user -p"$BACKUP_PASSWORD" \
mydb | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz
Anti-Patterns to Avoid #
# ✗ Anti-pattern 1: backups never restore-tested
# Backups run every night for 2 years
# Nobody ever tried a restore
# When needed: corrupt files, incompatible versions
# → No data can be recovered
# ✓ Solution: schedule automated restore tests at least monthly
# Restore script to staging + row count verification + alert on failure
────────────────────────────────────────────────────────────────────────────────
# ✗ Anti-pattern 2: backups on the same server as the database
# Server crash or disk failure → primary data lost AND backup lost
# ✓ Solution: backups always go to a different location (object storage, another server)
────────────────────────────────────────────────────────────────────────────────
# ✗ Anti-pattern 3: binary logging / WAL not enabled
# Only a daily full backup exists
# An incident happens at 23:55 → almost 24 hours of data lost
# ✓ Solution: enable binlog/WAL for PITR, so data loss is minimal
────────────────────────────────────────────────────────────────────────────────
# ✗ Anti-pattern 4: backups not encrypted in object storage
# Misconfigured S3 bucket → publicly accessible
# The entire database dump can be downloaded by anyone
# ✓ Solution: encrypt all backups before upload, use a strict bucket policy
────────────────────────────────────────────────────────────────────────────────
# ✗ Anti-pattern 5: not knowing the system's RTO and RPO
# "We have backups" — but how long does a restore take?
# A 500GB database via mysqldump: restore might take 6–8 hours
# If the business SLA demands the system online in 1 hour, this doesn't meet the RTO
# ✓ Solution: define RPO and RTO, measure actual restore time, adjust the strategy
────────────────────────────────────────────────────────────────────────────────
# ✗ Anti-pattern 6: undefined backup retention
# Backups pile up without limits → storage fills up → new backups fail
# ✓ Solution: a clear retention policy + backup storage usage monitoring
Backup and Restore Review Checklist #
BACKUP STRATEGY:
□ RPO defined and agreed with business stakeholders
□ RTO defined and validated with actual restore times
□ Backup types chosen matching RPO/RTO needs and data size
□ Binary logging (MySQL) or WAL archiving (PostgreSQL) enabled
□ A layered backup strategy (full + incremental/differential) exists
BACKUP SECURITY:
□ All backups encrypted before storage (AES-256 or GPG)
□ Encryption keys stored separately from backups
□ Backup transfers use HTTPS/TLS
□ Backup users have minimal rights (SELECT, SHOW VIEW, LOCK TABLES)
□ Backup storage access restricted with access policies
LOCATION AND RETENTION:
□ Backups stored in a different location from the database server (3-2-1 rule)
□ At least one copy in a different region or availability zone
□ Retention policies defined for every backup type
□ Backup storage monitored so it doesn't suddenly fill up
RESTORE TESTING:
□ Restore tests done at least monthly
□ Actual restore time measured and meeting the RTO
□ Restore procedures documented step-by-step in a runbook
□ The runbook practiced by on-call engineers
□ PITR tests performed at least once to validate binlog/WAL
MONITORING:
□ Backup jobs monitored — alert if failed or not running
□ Backup sizes monitored — drastic changes can signal problems
□ binlog/WAL retention monitored — must not be shorter than the full backup interval
□ Backup storage monitored — alert when approaching limits
Summary #
- A backup that’s never been restored isn’t a backup — it’s an untested assumption. Schedule regular, automated restore tests, not just when an incident has already happened.
- Define RPO and RTO before choosing a strategy — RPO determines how often backups must be taken, RTO determines how fast restore must happen. Both are business decisions, not purely technical ones.
- A full backup alone isn’t enough for small RPOs — enable binary logging (MySQL) or WAL archiving (PostgreSQL) to enable point-in-time recovery to the exact second before an incident.
- The 3-2-1 rule is the minimum standard — 3 copies of the data, on 2 different media, with 1 copy offsite. A backup on the same server as the database doesn’t meet this criterion.
- Backup encryption is mandatory, not optional — backups contain all the most sensitive data in the system. An unencrypted backup file is a security risk equal to an unprotected database.
- Run backups from a replica, not the primary — avoids IO load on the primary that could affect user queries, especially for physical backups that can take a long time.
- binlog/WAL retention must be longer than the full backup interval — if full backups run weekly but binlogs are only kept 3 days, PITR can only cover the last 3 days, not a week.
- Backup users need minimal rights — the account used for backups doesn’t need INSERT, UPDATE, or DROP. The least privilege principle applies here.
- Measure actual restore times periodically — the estimate “maybe around 2 hours” isn’t enough. Measure the real restore time and make sure it meets the agreed RTO.
- Runbook documentation is part of the backup — a restore procedure living only in someone’s head is a real risk. When an incident happens in the middle of the night, a clear runbook is the difference between fast recovery and chaos.