Backup & Recovery in MySQL

No database system is completely safe from failure.

Failures can occur due to:

  • Hardware crashes
  • Power failure
  • Accidental data deletion
  • Software bugs
  • Cyber attacks
  • Human errors

Because of this, backup and recovery are critical parts of database management.

A backup ensures that your data can be restored if something goes wrong.

1. Why Backup Is Important

Imagine:

  • A developer accidentally runs DELETE FROM users;
  • A server crashes and corrupts database files
  • A hacker drops important tables

Without backups:
Data may be permanently lost.

With backups:
You can restore the database to a previous safe state.

Backup is not optional in production systems — it is mandatory.

2. Types of Backups in MySQL

There are two main types of backups:

1. Logical Backup

A logical backup stores data in the form of SQL statements.

Example:

  • CREATE TABLE statements
  • INSERT statements

These backups are human-readable.

Tool used:
mysqldump

Example:

mysqldump -u root -p database_name > backup.sql

Advantages:

  • Portable
  • Easy to restore
  • Good for small to medium databases

Disadvantages:

  • Slower for very large databases
  • Restoration can take time

2. Physical Backup

A physical backup copies actual database files from disk.

This includes:

  • Data files
  • Index files
  • Log files

Physical backups are:

  • Faster for large databases
  • Used in enterprise-level systems

Tools:

  • MySQL Enterprise Backup
  • Percona XtraBackup

3. Full Backup vs Incremental Backup

Full Backup

Copies the entire database.

Simple but takes more storage.

Incremental Backup

Copies only the data changed since the last backup.

  • Faster
  • Saves storage
  • More complex to restore

Production systems often combine:
Weekly full backup + Daily incremental backups

4. Using mysqldump (Most Common Method)

Backup entire database:

mysqldump -u username -p database_name > backup.sql

Backup all databases:

mysqldump -u username -p --all-databases > alldb_backup.sql

Backup specific tables:

mysqldump -u username -p database_name table1 table2 > tables_backup.sql

5. Restoring a Backup

To restore a logical backup:

mysql -u username -p database_name < backup.sql

This executes all SQL statements in the backup file.

Always test restoration on a staging server before restoring in production.

6. Binary Logs and Point-in-Time Recovery

MySQL maintains binary logs (binlogs).

Binary logs record:

  • All changes made to the database
  • INSERT, UPDATE, DELETE operations

These logs help in:

Point-in-Time Recovery (PITR)

Example scenario:

  • Full backup taken at 2:00 AM
  • The data was corrupted at 4:00 PM

Using binary logs:
You can restore the backup from 2:00 AM
Then replay changes up to 3:59 PM

This minimizes data loss.

7. Crash Recovery in InnoDB

InnoDB supports crash recovery automatically.

It uses:

  • Redo logs
  • Undo logs

If MySQL crashes:

  • InnoDB replays redo logs
  • Rolls back incomplete transactions
  • Ensures the database remains consistent

This works because InnoDB follows ACID principles.

8. Backup Strategies for Production

A good backup strategy includes:

  1. Regular automated backups
  2. Off-site storage (cloud or remote server)
  3. Backup encryption
  4. Periodic restore testing
  5. Monitoring backup success

Never assume backups are working.
Always test the restore process.

9. Common Backup Mistakes

  1. Not testing backups
  2. Storing backup on the same server
  3. Not backing up binary logs
  4. Forgetting to back up users and privileges
  5. Running backups during peak traffic without planning

10. Real-World Example

E-commerce system:

  • Daily full backup at midnight
  • Incremental backups every 6 hours
  • Binary logs enabled
  • Backups stored in cloud storage

If something goes wrong, the system can be restored with minimal data loss.

Summary

Backup and recovery are essential for database reliability.

Important concepts:

  • Logical backup (mysqldump)
  • Physical backup
  • Full vs incremental backup
  • Binary logs
  • Point-in-time recovery
  • Crash recovery (InnoDB)

A database without backups is a major risk.

Proper backup planning ensures data safety, business continuity, and disaster recovery capability.