MySQL Backup and Recovery Strategies

Backup Types

mysqldump

Single Database

mysqldump -u root -p mydb > mydb_backup.sql

# Compressed
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

All Databases

mysqldump -u root -p --all-databases --single-transaction > all_dbs.sql

Key Flags

mysqldump \
  --single-transaction \     # Consistent snapshot without locking (InnoDB)
  --routines \               # Include stored procedures
  --triggers \               # Include triggers
  --events \                 # Include scheduled events
  --quick \                  # Don't buffer results in memory
  --set-gtid-purged=OFF \    # For non-replication backups
  mydb > backup.sql

Automated Daily Backups

#!/bin/bash
# /opt/backup/mysql-daily.sh

BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=14
DATE=$(date +%Y%m%d_%H%M)

mkdir -p "$BACKUP_DIR"

# Dump all databases
mysqldump --all-databases --single-transaction --routines --triggers \
  | gzip > "${BACKUP_DIR}/all_${DATE}.sql.gz"

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

echo "Backup completed: all_${DATE}.sql.gz ($(du -h ${BACKUP_DIR}/all_${DATE}.sql.gz | cut -f1))"

Restoring

# From plain SQL
mysql -u root -p mydb < mydb_backup.sql

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

# Restore specific table
mysql -u root -p mydb -e "source /backup/mydb_backup.sql"

Point-in-Time Recovery

Requires binary logging:

# /etc/mysql/mysql.conf.d/mysqld.cnf
log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 14
max_binlog_size = 100M

# Restore to specific point in time
mysqlbinlog --stop-datetime="2026-03-15 14:30:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

Verification

Danger A backup you haven't tested is not a backup. Schedule monthly restore tests to a separate server.

# Quick integrity check
gunzip -t backup.sql.gz && echo "File OK"

# Full restore test
mysql -u root -p -e "CREATE DATABASE backup_test;"
gunzip < backup.sql.gz | mysql -u root -p backup_test
mysql -u root -p -e "SELECT COUNT(*) FROM backup_test.users;"
mysql -u root -p -e "DROP DATABASE backup_test;"