Implement MySQL backup automation with Percona XtraBackup and systemd timers

Install Percona XtraBackup

Add the Percona repository and install XtraBackup for hot MySQL backups.

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release enable-only tools release
sudo apt install -y percona-xtrabackup-80 qpress

sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo dnf install -y percona-xtrabackup-80 qpress

Create backup user and directories

Set up a dedicated MySQL user for backups and create secure backup directories.

sudo mysql -e "CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'SecureBackupPass123!';"
sudo mysql -e "GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO 'xtrabackup'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.keyring_component_status TO 'xtrabackup'@'localhost';"
sudo mysql -e "FLUSH PRIVILEGES;"

sudo mkdir -p /backup/mysql/{full,incremental,logs}
sudo mkdir -p /backup/mysql/archive
sudo useradd -r -s /bin/false backup
sudo chown -R backup:backup /backup
sudo chmod 750 /backup/mysql

Create backup configuration file

Store backup credentials and settings in a secure configuration file.

[xtrabackup]
user=xtrabackup
password=SecureBackupPass123!
host=localhost
port=3306
socket=/var/run/mysqld/mysqld.sock

sudo chown root:backup /etc/mysql/backup.cnf
sudo chmod 640 /etc/mysql/backup.cnf

Create full backup script

Build a comprehensive backup script with compression, encryption, and verification.

#!/bin/bash

MySQL Full Backup with XtraBackup
Usage: mysql-backup-full.sh

set -euo pipefail

Configuration
BACKUP_DIR="/backup/mysql"
LOG_FILE="$BACKUP_DIR/logs/backup-$(date +%Y%m%d_%H%M%S).log"
DATE_FORMAT=$(date +%Y%m%d_%H%M%S)
FULL_BACKUP_DIR="$BACKUP_DIR/full/mysql-full-$DATE_FORMAT"
CONFIG_FILE="/etc/mysql/backup.cnf"
RETENTION_DAYS=7
COMPRESSION="--compress=lz4"
ENCRYPTION_KEY="/etc/mysql/backup.key"

Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

Error handling
cleanup() {
    if [ -d "$FULL_BACKUP_DIR" ]; then
        log "ERROR: Cleaning up incomplete backup directory"
        rm -rf "$FULL_BACKUP_DIR"
    fi
    exit 1
}
trap cleanup ERR

Generate encryption key if it doesn't exist
if [ ! -f "$ENCRYPTION_KEY" ]; then
    log "Generating encryption key"
    openssl rand -base64 32 > "$ENCRYPTION_KEY"
    chmod 600 "$ENCRYPTION_KEY"
    chown backup:backup "$ENCRYPTION_KEY"
fi

log "Starting full MySQL backup"

Create backup directory
mkdir -p "$FULL_BACKUP_DIR"
chown backup:backup "$FULL_BACKUP_DIR"

Perform backup
log "Creating XtraBackup"
xtrabackup --defaults-file="$CONFIG_FILE" \
    --backup \
    --target-dir="$FULL_BACKUP_DIR" \
    $COMPRESSION \
    --encrypt=AES256 \
    --encrypt-key-file="$ENCRYPTION_KEY" \
    --stream=xbstream | \
    gzip > "$BACKUP_DIR/full/mysql-full-$DATE_FORMAT.xbstream.gz"

Remove temporary directory
rm -rf "$FULL_BACKUP_DIR"

Verify backup integrity
log "Verifying backup integrity"
if gunzip -t "$BACKUP_DIR/full/mysql-full-$DATE_FORMAT.xbstream.gz"; then
    log "Backup compression verification passed"
else
    log "ERROR: Backup compression verification failed"
    exit 1
fi

Calculate backup size
BACKUP_SIZE=$(du -h "$BACKUP_DIR/full/mysql-full-$DATE_FORMAT.xbstream.gz" | cut -f1)
log "Backup completed successfully. Size: $BACKUP_SIZE"

Clean old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR/full" -name "mysql-full-*.xbstream.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR/logs" -name "backup-*.log" -mtime +$RETENTION_DAYS -delete

log "Full backup completed: mysql-full-$DATE_FORMAT.xbstream.gz"

Send notification (optional)
if command -v mail >/dev/null 2>&1; then
    echo "MySQL backup completed successfully at $(date)" | mail -s "MySQL Backup Success" root
fi

sudo chmod 750 /usr/local/bin/mysql-backup-full.sh
sudo chown backup:backup /usr/local/bin/mysql-backup-full.sh

Create incremental backup script

Set up incremental backups for space-efficient daily backups.

#!/bin/bash

MySQL Incremental Backup with XtraBackup
Usage: mysql-backup-incremental.sh

set -euo pipefail

Configuration
BACKUP_DIR="/backup/mysql"
LOG_FILE="$BACKUP_DIR/logs/incremental-$(date +%Y%m%d_%H%M%S).log"
DATE_FORMAT=$(date +%Y%m%d_%H%M%S)
INC_BACKUP_DIR="$BACKUP_DIR/incremental/mysql-inc-$DATE_FORMAT"
CONFIG_FILE="/etc/mysql/backup.cnf"
RETENTION_DAYS=7
COMPRESSION="--compress=lz4"
ENCRYPTION_KEY="/etc/mysql/backup.key"

Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

Error handling
cleanup() {
    if [ -d "$INC_BACKUP_DIR" ]; then
        log "ERROR: Cleaning up incomplete backup directory"
        rm -rf "$INC_BACKUP_DIR"
    fi
    exit 1
}
trap cleanup ERR

Find the latest full backup
LATEST_FULL=$(find "$BACKUP_DIR/full" -name "mysql-full-*.xbstream.gz" -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2)

if [ -z "$LATEST_FULL" ]; then
    log "ERROR: No full backup found. Run full backup first."
    exit 1
fi

log "Starting incremental MySQL backup based on: $(basename "$LATEST_FULL")"

Extract base backup for LSN
TEMP_DIR=$(mktemp -d)
gunzip -c "$LATEST_FULL" | xbstream -x -C "$TEMP_DIR"
xtrabackup --decrypt=AES256 --encrypt-key-file="$ENCRYPTION_KEY" --target-dir="$TEMP_DIR"
xtrabackup --decompress --target-dir="$TEMP_DIR"

Create incremental backup
mkdir -p "$INC_BACKUP_DIR"
chown backup:backup "$INC_BACKUP_DIR"

log "Creating incremental backup"
xtrabackup --defaults-file="$CONFIG_FILE" \
    --backup \
    --target-dir="$INC_BACKUP_DIR" \
    --incremental-basedir="$TEMP_DIR" \
    $COMPRESSION \
    --encrypt=AES256 \
    --encrypt-key-file="$ENCRYPTION_KEY" \
    --stream=xbstream | \
    gzip > "$BACKUP_DIR/incremental/mysql-inc-$DATE_FORMAT.xbstream.gz"

Cleanup temporary directory
rm -rf "$TEMP_DIR"
rm -rf "$INC_BACKUP_DIR"

Verify backup
log "Verifying incremental backup"
if gunzip -t "$BACKUP_DIR/incremental/mysql-inc-$DATE_FORMAT.xbstream.gz"; then
    BACKUP_SIZE=$(du -h "$BACKUP_DIR/incremental/mysql-inc-$DATE_FORMAT.xbstream.gz" | cut -f1)
    log "Incremental backup completed successfully. Size: $BACKUP_SIZE"
else
    log "ERROR: Incremental backup verification failed"
    exit 1
fi

Clean old incremental backups
log "Cleaning incremental backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR/incremental" -name "mysql-inc-*.xbstream.gz" -mtime +$RETENTION_DAYS -delete

log "Incremental backup completed: mysql-inc-$DATE_FORMAT.xbstream.gz"

sudo chmod 750 /usr/local/bin/mysql-backup-incremental.sh
sudo chown backup:backup /usr/local/bin/mysql-backup-incremental.sh

Create systemd service files

Set up systemd services for both full and incremental backups.

[Unit]
Description=MySQL Full Backup with XtraBackup
Wants=network-online.target
After=network-online.target mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=backup
Group=backup
ExecStart=/usr/local/bin/mysql-backup-full.sh
StandardOutput=journal
StandardError=journal
TimeoutStartSec=3600
PrivateTmp=true
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/backup

[Install]
WantedBy=multi-user.target

[Unit]
Description=MySQL Incremental Backup with XtraBackup
Wants=network-online.target
After=network-online.target mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=backup
Group=backup
ExecStart=/usr/local/bin/mysql-backup-incremental.sh
StandardOutput=journal
StandardError=journal
TimeoutStartSec=1800
PrivateTmp=true
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/backup

[Install]
WantedBy=multi-user.target

Configure systemd timers

Schedule automatic backups with systemd timers for reliable execution.

[Unit]
Description=Run MySQL Full Backup Weekly
Requires=mysql-backup-full.service

[Timer]
Run every Sunday at 2:00 AM
OnCalendar=Sun --* 02:00:00
Run on boot if missed
Persistent=true
Random delay to avoid system load spikes
RandomizedDelaySec=1800

[Install]
WantedBy=timers.target

[Unit]
Description=Run MySQL Incremental Backup Daily
Requires=mysql-backup-incremental.service

[Timer]
Run daily at 3:00 AM (except Sunday when full backup runs)
OnCalendar=Mon,Tue,Wed,Thu,Fri,Sat --* 03:00:00
Persistent=true
RandomizedDelaySec=900

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable mysql-backup-full.timer
sudo systemctl enable mysql-backup-incremental.timer
sudo systemctl start mysql-backup-full.timer
sudo systemctl start mysql-backup-incremental.timer

Create backup restoration script

Build a restoration script to recover from backups when needed.

#!/bin/bash

MySQL Restore from XtraBackup
Usage: mysql-restore.sh  [incremental_files...]

set -euo pipefail

if [ $# -lt 1 ]; then
    echo "Usage: $0  [incremental1.xbstream.gz] [incremental2.xbstream.gz] ..."
    echo "Example: $0 /backup/mysql/full/mysql-full-20241201_020000.xbstream.gz"
    exit 1
fi

FULL_BACKUP="$1"
shift
INCREMENTALS=("$@")

ENCRYPTION_KEY="/etc/mysql/backup.key"
RESTORE_DIR="/tmp/mysql-restore-$(date +%Y%m%d_%H%M%S)"
MYSQL_DATADIR="/var/lib/mysql"

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1"
}

Check if MySQL is running
if systemctl is-active --quiet mysql; then
    log "ERROR: MySQL is running. Stop MySQL before restoration:"
    log "sudo systemctl stop mysql"
    exit 1
fi

Verify backup files exist
if [ ! -f "$FULL_BACKUP" ]; then
    log "ERROR: Full backup file not found: $FULL_BACKUP"
    exit 1
fi

for inc in "${INCREMENTALS[@]}"; do
    if [ ! -f "$inc" ]; then
        log "ERROR: Incremental backup file not found: $inc"
        exit 1
    fi
done

log "Starting MySQL restoration from: $(basename "$FULL_BACKUP")"

Create restore directory
mkdir -p "$RESTORE_DIR"

Extract and decrypt full backup
log "Extracting full backup"
gunzip -c "$FULL_BACKUP" | xbstream -x -C "$RESTORE_DIR"
xtrabackup --decrypt=AES256 --encrypt-key-file="$ENCRYPTION_KEY" --target-dir="$RESTORE_DIR"
xtrabackup --decompress --target-dir="$RESTORE_DIR"

Apply incremental backups if provided
for inc in "${INCREMENTALS[@]}"; do
    log "Applying incremental backup: $(basename "$inc")"
    INC_DIR="$RESTORE_DIR/incremental-$(basename "$inc" .xbstream.gz)"
    mkdir -p "$INC_DIR"
    gunzip -c "$inc" | xbstream -x -C "$INC_DIR"
    xtrabackup --decrypt=AES256 --encrypt-key-file="$ENCRYPTION_KEY" --target-dir="$INC_DIR"
    xtrabackup --decompress --target-dir="$INC_DIR"
    xtrabackup --prepare --target-dir="$RESTORE_DIR" --incremental-dir="$INC_DIR"
    rm -rf "$INC_DIR"
done

Prepare the backup
log "Preparing backup for restoration"
xtrabackup --prepare --target-dir="$RESTORE_DIR"

Backup current data directory
if [ -d "$MYSQL_DATADIR" ]; then
    log "Backing up current MySQL data directory"
    sudo mv "$MYSQL_DATADIR" "${MYSQL_DATADIR}.backup.$(date +%Y%m%d_%H%M%S)"
fi

Copy restored data
log "Copying restored data to MySQL directory"
sudo mkdir -p "$MYSQL_DATADIR"
sudo cp -R "$RESTORE_DIR"/* "$MYSQL_DATADIR"/
sudo chown -R mysql:mysql "$MYSQL_DATADIR"
sudo chmod 750 "$MYSQL_DATADIR"

Cleanup
rm -rf "$RESTORE_DIR"

log "Restoration completed. You can now start MySQL:"
log "sudo systemctl start mysql"
log ""
log "Verify the restoration:"
log "sudo mysql -e 'SHOW DATABASES;'"
log "sudo mysql -e 'SELECT NOW();'"

sudo chmod 750 /usr/local/bin/mysql-restore.sh
sudo chown root:backup /usr/local/bin/mysql-restore.sh

Install backup monitoring dependencies

Install tools for backup verification and monitoring integration.

sudo apt install -y mailutils curl jq

sudo dnf install -y mailx curl jq

Create backup verification script

Add automated backup testing to ensure restore capability.

#!/bin/bash

MySQL Backup Verification Script
Tests that backups can be restored successfully

set -euo pipefail

BACKUP_DIR="/backup/mysql"
TEST_DIR="/tmp/backup-test-$(date +%Y%m%d_%H%M%S)"
ENCRYPTION_KEY="/etc/mysql/backup.key"
LOG_FILE="$BACKUP_DIR/logs/verify-$(date +%Y%m%d_%H%M%S).log"

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

Find latest full backup
LATEST_BACKUP=$(find "$BACKUP_DIR/full" -name "mysql-full-*.xbstream.gz" -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2)

if [ -z "$LATEST_BACKUP" ]; then
    log "ERROR: No backup found to verify"
    exit 1
fi

log "Verifying backup: $(basename "$LATEST_BACKUP")"

Test backup extraction
mkdir -p "$TEST_DIR"
if gunzip -c "$LATEST_BACKUP" | xbstream -x -C "$TEST_DIR" && \
   xtrabackup --decrypt=AES256 --encrypt-key-file="$ENCRYPTION_KEY" --target-dir="$TEST_DIR" && \
   xtrabackup --decompress --target-dir="$TEST_DIR" && \
   xtrabackup --prepare --target-dir="$TEST_DIR"; then
    log "Backup verification PASSED: $(basename "$LATEST_BACKUP")"
    RESULT=0
else
    log "Backup verification FAILED: $(basename "$LATEST_BACKUP")"
    RESULT=1
fi

Cleanup
rm -rf "$TEST_DIR"

Send alert if verification failed
if [ $RESULT -ne 0 ] && command -v mail >/dev/null 2>&1; then
    echo "MySQL backup verification failed for $(basename "$LATEST_BACKUP")" | \
    mail -s "ALERT: MySQL Backup Verification Failed" root
fi

exit $RESULT

sudo chmod 750 /usr/local/bin/mysql-backup-verify.sh
sudo chown backup:backup /usr/local/bin/mysql-backup-verify.sh

Schedule backup verification

Create a systemd timer to regularly verify backup integrity.

[Unit]
Description=MySQL Backup Verification
After=mysql-backup-full.service

[Service]
Type=oneshot
User=backup
Group=backup
ExecStart=/usr/local/bin/mysql-backup-verify.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=Run MySQL Backup Verification Weekly
Requires=mysql-backup-verify.service

[Timer]
Run every Monday at 9:00 AM (day after full backup)
OnCalendar=Mon --* 09:00:00
Persistent=true

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable mysql-backup-verify.timer
sudo systemctl start mysql-backup-verify.timer

Configure S3-compatible remote storage

Sync backups to remote storage for disaster recovery.

sudo apt install -y awscli

sudo dnf install -y awscli

#!/bin/bash

Sync MySQL backups to S3-compatible storage

set -euo pipefail

BACKUP_DIR="/backup/mysql"
S3_BUCKET="s3://your-backup-bucket/mysql-backups/"
AWS_CONFIG="/home/backup/.aws/config"
LOG_FILE="$BACKUP_DIR/logs/sync-$(date +%Y%m%d_%H%M%S).log"
RETENTION_DAYS=30

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

if [ ! -f "$AWS_CONFIG" ]; then
    log "AWS configuration not found. Run: aws configure"
    exit 1
fi

log "Syncing backups to remote storage"

Sync full backups
aws s3 sync "$BACKUP_DIR/full/" "${S3_BUCKET}full/" \
    --exclude "*" \
    --include "mysql-full-*.xbstream.gz" \
    --storage-class STANDARD_IA

Sync incremental backups
aws s3 sync "$BACKUP_DIR/incremental/" "${S3_BUCKET}incremental/" \
    --exclude "*" \
    --include "mysql-inc-*.xbstream.gz" \
    --storage-class STANDARD_IA

Clean old remote backups
log "Cleaning remote backups older than $RETENTION_DAYS days"
CUTOFF_DATE=$(date -d "$RETENTION_DAYS days ago" +%Y%m%d)

List and delete old backups
aws s3 ls "${S3_BUCKET}full/" | while read -r line; do
    FILE=$(echo "$line" | awk '{print $4}')
    if [[ "$FILE" =~ mysql-full-([0-9]{8})_ ]]; then
        FILE_DATE="${BASH_REMATCH[1]}"
        if [ "$FILE_DATE" -lt "$CUTOFF_DATE" ]; then
            aws s3 rm "${S3_BUCKET}full/$FILE"
            log "Deleted remote backup: $FILE"
        fi
    fi
done

log "Remote backup sync completed"

sudo chmod 750 /usr/local/bin/mysql-backup-sync.sh
sudo chown backup:backup /usr/local/bin/mysql-backup-sync.sh