Database Backup Guide
Backup Strategy Reference
| Type | RTO | RPO | Storage | Best For |
|---|---|---|---|---|
| Full Backup | Hours | 24h | High | Weekly baseline |
| Incremental | Hours | 24h | Low | Daily changes |
| WAL/Binlog streaming | Minutes | Seconds | Medium | PITR |
| Snapshot (cloud) | Minutes | 1–4h | Medium | Cloud VMs |
PostgreSQL Backup & Restore
# Full dump (logical backup) pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump # -F c = custom format (recommended, supports parallel restore) # Restore pg_restore -h localhost -U postgres -d mydb_new -j 4 mydb.dump # -j 4 = 4 parallel workers # Point-in-Time Recovery setup # postgresql.conf: wal_level = replica archive_mode = on archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f' # Continuous backup with pgBackRest pgbackrest --stanza=main backup --type=full pgbackrest --stanza=main backup --type=incr # incremental
MySQL Backup & Restore
# Logical dump mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql # --single-transaction: consistent InnoDB backup without locking # Restore mysql -u root -p mydb < mydb.sql # Physical backup with Percona XtraBackup (no lock) xtrabackup --backup --target-dir=/backup/full xtrabackup --prepare --target-dir=/backup/full xtrabackup --copy-back --target-dir=/backup/full # Automated daily backup script #!/bin/bash DATE=$(date +%Y%m%d_%H%M%S) mysqldump -u backup_user -p$PASS --all-databases --single-transaction \ | gzip | aws s3 cp - s3://backups/mysql_$DATE.sql.gz
3-2-1 Backup Rule
- 3 — copies of data
- 2 — different storage media/types
- 1 — offsite copy (different region/cloud)