Database Backup & Recovery
Master backup strategies — logical/physical backups, point-in-time recovery, disaster recovery planning, and backup automation.
50 min•By Priygop Team•Last updated: Feb 2026
Backup Strategies
- Logical Backup: mysqldump, pg_dump — exports SQL statements. Portable between versions/platforms. Slow for large databases (GB+). Good for small-medium databases and migrations
- Physical Backup: Copy data files directly — xtrabackup (MySQL), pg_basebackup (PostgreSQL). Faster for large databases. Must match exact database version
- Point-in-Time Recovery (PITR): Binary logs (MySQL) / WAL archives (PostgreSQL) — replay transactions to recover to any moment. Restore last full backup, then replay logs to target time
- Backup Schedule: Full backup weekly (Sunday night) + incremental daily + transaction log every 15 minutes. Retention: 30 days of dailies, 1 year of monthlies. Test restores regularly
- Disaster Recovery: RPO (Recovery Point Objective) — how much data loss is acceptable (5 minutes = continuous log shipping). RTO (Recovery Time Objective) — how fast to recover (1 hour = hot standby)
- Automation: Cron jobs for scheduled backups, upload to S3/GCS with encryption, monitor backup completion, alert on failures. Regular restore drills — untested backups are worthless