The Ultimate Guide to Database Backups and Disaster Recovery

"We lost everything."

Those are words no developer or business owner ever wants to say. Yet every day, companies lose critical data to hardware failures, human error, ransomware attacks, and natural disasters.

The good news? With a proper backup strategy, you can recover from almost anything.

The 3-2-1 Backup Rule

Before diving into specifics, remember this simple rule:

• 3 copies of your data
• 2 different storage types
• 1 copy offsite

This approach protects against almost any disaster scenario.

Types of Database Backups

Full Backup

A complete copy of your entire database.

Pros:

• Simple to restore
• Self-contained
• Easy to understand

Cons:

• Takes longest to create
• Uses the most storage
• Can impact performance during backup

# MySQL full backup
mysqldump -u root -p --all-databases > full_backup_2026-02-05.sql

# PostgreSQL full backup
pg_dumpall -U postgres > full_backup_2026-02-05.sql

# MongoDB full backup
mongodump --out /backups/full_2026-02-05

Incremental Backup

Only backs up data that changed since the last backup.

Pros:

• Fast to create
• Uses minimal storage
• Less performance impact

Cons:

• Requires full backup chain to restore
• More complex recovery process

# MySQL binary log backup (after enabling binary logging)
mysqlbinlog /var/log/mysql/mysql-bin.000001 > incremental_backup.sql

# PostgreSQL WAL archiving
archive_command = 'cp %p /backups/wal/%f'

Differential Backup

Backs up everything that changed since the last full backup.

Pros:

• Faster than full backups
• Simpler restore than incremental

Cons:

• Grows larger each day
• Still needs the full backup to restore

Snapshot Backup

A point-in-time image of your database storage.

Pros:

• Nearly instant
• Minimal performance impact
• Great for cloud environments

Cons:

• Requires compatible storage
• May need additional consistency steps

# AWS RDS snapshot
aws rds create-db-snapshot \
    --db-instance-identifier mydb \
    --db-snapshot-identifier mydb-snapshot-2026-02-05

# LVM snapshot (Linux)
lvcreate --snapshot --name db_snap --size 10G /dev/vg0/mysql_data

Backup Strategies by Database

MySQL Backup Options

Option 1: mysqldump (Simple, works everywhere)

# Backup single database
mysqldump -u root -p myapp > myapp_backup.sql

# Backup with compression
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Include routines and triggers
mysqldump -u root -p --routines --triggers myapp > myapp_full.sql

Option 2: mysqlpump (Faster, parallel)

# Parallel backup with 4 threads
mysqlpump -u root -p --default-parallelism=4 myapp > myapp_backup.sql

Option 3: Percona XtraBackup (Hot backup, no locking)

# Full backup without locking tables
xtrabackup --backup --target-dir=/backups/full

# Incremental backup
xtrabackup --backup --target-dir=/backups/inc1 \
    --incremental-basedir=/backups/full

PostgreSQL Backup Options

Option 1: pg_dump (Standard tool)

# Plain SQL format
pg_dump -U postgres myapp > myapp_backup.sql

# Custom format (compressed, flexible)
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Directory format (parallel backup)
pg_dump -U postgres -Fd -j 4 myapp -f /backups/myapp_dir

Option 2: pg_basebackup (Physical backup)

# Full physical backup
pg_basebackup -U postgres -D /backups/base -Ft -z -P

# With WAL files included
pg_basebackup -U postgres -D /backups/base -Ft -z -X stream

Option 3: Continuous Archiving (Point-in-time recovery)

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'
wal_level = replica

MongoDB Backup Options

Option 1: mongodump

# Backup all databases
mongodump --out /backups/mongo_2026-02-05

# Backup specific database
mongodump --db myapp --out /backups/myapp

# With compression
mongodump --db myapp --gzip --out /backups/myapp

Option 2: Filesystem snapshots

# Lock database, snapshot, unlock
mongo --eval "db.fsyncLock()"
# Take storage snapshot here
mongo --eval "db.fsyncUnlock()"

Automating Backups

Simple Cron Script

#!/bin/bash
# backup.sh

DATE=$(date +%Y-%m-%d_%H-%M)
BACKUP_DIR="/backups"
RETENTION_DAYS=30

# Create backup
mysqldump -u backup_user -p'password' myapp | gzip > "$BACKUP_DIR/myapp_$DATE.sql.gz"

# Remove old backups
find $BACKUP_DIR -name "myapp_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Upload to S3
aws s3 cp "$BACKUP_DIR/myapp_$DATE.sql.gz" s3://my-backups/mysql/

Add to crontab:

# Daily backup at 2 AM
0 2 * * * /usr/local/bin/backup.sh >> /var/log/backup.log 2>&1

Monitoring Backup Success

Don't just run backups - verify they completed:

#!/bin/bash
# Check if backup file exists and is recent
BACKUP_FILE=$(ls -t /backups/myapp_*.sql.gz 2>/dev/null | head -1)

if [ -z "$BACKUP_FILE" ]; then
    echo "ALERT: No backup file found!" | mail -s "Backup Failed" [email protected]
    exit 1
fi

# Check if file is less than 24 hours old
if [ $(find "$BACKUP_FILE" -mtime -1 | wc -l) -eq 0 ]; then
    echo "ALERT: Backup is older than 24 hours!" | mail -s "Backup Stale" [email protected]
    exit 1
fi

# Check minimum file size (adjust based on your data)
MIN_SIZE=1000000  # 1MB
FILE_SIZE=$(stat -f%z "$BACKUP_FILE" 2>/dev/null || stat -c%s "$BACKUP_FILE")

if [ "$FILE_SIZE" -lt "$MIN_SIZE" ]; then
    echo "ALERT: Backup file is too small!" | mail -s "Backup Suspect" [email protected]
    exit 1
fi

echo "Backup OK: $BACKUP_FILE ($FILE_SIZE bytes)"

Testing Your Backups

A backup you haven't tested is not a backup.

Schedule regular restore tests:

#!/bin/bash
# test_restore.sh

# Create test database
mysql -u root -p -e "CREATE DATABASE backup_test;"

# Restore latest backup
gunzip < /backups/myapp_latest.sql.gz | mysql -u root -p backup_test

# Verify row counts
mysql -u root -p -e "SELECT COUNT(*) FROM backup_test.users;"

# Compare with production
PROD_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM myapp.users;")
TEST_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM backup_test.users;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then
    echo "Restore test PASSED"
else
    echo "Restore test FAILED: counts don't match"
fi

# Cleanup
mysql -u root -p -e "DROP DATABASE backup_test;"

Disaster Recovery Planning

Recovery Time Objective (RTO)

How long can you be down? This determines your backup strategy:

Recovery Point Objective (RPO)

How much data can you afford to lose?

Document Your Recovery Process

Write down exact steps. When disaster strikes, you'll be stressed:

# Database Recovery Procedure

## Prerequisites
- Access to backup storage (S3 bucket: my-backups)
- Database server credentials (in password manager)
- SSH access to database server

## Steps

1. Identify latest valid backup
   - Check S3: `aws s3 ls s3://my-backups/mysql/ --recursive | tail -5`
   - Download: `aws s3 cp s3://my-backups/mysql/latest.sql.gz .`

2. Verify backup integrity
   - `gunzip -t latest.sql.gz`

3. Stop application servers
   - `kubectl scale deployment/myapp --replicas=0`

4. Restore database
   - `gunzip < latest.sql.gz | mysql -u root -p myapp`

5. Apply any binary logs if available
   - `mysqlbinlog binlog.000123 | mysql -u root -p`

6. Verify data
   - Run integrity checks
   - Compare row counts

7. Restart application
   - `kubectl scale deployment/myapp --replicas=3`

8. Monitor for errors
   - Check application logs
   - Verify user-facing functionality

Common Backup Mistakes

1. Backing Up to the Same Disk

If the disk fails, you lose both data and backups.

Fix: Always copy backups to a different physical location.

2. Not Testing Restores

Many backups are discovered to be corrupt only when needed.

Fix: Schedule monthly restore tests.

3. Ignoring Backup Monitoring

Failed backups go unnoticed until disaster strikes.

Fix: Set up alerts for backup failures and missing backups.

4. Keeping Backups Forever

Storage costs grow, old backups become useless.

Fix: Define a retention policy (e.g., daily for 7 days, weekly for 4 weeks, monthly for 12 months).

5. Backing Up Only the Database

What about configuration files, application code, uploaded files?

Fix: Document everything that needs backing up, not just the database.

Security Considerations

Encrypt Your Backups

# Encrypt with GPG
mysqldump myapp | gzip | gpg --encrypt --recipient [email protected] > backup.sql.gz.gpg

# Or with OpenSSL
mysqldump myapp | gzip | openssl enc -aes-256-cbc -salt -out backup.sql.gz.enc

Secure Backup Storage

• Use IAM roles with minimal permissions
• Enable versioning on S3 buckets
• Consider S3 Object Lock for ransomware protection
• Encrypt buckets at rest

Audit Backup Access

Know who accessed your backups and when:

# Enable S3 access logging
aws s3api put-bucket-logging --bucket my-backups --bucket-logging-status file://logging.json

Quick Reference: Backup Checklist

• Full backups run daily
• Backups stored in multiple locations
• At least one offsite/cloud backup
• Backups are encrypted
• Backup success is monitored
• Restore tested monthly
• Recovery procedure documented
• Team knows how to restore
• Retention policy defined
• Backup credentials secured

Conclusion

Backups are like insurance - you hope you never need them, but when you do, they're invaluable. The time to set up proper backups is now, not after disaster strikes.

Start with the basics: daily backups, offsite storage, and regular testing. Then refine based on your RTO and RPO requirements.

Remember: The only good backup is one you've successfully restored.

Need an Easier Way to Export Your Data?

LunoDB makes database exports simple with one-click backups and multiple format options. Export your data to SQL, CSV, or JSON with just a few clicks. Download LunoDB and protect your data today.