Chapter 9: Backup and Recovery Strategies

September 28, 2025 ยท View on GitHub

Backup Types

Logical Backups

Export data as SQL statements:

  • Portable between MySQL versions
  • Human-readable
  • Slower for large databases
  • Can be selective (specific tables/databases)

Physical Backups

Copy actual database files:

  • Faster for large databases
  • Platform-specific
  • Requires same MySQL version for restore
  • All-or-nothing approach

mysqldump

Basic Usage

# Backup single database
mysqldump -u root -p mydb > mydb_backup.sql

# Backup specific tables
mysqldump -u root -p mydb table1 table2 > tables_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Backup with compression
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

Advanced Options

# Include stored procedures and functions
mysqldump -u root -p --routines mydb > mydb_with_routines.sql

# Add CREATE DATABASE statement
mysqldump -u root -p --databases mydb > mydb_with_create.sql

# Consistent backup (InnoDB)
mysqldump -u root -p --single-transaction mydb > mydb_consistent.sql

# Lock tables (MyISAM)
mysqldump -u root -p --lock-tables mydb > mydb_locked.sql

# Export structure only
mysqldump -u root -p --no-data mydb > mydb_structure.sql

# Export data only
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

Restoring from mysqldump

# Restore database
mysql -u root -p mydb < mydb_backup.sql

# Restore compressed backup
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# Restore with progress indicator
pv mydb_backup.sql | mysql -u root -p mydb

Binary Logs

Binary logs record all changes to database:

Enabling Binary Logs

-- In my.cnf
[mysqld]
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 100M
expire_logs_days = 7

Using Binary Logs

# View binary log files
mysql> SHOW BINARY LOGS;

# View events in binary log
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';

# Extract SQL from binary log
mysqlbinlog mysql-bin.000001 > statements.sql

# Apply binary log to database
mysqlbinlog mysql-bin.000001 | mysql -u root -p

Point-in-Time Recovery

# 1. Restore full backup
mysql -u root -p mydb < full_backup.sql

# 2. Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" mysql-bin.000001 | mysql -u root -p mydb

# Or up to specific position
mysqlbinlog --stop-position=4321 mysql-bin.000001 | mysql -u root -p mydb

Physical Backup Methods

Cold Backup

# Stop MySQL
sudo systemctl stop mysql

# Copy data directory
sudo cp -R /var/lib/mysql /backup/mysql_backup

# Start MySQL
sudo systemctl start mysql

Hot Backup with Percona XtraBackup

# Install XtraBackup
sudo apt-get install percona-xtrabackup-80

# Create backup
xtrabackup --backup --target-dir=/backup/full

# Prepare backup
xtrabackup --prepare --target-dir=/backup/full

# Restore backup
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

Backup Strategies

Full Backup

#!/bin/bash
# Weekly full backup script
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
mysqldump -u root -p$PASSWORD --all-databases --single-transaction > $BACKUP_DIR/full_$DATE.sql
find $BACKUP_DIR -name "full_*.sql" -mtime +30 -delete

Incremental Backup

# Enable binary logging
# Take full backup weekly
# Copy binary logs daily
cp /var/lib/mysql/mysql-bin.* /backup/incremental/

Differential Backup

# Using XtraBackup
# Full backup
xtrabackup --backup --target-dir=/backup/full

# Differential backup
xtrabackup --backup --incremental-basedir=/backup/full --target-dir=/backup/diff1

Automated Backup Script

#!/bin/bash

# Configuration
MYSQL_USER="backup_user"
MYSQL_PASS="secure_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup all databases
DATABASES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -v Database)

for DB in $DATABASES; do
    if [[ "$DB" != "information_schema" ]] && [[ "$DB" != "performance_schema" ]]; then
        echo "Backing up $DB"
        mysqldump -u $MYSQL_USER -p$MYSQL_PASS --single-transaction $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
    fi
done

# Remove old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed at $(date)"

Testing Backups

# Restore to test server
mysql -h testserver -u root -p test_db < backup.sql

# Verify data integrity
mysql -u root -p -e "SELECT COUNT(*) FROM important_table;" test_db

# Compare checksums
mysql -u root -p -e "CHECKSUM TABLE important_table;" production_db
mysql -u root -p -e "CHECKSUM TABLE important_table;" test_db

Cloud Backup

AWS S3

# Backup to S3
mysqldump -u root -p mydb | gzip | aws s3 cp - s3://my-backup-bucket/mysql/mydb_$(date +%Y%m%d).sql.gz

# Restore from S3
aws s3 cp s3://my-backup-bucket/mysql/mydb_20240115.sql.gz - | gunzip | mysql -u root -p mydb

Replication for Backup

-- Setup master-slave replication
-- On master
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- On slave
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=0;

START SLAVE;

Disaster Recovery Plan

  1. Regular Backups: Daily incremental, weekly full
  2. Offsite Storage: Cloud or remote location
  3. Test Restores: Monthly verification
  4. Documentation: Recovery procedures
  5. RTO/RPO Goals: Define acceptable downtime and data loss
  6. Monitoring: Alert on backup failures
  7. Encryption: Secure backup files

Best Practices

  1. 3-2-1 Rule: 3 copies, 2 different media, 1 offsite
  2. Test regularly: Backups are only good if they restore
  3. Document procedures: Clear recovery steps
  4. Monitor backup jobs: Alert on failures
  5. Encrypt sensitive data: Protect backups
  6. Version control: Track schema changes
  7. Automate: Reduce human error
  8. Retention policy: Balance storage vs. recovery needs

Next: Chapter 10: MySQL Tools and Ecosystem