Database Backup
Chapter 74: Database Backup and Restore
Section titled “Chapter 74: Database Backup and Restore”Comprehensive Database Backup Strategies
Section titled “Comprehensive Database Backup Strategies”Why This Matters in DevOps/SRE
Section titled “Why This Matters in DevOps/SRE”Backups are your last line of defense against data loss. Whether from hardware failure, human error, or ransomware, you need tested backups to recover. As a DevOps/SRE, you’ll design backup strategies, automate backups, and most importantly - regularly test restores.
┌─────────────────────────────────────────────────────────────────────────────┐│ BACKUP STRATEGY IN DEVOPS │├─────────────────────────────────────────────────────────────────────────────┤│ ││ ┌─────────────────────────────────────────────────────────────────────┐ ││ │ THE 3-2-1 BACKUP RULE │ ││ │ │ ││ │ 3 copies of data │ ││ │ 2 different media types │ ││ │ 1 offsite backup │ ││ │ │ ││ │ Example: │ ││ │ • Primary DB │ ││ │ • Local backup (disk) │ ││ │ • Remote backup (S3/Glacier) │ ││ │ │ ││ └─────────────────────────────────────────────────────────────────────┘ ││ ││ ┌─────────────────────────────────────────────────────────────────────┐ ││ │ BACKUP TYPES │ ││ │ │ ││ │ Full: Complete database copy │ ││ │ Incremental: Changes since last backup │ ││ │ Differential: Changes since last full backup │ ││ │ Point-in-time: Recovery to specific timestamp │ ││ │ │ ││ └─────────────────────────────────────────────────────────────────────┘ ││ ││ ┌─────────────────────────────────────────────────────────────────────┐ ││ │ CRITICAL: TEST RESTORES! │ ││ │ │ ││ │ A backup that can't be restored is worthless! │ ││ │ │ ││ │ • Weekly restore tests in staging │ ││ │ • Document restore procedures │ ││ │ • Measure time to restore │ ││ │ • Test partial restores (specific tables) │ ││ │ │ ││ └─────────────────────────────────────────────────────────────────────┘ ││ │└─────────────────────────────────────────────────────────────────────────────┘Real-world scenarios:
- Ransomware recovery: Restore from clean backup before attack
- Accidental deletion: Point-in-time recovery to before deletion
- Schema migration failure: Restore to previous state
- Hardware failure: Bring up DB on new hardware from backup
74.1 MySQL/MariaDB Backup
Section titled “74.1 MySQL/MariaDB Backup”mysqldump
Section titled “mysqldump”# Full backupmysqldump -u root -p --all-databases > full_backup.sql
# Single databasemysqldump -u root -p mydb > mydb.sql
# Multiple databasesmysqldump -u root -p --databases db1 db2 db3 > backup.sql
# With optionsmysqldump -u root -p \ --single-transaction \ --routines \ --triggers \ --events \ mydb > mydb.sql
# Quick backup (no locks)mysqldump -u root -p --single-transaction --quick mydb > backup.sql
# Lock tables (for MyISAM)mysqldump -u root -p --lock-tables mydb > backup.sql
# Compressed backupmysqldump -u root -p mydb | gzip > backup.sql.gz
# Partial backup (only tables)mysqldump -u root -p mydb table1 table2 > tables.sqlRestore
Section titled “Restore”# Restore databasemysql -u root -p mydb < backup.sql
# Restore compressedgunzip < backup.sql.gz | mysql -u root -p
# Restore to different databasemysql -u root -p newdb < backup.sql
# Execute SQL directlymysql -u root -p -e "source backup.sql"Binary (Physical) Backup
Section titled “Binary (Physical) Backup”# Using xtrabackup (Percona)# Installsudo apt install percona-xtrabackup
# Full backupxtrabackup --backup --target-dir=/backup/full --user=root --password=root
# Prepare backupxtrabackup --prepare --target-dir=/backup/full
# Restorextrabackup --copy-back --target-dir=/backup/full
# Incremental backupxtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full
# Stream backupxtrabackup --backup --stream=tar | gzip > backup.tar.gz74.2 PostgreSQL Backup
Section titled “74.2 PostgreSQL Backup”pg_dump
Section titled “pg_dump”# Plain SQL backuppg_dump -U user mydb > backup.sqlpg_dump -U user mydb -f backup.sql
# Custom format (compressed)pg_dump -U user -Fc mydb > backup.dump
# Directory format (parallel)pg_dump -U user -Fd mydb -f backup_dir
# Only schemapg_dump -U user --schema-only mydb > schema.sql
# Only datapg_dump -U user --data-only mydb > data.sql
# Compressedpg_dump -U user mydb | gzip > backup.sql.gzRestore
Section titled “Restore”# Plain SQL restorepsql -U user mydb < backup.sql
# Custom format restorepg_restore -U user -d mydb backup.dump
# Restore to different databasecreatedb -U user newdbpg_restore -U user -d newdb backup.dump
# Restore specific tablepg_restore -U user -d mydb --data-only --table=mytable backup.dumppg_basebackup (Physical)
Section titled “pg_basebackup (Physical)”# Full base backuppg_basebackup -h localhost -U replicator -D /backup/base -P -Xs
# With tar formatpg_basebackup -h localhost -U replicator -D /backup -Ft -z -P
# With replication slotpg_basebackup -h master -D /backup -U replicator -P --slot=replica_slot74.3 Automation
Section titled “74.3 Automation”Backup Script Example
Section titled “Backup Script Example”#!/bin/bashBACKUP_DIR="/backups"DATE=$(date +%Y%m%d_%H%M%S)DB_NAME="mydb"DB_USER="backup"DB_PASS="password"
# MySQL backupmysqldump -u $DB_USER -p$DB_PASS --single-transaction \ --routines --triggers $DB_NAME | gzip > \ $BACKUP_DIR/mysql_${DB_NAME}_${DATE}.sql.gz
# PostgreSQL backuppg_dump -U $DB_USER -Fc $DB_NAME | gzip > \ $BACKUP_DIR/pg_${DB_NAME}_${DATE}.dump.gz
# Keep only 7 daysfind $BACKUP_DIR -name "*.sql.gz" -mtime +7 -deletefind $BACKUP_DIR -name "*.dump.gz" -mtime +7 -delete
# Logecho "Backup completed: $DATE" >> /var/log/backup.logCron Schedule
Section titled “Cron Schedule”# Daily at 2 AM0 2 * * * root /usr/local/bin/db-backup.sh
# Every 6 hours# 0 */6 * * * root /usr/local/bin/db-backup.sh74.4 Interview Questions
Section titled “74.4 Interview Questions”Basic Questions
Section titled “Basic Questions”-
What is mysqldump?
- MySQL logical backup tool
-
What’s the difference between pg_dump and pg_basebackup?
- pg_dump: logical (SQL), pg_basebackup: physical
-
What does —single-transaction do?
- Wraps dump in transaction for consistent backup
-
What is point-in-time recovery?
- Restoring to specific point using WAL
-
How do you automate backups?
- Cron jobs with backup scripts
Common Mistakes & Anti-Patterns
Section titled “Common Mistakes & Anti-Patterns”1. Not Testing Backups
Section titled “1. Not Testing Backups”❌ WRONG: Assuming backups work without testing
# Just assuming backup is goodmysqldump -u root -p db > backup.sql# Never restore to verify!✅ CORRECT: Regular restore testing
#!/bin/bash# Test restore weeklymysql -u root -p test_restore < backup.sql# Verify data integrity2. No Offsite Backup
Section titled “2. No Offsite Backup”❌ WRONG: Backups only on same server
# Local backup onlymysqldump db > /backup/db.sql# If server fails, backups lost!✅ CORRECT: Offsite backup
# Backup to local + remotemysqldump db > /backup/db.sqlaws s3 cp /backup/db.sql s3://bucket/backups/3. Backing Up During Peak Hours
Section titled “3. Backing Up During Peak Hours”❌ WRONG: Backup impacts production performance
# Running during business hours0 14 * * * mysqldump db > backup.sql # 2 PM - peak time✅ CORRECT: Schedule during off-peak
# Run at 3 AM0 3 * * * mysqldump --single-transaction db > backup.sql4. Not Encrypting Backups
Section titled “4. Not Encrypting Backups”❌ WRONG: Sensitive data in plain text
# Plain text backupmysqldump db > backup.sql# Anyone with access can read sensitive data!✅ CORRECT: Encrypt backups
# Encrypted backupmysqldump db | gpg -c > backup.sql.gpg# Or use mysqldump with --set-gtid-purged5. No Backup Retention Policy
Section titled “5. No Backup Retention Policy”❌ WRONG: Keeping backups forever, filling disk
# No cleanupmysqldump db > backup_$(date +%Y%m%d).sql# Disk will fill up!✅ CORRECT: Retention policy
# Keep 7 days locally, 30 days in S30 3 * * * mysqldump db > /backup/db_$(date +%Y%m%d).sqlfind /backup -name "db_*.sql" -mtime +7 -deleteaws s3 sync /backup s3://bucket/ --deleteSummary
Section titled “Summary” Quick Reference+------------------------------------------------------------------+| || MySQL: || +----------------------------------------------------------+ || | mysqldump -u user -p db > backup.sql | || | mysql -u user -p db < backup.sql | || | xtrabackup --backup --target-dir=/backup | || +----------------------------------------------------------+ || || PostgreSQL: || +----------------------------------------------------------+ || | pg_dump -U user db > backup.sql | || | psql -U user db < backup.sql | || | pg_basebackup -D /backup | || +----------------------------------------------------------+ || |+------------------------------------------------------------------+