MySQL Database Backup Strategies

Introduction

Database backups are critical for any production server. MySQL and MariaDB offer several backup methods, each with different trade-offs between speed, consistency, and resource usage.

Method 1: mysqldump (Logical Backup)

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

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

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

The --single-transaction flag ensures a consistent snapshot for InnoDB tables without locking.

Method 2: mariadb-backup (Physical Backup)

sudo apt install -y mariadb-backup

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

# Prepare the backup (apply logs)
mariabackup --prepare --target-dir=/backup/full

Physical backups are much faster for large databases (100 GB+) compared to mysqldump.

Automated Backup Script

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
mkdir -p "$BACKUP_DIR"

for DB in $(mysql -u root -e "SHOW DATABASES" -s --skip-column-names | grep -v -E "^(information_schema|performance_schema|sys)$"); do
    mysqldump -u root --single-transaction "$DB" | gzip > "${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
done

# Delete backups older than 14 days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +14 -delete

Testing Your Backups

A backup is only useful if it can be restored. Periodically test by restoring to a temporary database:

mysql -u root -e "CREATE DATABASE test_restore"
gunzip < mydb_20260225.sql.gz | mysql -u root test_restore
mysql -u root -e "DROP DATABASE test_restore"