MySQL - Backup and Recovery in MySQL

1. Why Backup & Recovery is Important

• Protects against data loss (hardware failure, human error, bugs).

• Ensures business continuity.

• Enables disaster recovery and point-in-time recovery (PITR).

2. Types of Backups in MySQL

(a) Logical Backup

• Exports database objects and data as SQL statements.

• Tools: mysqldump, mysqlpump.

• Example:

  mysqldump -u root -p mydb > mydb_backup.sql
  

  • Easy to restore (just run the SQL).

  • Portable (works across MySQL versions).

  • Slower for very large databases.

(b) Physical Backup

• Copies actual database files (.ibd, .frm, .myd, .myi).

• Tools: Percona XtraBackup, filesystem snapshots.

• Example (XtraBackup):

  innobackupex /backup/2025-09-16
  

  • Faster for large datasets.

  • Includes logs, indexes, and metadata.

  • Better for big production systems.

(c) Full Backup

• Entire database (all data + schema).

• Example:

  mysqldump --all-databases > full_backup.sql
  

(d) Incremental Backup

• Stores only changes since the last backup.

• Useful for reducing storage.

• Example (XtraBackup):

  innobackupex --incremental /backup/inc1 --incremental-basedir=/backup/base
  

(e) Binary Log Backup

• Backup of MySQL binlogs (records of all changes).

• Essential for Point-in-Time Recovery (PITR).

3. Recovery Methods

(a) Recover from Logical Backup

mysql -u root -p mydb < mydb_backup.sql

(b) Recover from Physical Backup

• Stop MySQL, copy back data files, then restart.

• Example (XtraBackup):

  innobackupex --copy-back /backup/2025-09-16
  

(c) Point-in-Time Recovery (PITR)

• Restore last full backup.

• Replay binary logs up to just before the failure.

• Example:

  mysqlbinlog --start-datetime="2025-09-16 09:00:00" \
              --stop-datetime="2025-09-16 11:45:00" \
              /var/log/mysql/mysql-bin.000003 | mysql -u root -p
  

4. Backup & Recovery Strategy (Best Practices)

1. Automate backups (daily full + hourly incremental).

2. Keep binary logs for several days.

3. Store backups on remote servers/cloud (not same machine).

4. Regularly test recovery (a backup is useless if it can’t be restored).

5. Use compression & encryption for security.

6. Monitor backup jobs and set alerts.

5. Example Real-World Strategy (E-commerce DB)

• Nightly full backup using mysqldump or XtraBackup.

• Hourly incremental backups using binlogs.

• If a failure happens at 3:15 PM:

  • Restore full backup from midnight.

  • Apply incremental + binlogs until 3:14 PM.

  • Database is restored almost exactly to failure time.

Summary:

• Logical backups (easy, portable) for small/medium DBs.

• Physical backups (fast, efficient) for large production DBs.

• Combine with binary logs for point-in-time recovery.

• Always test restore process — backup is only as good as your ability to recover it.