← Back to Curriculum

MySQL Backup and Recovery

📚 Lesson 10 of 15 ⏱️ 35 min

MySQL Backup and Recovery

35 min

Regular backups are essential for data protection, enabling recovery from data loss, corruption, or disasters. MySQL provides multiple backup methods, each suited to different scenarios. Understanding backup types, recovery procedures, and testing restore processes is critical for maintaining data availability. A backup strategy is only as good as your ability to restore from it, making regular restore testing essential.

mysqldump is MySQL's logical backup tool that creates SQL scripts containing database schema and data. mysqldump can backup entire databases, specific databases, or individual tables. Logical backups are portable across MySQL versions and platforms, making them ideal for migrations and cross-platform backups. Logical backups are human-readable and can be restored with mysql command. Logical backups are suitable for small to medium databases.

Physical backups copy the actual database files from the filesystem. Physical backups are faster than logical backups for large databases and enable point-in-time recovery when combined with binary logs. Physical backups require the database to be stopped or use special tools like Percona XtraBackup for hot backups. Physical backups are typically used for large databases requiring fast backup and restore.

Binary logs record all data changes and enable point-in-time recovery (PITR). By enabling binary logging and archiving binary logs, you can restore a database to any specific moment in time, not just when a backup was taken. PITR requires a base backup (full backup) and archived binary logs. This enables recovery from accidental data deletion or corruption that occurred after the last backup. PITR is essential for databases requiring minimal data loss.

Backup best practices include automating backups, storing backups off-site, testing restore procedures regularly, monitoring backup success, and maintaining multiple backup copies. Backup retention policies should balance storage costs with recovery requirements. Understanding backup and recovery procedures enables you to protect your data and meet recovery time objectives (RTO) and recovery point objectives (RPO) for your applications.

Additional backup considerations include backup compression (reducing storage requirements), incremental backups (backing up only changes), and backup encryption (protecting backup data). These features enable efficient, secure backup strategies. Understanding these considerations enables comprehensive data protection.

Key Concepts

  • Regular backups are essential for data protection and disaster recovery.
  • mysqldump creates logical backups as SQL scripts.
  • Physical backups copy database files for faster backup/restore.
  • Binary logs enable point-in-time recovery.
  • Backup strategies should include regular testing of restore procedures.

Learning Objectives

Master

  • Performing logical backups with mysqldump
  • Understanding physical backup methods
  • Configuring binary logging for point-in-time recovery
  • Restoring databases from backups

Develop

  • Understanding backup and recovery strategies
  • Designing disaster recovery procedures
  • Implementing data protection best practices

Tips

  • Backup database: mysqldump -u user -p database > backup.sql
  • Backup with options: mysqldump --single-transaction --routines --triggers database > backup.sql
  • Restore backup: mysql -u user -p database < backup.sql
  • Enable binary logging: log-bin = mysql-bin in my.cnf for point-in-time recovery.

Common Pitfalls

  • Not testing restore procedures, discovering backups are invalid when needed.
  • Not archiving binary logs, unable to perform point-in-time recovery.
  • Storing backups on same server, losing backups if server fails.
  • Not automating backups, missing backups due to human error.

Summary

  • Regular backups are essential for data protection.
  • Logical and physical backups serve different use cases.
  • Binary logs enable point-in-time recovery.
  • Testing restore procedures ensures backup validity.

Exercise

Perform database backup and recovery operations.

-- Backup entire database
mysqldump -u root -p my_first_db > backup_$(date +%Y%m%d_%H%M%S).sql

-- Backup specific tables
mysqldump -u root -p my_first_db users products > tables_backup.sql

-- Backup with specific options
mysqldump -u root -p --single-transaction --routines --triggers my_first_db > full_backup.sql

-- Create a backup database
CREATE DATABASE backup_db;

-- Restore from backup
mysql -u root -p backup_db < backup_20240101_120000.sql

-- Backup using MySQL commands
-- In MySQL client:
SELECT * FROM users INTO OUTFILE '/tmp/users_backup.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
';

-- Load data from backup
LOAD DATA INFILE '/tmp/users_backup.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
';

-- Enable binary logging for point-in-time recovery
-- In my.cnf:
-- log-bin = mysql-bin
-- binlog_format = ROW

-- Show binary logs
SHOW BINARY LOGS;

-- Show binary log events
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

Exercise Tips

  • Automate backups: use cron or scheduled tasks for regular backups.
  • Test PITR: practice restoring to different points in time.
  • Monitor backup sizes and durations to detect issues early.
  • Use --single-transaction for consistent backups of InnoDB tables.

Code Editor

Output