Master MySQL Backup & Recovery: Complete Guide for Reliable Data Protection

Introduction

Data is a core asset; MySQL is a mainstream relational DBMS; ensuring its data safety is critical. This article explores backup strategies, tools, and best practices for ops engineers.

Backup Strategy Overview

Backup Types

By content:

Full Backup : backs up all data.

Incremental Backup : backs up changes since last backup.

Differential Backup : backs up changes since last full backup.

By method:

Physical Backup : copies data and log files directly.

Logical Backup : exports schema and data as SQL statements.

By service availability:

Hot Backup : performed while the database is running.

Warm Backup : performed when the database is read‑only.

Cold Backup : performed when the database is stopped.

Backup Planning Principles

Key factors include RTO, RPO, data volume, business importance, network bandwidth, and storage cost.

Built‑in MySQL Backup Tools

mysqldump

mysqldump is the official logical backup utility.

Basic syntax:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

Common options: --single-transaction: consistent read for InnoDB. --routines: include stored procedures and functions. --triggers: include triggers. --events: include event scheduler. --master-data=2: record binary log position. --flush-logs: flush logs before backup. --lock-all-tables: lock all tables (MyISAM).

Usage examples:

# Backup a single database
mysqldump -u root -p --single-transaction --routines --triggers \
  --master-data=2 --flush-logs database_name > backup_$(date +%Y%m%d_%H%M%S).sql

# Backup all databases
mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events > full_backup_$(date +%Y%m%d_%H%M%S).sql

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

# Dump only schema
mysqldump -u root -p --no-data database_name > schema_backup.sql

Pros:

Cross‑platform, portable dump files.

Selective backup of databases or tables.

Text format easy to inspect and edit.

Supports compression.

Cons:

Slower backup and restore.

Large dump files for big databases.

Potential table locking affecting production.

mysqlpump

mysqlpump, introduced in MySQL 5.7, provides multithreaded backup.

Basic syntax: mysqlpump [options] [db_name [tbl_name ...]] Key features:

Parallel multithreaded backup.

Exclude specific databases or tables.

Compressed output.

Improved progress reporting.

Example:

# Parallel backup with 4 threads
mysqlpump -u root -p --default-parallelism=4 --all-databases > backup.sql

# Exclude a database
mysqlpump -u root -p --exclude-databases=test,information_schema \
  --all-databases > backup.sql

# Compressed backup
mysqlpump -u root -p --compress-output=ZLIB --all-databases > backup.sql.gz

Third‑Party Backup Tools

Percona XtraBackup

Open‑source physical backup tool for InnoDB, supports hot backup.

Features:

Hot backup for InnoDB.

Incremental backups.

Fast backup and restore.

Compression and encryption.

Streaming backup.

Installation:

# CentOS/RHEL
yum install percona-xtrabackup-80

# Ubuntu/Debian
apt-get install percona-xtrabackup-80

Usage example:

# Full backup
xtrabackup --backup --target-dir=/backup/full --user=root --password=password

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full \
  --user=root --password=password

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

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

MySQL Enterprise Backup

Oracle’s commercial backup solution.

Features:

Hot and incremental backup.

Compression and encryption.

Point‑in‑time recovery.

Cloud storage integration.

Advanced monitoring and reporting.

Example:

# Full backup
mysqlbackup --user=root --password=password --backup-dir=/backup/full backup

# Incremental backup
mysqlbackup --user=root --password=password --backup-dir=/backup/inc1 \
  --incremental --incremental-base=dir:/backup/full backup

# Restore
mysqlbackup --backup-dir=/backup/full copy-back

mydumper / myloader

Multithreaded logical backup and restore utilities.

Features:

Parallel backup and restore.

Compression support.

Consistent snapshots.

Outputs multiple files for easier management.

Installation:

# CentOS/RHEL
yum install mydumper

# Ubuntu/Debian
apt-get install mydumper

Example:

# Backup
mydumper -u root -p password -h localhost -B database_name -c -o /backup/

# Restore
myloader -u root -p password -h localhost -B database_name -d /backup/

Backup Strategy Implementation

Scheduling

Automate backups with cron:

# Daily full backup at 02:00
0 2 * * * /usr/local/bin/mysql_backup.sh full >> /var/log/mysql_backup.log 2>&1

# Incremental every 4 hours
0 */4 * * * /usr/local/bin/mysql_backup.sh incremental >> /var/log/mysql_backup.log 2>&1

# Weekly cleanup on Sunday
0 3 * * 0 /usr/local/bin/mysql_backup_cleanup.sh >> /var/log/mysql_backup.log 2>&1

Backup script example

#!/bin/bash
# mysql_backup.sh
MYSQL_USER="backup_user"
MYSQL_PASSWORD="backup_password"
MYSQL_HOST="localhost"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql_backup.log"

mkdir -p $BACKUP_DIR
echo "$(date): Starting MySQL backup..." >> $LOG_FILE

BACKUP_FILE="$BACKUP_DIR/mysql_backup_$(date +%Y%m%d_%H%M%S).sql"
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST \
  --single-transaction --routines --triggers --events \
  --master-data=2 --all-databases > $BACKUP_FILE

if [ $? -eq 0 ]; then
  echo "$(date): Backup completed successfully: $BACKUP_FILE" >> $LOG_FILE
  gzip $BACKUP_FILE
  echo "$(date): Backup compressed: ${BACKUP_FILE}.gz" >> $LOG_FILE
else
  echo "$(date): Backup failed!" >> $LOG_FILE
  exit 1
fi

# Cleanup old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "$(date): Old backups cleaned up" >> $LOG_FILE
echo "$(date): Backup process completed" >> $LOG_FILE

Backup verification

Validate backup usability by restoring to a test database:

#!/bin/bash
# backup_verification.sh
BACKUP_FILE="/backup/mysql/latest_backup.sql.gz"
TEST_DB="backup_test"
MYSQL_USER="root"
MYSQL_PASSWORD="password"

# Create test database
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE IF NOT EXISTS $TEST_DB;"

# Restore
zcat $BACKUP_FILE | mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $TEST_DB

# Verify table count
TABLE_COUNT=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e \
  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB';" -s)

if [ $TABLE_COUNT -gt 0 ]; then
  echo "Backup verification successful: $TABLE_COUNT tables restored"
else
  echo "Backup verification failed: No tables found"
  exit 1
fi

# Clean up
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "DROP DATABASE $TEST_DB;"

Data Restoration Strategies

Full restore

Restore from a full backup:

# Stop MySQL
systemctl stop mysql

# Restore from mysqldump backup
mysql -u root -p < full_backup.sql

# Restore from XtraBackup
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

# Start MySQL
systemctl start mysql

Point‑in‑time restore

Combine a full backup with binary logs:

# Apply binary log by position
mysqlbinlog --start-position=154 --stop-position=1024 mysql-bin.000001 | mysql -u root -p

# Or by datetime
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
  --stop-datetime="2024-01-01 11:00:00" mysql-bin.000001 | mysql -u root -p

Incremental restore

Using XtraBackup incremental backups:

# Prepare full backup (log only)
xtrabackup --prepare --apply-log-only --target-dir=/backup/full

# Apply incremental
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
  --incremental-dir=/backup/inc1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
  --incremental-dir=/backup/inc2

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

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

Backup Storage and Management

Local storage

Advantages: fast access; risks: single point of failure. Recommendations include dedicated disks, RAID, health checks, and retention policies.

Remote storage

Provides better protection. Examples:

# Copy to remote server
scp backup.sql.gz backup_user@remote_server:/backup/mysql/

# Sync with rsync
rsync -avz /backup/mysql/ backup_user@remote_server:/backup/mysql/

# Upload to AWS S3
aws s3 cp backup.sql.gz s3://mysql-backup-bucket/$(date +%Y/%m/%d)/

Backup encryption

Encrypt sensitive data before storage:

# GPG encryption
mysqldump -u root -p --all-databases | gpg --cipher-algo AES256 \
  --compress-algo 1 --symmetric --output backup_encrypted.sql.gpg

# OpenSSL encryption
mysqldump -u root -p --all-databases | openssl enc -aes-256-cbc -salt \
  -out backup_encrypted.sql.enc -k encryption_password

Monitoring and Alerts

Backup monitoring

Script to verify recent backup size and send email alerts.

#!/bin/bash
BACKUP_DIR="/backup/mysql"
EXPECTED_SIZE=1000000
ALERT_EMAIL="[email protected]"

LATEST_BACKUP=$(find $BACKUP_DIR -name "*.sql.gz" -mtime -1 | head -1)

if [ -z "$LATEST_BACKUP" ]; then
  echo "No recent backup found!" | mail -s "MySQL Backup Alert" $ALERT_EMAIL
  exit 1
fi

BACKUP_SIZE=$(stat -c%s "$LATEST_BACKUP")
if [ $BACKUP_SIZE -lt $EXPECTED_SIZE ]; then
  echo "Backup file size is smaller than expected: $BACKUP_SIZE bytes" \
    | mail -s "MySQL Backup Size Alert" $ALERT_EMAIL
fi

echo "Backup monitoring completed: $LATEST_BACKUP ($BACKUP_SIZE bytes)"

Recovery testing

Automated test that restores a backup into a Docker MySQL instance and verifies table count.

#!/bin/bash
TEST_ENV="test_recovery"
BACKUP_FILE="/backup/mysql/latest_backup.sql.gz"
LOG_FILE="/var/log/recovery_test.log"

echo "$(date): Starting recovery test..." >> $LOG_FILE

# Start Docker container
docker run -d --name $TEST_ENV -e MYSQL_ROOT_PASSWORD=testpass mysql:8.0
sleep 30

# Create test database and restore
docker exec $TEST_ENV mysql -u root -ptestpass -e "CREATE DATABASE test_restore;"
zcat $BACKUP_FILE | docker exec -i $TEST_ENV mysql -u root -ptestpass test_restore

# Verify
TABLE_COUNT=$(docker exec $TEST_ENV mysql -u root -ptestpass -e \
  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='test_restore';" -s)

if [ $TABLE_COUNT -gt 0 ]; then
  echo "$(date): Recovery test successful: $TABLE_COUNT tables restored" >> $LOG_FILE
else
  echo "$(date): Recovery test failed!" >> $LOG_FILE
  echo "Recovery test failed!" | mail -s "MySQL Recovery Test Alert" [email protected]
fi

# Cleanup
docker stop $TEST_ENV
docker rm $TEST_ENV
echo "$(date): Recovery test completed" >> $LOG_FILE

Best Practices and Recommendations

Backup strategy best practices

Define clear backup policies based on RTO and RPO.

Implement multi‑layer backups (full, incremental, logs).

Store copies off‑site.

Perform regular verification.

Document procedures thoroughly.

Set up monitoring and alerting.

Performance optimization tips

Select the appropriate backup tool for data size and workload.

Schedule backups during low‑traffic windows.

Use parallel backup utilities.

Optimize network bandwidth and transfer protocols.

Employ high‑performance storage devices and suitable filesystems.

Security considerations

Restrict access to backup files.

Encrypt stored backups.

Use encrypted channels for transfer.

Maintain audit logs of backup and restore actions.

Apply the principle of least privilege for backup accounts.

Troubleshooting

Common backup issues

Lock timeout during backup

# Increase lock wait timeout
mysqldump --single-transaction --lock-wait-timeout=120 ...

Corrupted backup file

# Verify gzip integrity
gzip -t backup.sql.gz
# Test restore
mysql -u root -p --execute="SELECT 1" < backup.sql

Incremental backup failure

# Check binary log configuration
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -u root -p -e "SHOW BINARY LOGS;"

Restore issues

Permission errors

# Fix ownership and permissions
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql

InnoDB recovery problems

# Review InnoDB settings
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_%';"
# Adjust parameters such as innodb_log_file_size if needed

Conclusion

MySQL backup and recovery are essential for database management. By selecting suitable tools, defining robust policies, automating schedules, encrypting data, monitoring processes, and regularly testing restores, operations teams can ensure data safety and business continuity.