Backup & Recovery

IntermediateDatabases
mysqldumppg_dumppoint-in-time recovery

Objective

Implement database backup and recovery procedures.

Tools & Technologies

  • mysqldump
  • pg_dump
  • point-in-time recovery

Key Commands

mysqldump -u root -p mydb > backup.sql
pg_dump mydb | gzip > backup.sql.gz
mysql -u root -p mydb < backup.sql
pg_restore -d mydb backup.dump

Lab Steps

01
Logical Backup

Create SQL dumps with mysqldump and pg_dump.

02
Restore

Restore from backup files and verify data integrity.

03
Binary Log

Enable MySQL binary logging for point-in-time recovery.

04
Test Recovery

Regularly test backups by restoring to a test environment.

Challenges Encountered

  • Backups without testing are worthless — always verify restore
  • Large databases need incremental backup strategies

Key Takeaways

  • 3-2-1 backup rule: 3 copies, 2 media types, 1 offsite
  • Test restoration quarterly at minimum