Backup & Disaster Recovery Strategies for MySQL/MariaDB

Apr 22, 2025

—

In the world of database management, a solid backup and disaster recovery strategy isn’t just good practice—it’s essential for business continuity. For organizations relying on MySQL or MariaDB databases, implementing robust backup procedures and having tested recovery plans can mean the difference between minor disruption and catastrophic data loss.

This guide explores comprehensive backup and disaster recovery strategies for MySQL and MariaDB databases, covering everything from basic backup types to advanced recovery techniques.

Understanding Backup Types

Before diving into implementation, it’s important to understand the different types of backups available for MySQL/MariaDB:

Logical Backups

Logical backups store data as SQL statements that can recreate your database objects and data. They’re flexible but typically slower to restore than physical backups.

Pros:

Human-readable format
Can selectively restore specific tables or data
Database version independence
Smaller file size (especially when compressed)

Cons:

Slower restoration process
Higher CPU usage during backup

Physical Backups

Physical backups copy the actual database files. They’re faster to restore but less flexible than logical backups.

Pros:

Faster backup and restoration
Less CPU overhead during backup
Complete snapshot of the database

Cons:

Database version dependency
Larger file size
Less selective restoration options

Full vs. Incremental Backups

Full backups: Capture the entire database
Incremental backups: Capture only changes since the last backup
Differential backups: Capture changes since the last full backup

Essential Backup Tools

mysqldump (Logical Backup)

The classic and most widely used backup tool for MySQL/MariaDB:

# Basic backup of a single database
mysqldump -u username -p database_name > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > full_backup.sql

# Backup with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

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

MySQL Enterprise Backup / Percona XtraBackup (Physical Backup)

For physical backups, these tools offer hot backup capabilities:

# Percona XtraBackup full backup
xtrabackup --backup --target-dir=/path/to/backup

# Incremental backup
xtrabackup --backup --target-dir=/path/to/incremental --incremental-basedir=/path/to/full-backup

mariabackup (MariaDB Physical Backup)

MariaDB’s native physical backup tool:

# Full backup
mariabackup --backup --target-dir=/path/to/backup --user=username --password=password

# Prepare the backup for restoration
mariabackup --prepare --target-dir=/path/to/backup

mysqlpump (Enhanced Logical Backup)

An improved version of mysqldump with parallel processing capabilities:

# Parallel backup with 4 threads
mysqlpump --default-parallelism=4 --user=username --password=password database_name > backup.sql

Implementing a Comprehensive Backup Strategy

1. Determine Recovery Point Objective (RPO)

Your RPO defines how much data loss is acceptable in the event of a disaster. This will guide your backup frequency:

Minimal data loss (minutes): Binary log + frequent transaction log backups
Moderate data loss (hours): Daily full backups + binary logs
Larger data loss tolerance (days): Weekly full backups + daily incremental backups

2. Determine Recovery Time Objective (RTO)

Your RTO defines how quickly you need to restore service after a disaster:

Immediate recovery (minutes): Maintain hot standby replicas
Fast recovery (hours): Keep recent physical backups readily available
Extended recovery (days): Standard backup/restoration procedures

3. Create a Backup Schedule

Based on your RPO and RTO, design a backup schedule. Here’s an example for a production database:

# Daily full backup at 1 AM
0 1 * * * /usr/bin/mysqldump -u backup_user -p'password' --all-databases | gzip > /backup/full_$(date +\%Y\%m\%d).sql.gz

# Binary log backup every hour
0 * * * * /usr/bin/mysqlbinlog --read-from-remote-server --raw --host=localhost --user=backup_user --password=password mysql-bin.* --result-file=/backup/binlog/

4. Enable Binary Logging

Binary logs record all changes to your data, enabling point-in-time recovery:

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

5. Verify Backups

Always verify that your backups are valid and can be restored:

# Test MySQL dump file
mysql -u root -p < backup.sql

# For compressed backups
gunzip < backup.sql.gz | mysql -u root -p

Consider implementing automated backup verification:

#!/bin/bash
# Simple backup verification script
BACKUP_FILE=$1
TEST_DB="backup_test"

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

# Restore backup to test database
gunzip < $BACKUP_FILE | mysql -u root -p $TEST_DB

# Check for errors
if [ $? -eq 0 ]; then
    echo "Backup verification successful"
    mysql -u root -p -e "DROP DATABASE $TEST_DB;"
else
    echo "Backup verification failed!"
    exit 1
fi

6. Implement Off-site Storage

Never keep backups only on the same server as your database:

# Copy backup to remote storage
rsync -av /backup/ remote_server:/backup/

# Or use cloud storage (AWS S3 example)
aws s3 cp /backup/full_$(date +\%Y\%m\%d).sql.gz s3://my-database-backups/

Disaster Recovery Techniques

Basic Restoration

Restoring from a logical backup:

# Restore a full backup
mysql -u root -p < backup.sql

# Restore specific databases
mysql -u root -p database_name < database_backup.sql

Restoring from a physical backup (XtraBackup example):

# Stop MySQL server
systemctl stop mysql

# Prepare the backup
xtrabackup --prepare --target-dir=/path/to/backup

# Restore the backup
xtrabackup --copy-back --target-dir=/path/to/backup

# Fix permissions
chown -R mysql:mysql /var/lib/mysql

# Start MySQL server
systemctl start mysql

Point-in-Time Recovery

To restore to a specific moment in time:

# First restore the full backup
mysql -u root -p < full_backup.sql

# Then apply binary logs up to a specific point
mysqlbinlog --stop-datetime="2023-04-15 14:30:00" /path/to/mysql-bin.000001 | mysql -u root -p

Replication-Based Recovery

Setting up a replica for disaster recovery:

-- On primary server
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- Get binary log position
SHOW MASTER STATUS;

-- On replica server
CHANGE MASTER TO
  MASTER_HOST='primary_server_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123456;

START SLAVE;

Delayed Replication for Human Error Protection

Delayed replication can save you from accidental data deletion:

-- Set up a replica with 1 hour delay
CHANGE MASTER TO
  MASTER_DELAY=3600;
START SLAVE;

If someone accidentally drops a table, you have one hour to stop the replica before it applies the same change.

Advanced Backup Strategies

Automating with Orchestration Tools

Using tools like Percona Backup for MongoDB (PBM) or custom scripts:

#!/usr/bin/env python3
# Example backup orchestration script
import subprocess
import datetime
import os

BACKUP_DIR = "/backup"
DB_USER = "backup_user"
DB_PASS = "secure_password"
RETENTION_DAYS = 7

def create_backup():
    date_str = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_file = f"{BACKUP_DIR}/mysql_backup_{date_str}.sql.gz"
    
    cmd = f"mysqldump -u {DB_USER} -p{DB_PASS} --all-databases | gzip > {backup_file}"
    subprocess.run(cmd, shell=True, check=True)
    return backup_file

def cleanup_old_backups():
    cutoff = datetime.datetime.now() - datetime.timedelta(days=RETENTION_DAYS)
    for file in os.listdir(BACKUP_DIR):
        if file.startswith("mysql_backup_"):
            file_path = os.path.join(BACKUP_DIR, file)
            file_date_str = file.split("_")[2].split(".")[0]
            file_date = datetime.datetime.strptime(file_date_str, "%Y%m%d_%H%M%S")
            if file_date < cutoff:
                os.remove(file_path)
                print(f"Removed old backup: {file}")

if __name__ == "__main__":
    backup_file = create_backup()
    print(f"Created backup: {backup_file}")
    cleanup_old_backups()

Encrypting Backups

Always encrypt backups that contain sensitive data:

# Create encrypted backup
mysqldump -u root -p --all-databases | openssl enc -aes-256-cbc -salt -out backup.sql.enc -pass pass:"secure_passphrase"

# Decrypt and restore
openssl enc -d -aes-256-cbc -in backup.sql.enc -pass pass:"secure_passphrase" | mysql -u root -p

Database Firedrills

Regularly practice disaster recovery scenarios:

Schedule quarterly recovery tests
Document the exact recovery procedures
Measure recovery time and identify bottlenecks
Update procedures based on findings

High Availability Solutions

Primary-Replica Setup

Basic high availability with one or more replicas:

# Primary server my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

# Replica server my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay-bin
read_only=1

MySQL Group Replication

For automated failover and high availability:

[mysqld]
# Group replication settings
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot=OFF
group_replication_local_address="192.168.1.10:33061"
group_replication_group_seeds="192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061"
group_replication_bootstrap_group=OFF

MariaDB Galera Cluster

For multi-master synchronous replication:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.11,192.168.1.12"
wsrep_cluster_name="my_galera_cluster"
wsrep_node_address="192.168.1.10"
wsrep_node_name="node1"
wsrep_sst_method=mariabackup
wsrep_sst_auth=sst_user:password

Building a Comprehensive Disaster Recovery Plan

1. Document Infrastructure

Create detailed documentation of your database environment:

Server configurations
Database schemas
User permissions
Replication setup
Network configuration

2. Define Recovery Scenarios

Prepare for different types of disasters:

Hardware failure
Data corruption
Accidental data deletion
Ransomware/malicious attacks
Natural disasters affecting data centers

3. Establish Communication Protocols

Define who needs to be notified and how:

Internal team communication
Management notifications
Customer communications for extended outages

4. Create Step-by-Step Recovery Procedures

For each scenario, document detailed recovery steps:

# Example procedure for complete server failure
1. Provision new server with same specifications
2. Install MySQL/MariaDB with matching version
3. Copy configuration files from backup
4. Restore most recent full backup
5. Apply binary logs for point-in-time recovery
6. Verify data integrity
7. Update DNS/load balancers to point to new server
8. Verify application connectivity

5. Define Recovery Metrics

Establish clear metrics to evaluate your recovery:

Recovery Time Actual (RTA)
Data loss measurement
Application performance post-recovery

Conclusion

Implementing a robust backup and disaster recovery strategy for MySQL/MariaDB requires careful planning, appropriate tools, and regular testing. By combining frequent backups, point-in-time recovery capabilities, and potentially high-availability solutions, you can minimize both data loss and downtime when disaster strikes.

Remember these key principles:

Backup regularly and verify backup integrity
Store backups in multiple locations
Test your recovery procedures before you need them
Document everything
Automate where possible
Monitor backup processes

With these strategies in place, you can approach database disasters with confidence, knowing you have the tools and processes to recover quickly and completely.