MySQL Backup Automation with XtraBackup & systemd

Set up automated MySQL hot backups with Percona XtraBackup, systemd timers, compression, and encryption. Configure backup verification, retention policies, and restoration procedures for production-ready database backup automation.

Prerequisites

MySQL 8.0 or newer installed
Root access to the server
At least 2GB free disk space for backups

What this solves

MySQL databases require consistent, automated backups that don't interrupt service. Percona XtraBackup creates hot backups without locking your database, while systemd timers provide reliable scheduling. This setup gives you compressed, encrypted backups with automatic verification and configurable retention policies.

Step-by-step configuration

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 

set -euo pipefail

if [ $# -lt 1 ]; then
    echo "Usage: $0

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

Verify your setup

Test the backup system and verify all components are working correctly.

# Check systemd timers are active
sudo systemctl list-timers | grep mysql-backup

# Test full backup manually
sudo systemctl start mysql-backup-full.service
sudo systemctl status mysql-backup-full.service

# Check backup files
ls -la /backup/mysql/full/
ls -la /backup/mysql/logs/

# Verify XtraBackup installation
xtrabackup --version

# Check MySQL backup user
sudo mysql -e "SELECT User, Host FROM mysql.user WHERE User='xtrabackup';"

# Test backup directory permissions
sudo -u backup ls /backup/mysql/

Note: Your first backup will be a full backup. Incremental backups will only work after a successful full backup exists.

Configure backup verification

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

Remote backup storage

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 back

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Configuration
MYSQL_BACKUP_USER="xtrabackup"
MYSQL_BACKUP_PASS=""
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS="7"

# Usage message
usage() {
    echo "Usage: $0 [--password PASSWORD] [--retention-days DAYS]"
    echo "Options:"
    echo "  --password PASSWORD     Set MySQL backup user password (default: auto-generated)"
    echo "  --retention-days DAYS   Backup retention in days (default: 7)"
    exit 1
}

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --password)
            MYSQL_BACKUP_PASS="$2"
            shift 2
            ;;
        --retention-days)
            RETENTION_DAYS="$2"
            shift 2
            ;;
        -h|--help)
            usage
            ;;
        *)
            echo -e "${RED}Unknown option: $1${NC}"
            usage
            ;;
    esac
done

# Logging functions
log_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

# Cleanup on error
cleanup() {
    log_error "Installation failed. Cleaning up..."
    systemctl stop mysql-backup.timer 2>/dev/null || true
    systemctl disable mysql-backup.timer 2>/dev/null || true
    rm -f /etc/systemd/system/mysql-backup.{service,timer}
    rm -f /usr/local/bin/mysql-backup-full.sh
    rm -f /etc/mysql/backup.cnf
    exit 1
}
trap cleanup ERR

# Check prerequisites
echo "[1/9] Checking prerequisites..."
if [[ $EUID -ne 0 ]]; then
    log_error "This script must be run as root"
    exit 1
fi

# Auto-detect distro
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update"
            MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
            MYSQL_CONFIG_DIR="/etc/mysql"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora) 
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf check-update || true"
            MYSQL_SOCKET="/var/lib/mysql/mysql.sock"
            MYSQL_CONFIG_DIR="/etc/mysql"
            ;;
        amzn) 
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum check-update || true"
            MYSQL_SOCKET="/var/lib/mysql/mysql.sock"
            MYSQL_CONFIG_DIR="/etc/mysql"
            ;;
        *) 
            log_error "Unsupported distro: $ID"
            exit 1
            ;;
    esac
else
    log_error "Cannot detect distribution"
    exit 1
fi

log_info "Detected distribution: $ID"

# Check if MySQL is running
if ! systemctl is-active --quiet mysql && ! systemctl is-active --quiet mysqld && ! systemctl is-active --quiet mariadb; then
    log_error "MySQL/MariaDB is not running. Please start it first."
    exit 1
fi

echo "[2/9] Installing Percona XtraBackup..."
$PKG_UPDATE

if [[ "$PKG_MGR" == "apt" ]]; then
    wget -q https://repo.percona.com/apt/percona-release_latest.generic_all.deb -O /tmp/percona-release.deb
    dpkg -i /tmp/percona-release.deb
    $PKG_UPDATE
    percona-release enable-only tools release
    $PKG_INSTALL percona-xtrabackup-80 qpress openssl
    rm -f /tmp/percona-release.deb
else
    $PKG_INSTALL https://repo.percona.com/yum/percona-release-latest.noarch.rpm
    percona-release enable-only tools release
    $PKG_INSTALL percona-xtrabackup-80 qpress openssl
fi

echo "[3/9] Creating backup user and directories..."

# Generate password if not provided
if [[ -z "$MYSQL_BACKUP_PASS" ]]; then
    MYSQL_BACKUP_PASS=$(openssl rand -base64 24)
    log_info "Generated backup user password: $MYSQL_BACKUP_PASS"
fi

# Create MySQL backup user
mysql -e "DROP USER IF EXISTS '${MYSQL_BACKUP_USER}'@'localhost';" 2>/dev/null || true
mysql -e "CREATE USER '${MYSQL_BACKUP_USER}'@'localhost' IDENTIFIED BY '${MYSQL_BACKUP_PASS}';"
mysql -e "GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO '${MYSQL_BACKUP_USER}'@'localhost';"
mysql -e "GRANT SELECT ON performance_schema.log_status TO '${MYSQL_BACKUP_USER}'@'localhost';"
mysql -e "GRANT SELECT ON performance_schema.keyring_component_status TO '${MYSQL_BACKUP_USER}'@'localhost';" 2>/dev/null || true
mysql -e "FLUSH PRIVILEGES;"

# Create backup directories
mkdir -p $BACKUP_DIR/{full,incremental,logs,archive}
useradd -r -s /bin/false backup 2>/dev/null || true
chown -R backup:backup $BACKUP_DIR
chmod 750 $BACKUP_DIR
chmod -R 750 $BACKUP_DIR/*

echo "[4/9] Creating backup configuration..."
mkdir -p $MYSQL_CONFIG_DIR

cat > $MYSQL_CONFIG_DIR/backup.cnf << EOF
[xtrabackup]
user=$MYSQL_BACKUP_USER
password=$MYSQL_BACKUP_PASS
host=localhost
port=3306
socket=$MYSQL_SOCKET
EOF

chown root:backup $MYSQL_CONFIG_DIR/backup.cnf
chmod 640 $MYSQL_CONFIG_DIR/backup.cnf

echo "[5/9] Creating backup script..."
cat > /usr/local/bin/mysql-backup-full.sh << 'EOF'
#!/bin/bash
set -euo pipefail

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"

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

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

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"

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

log "Creating XtraBackup"
sudo -u backup 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"

rm -rf "$FULL_BACKUP_DIR"

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

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

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

log "Backup process completed"
EOF

chmod 755 /usr/local/bin/mysql-backup-full.sh
chown root:root /usr/local/bin/mysql-backup-full.sh

echo "[6/9] Creating systemd service..."
cat > /etc/systemd/system/mysql-backup.service << EOF
[Unit]
Description=MySQL Full Backup with XtraBackup
After=mysql.service mysqld.service mariadb.service
Requires=mysql.service

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

echo "[7/9] Creating systemd timer..."
cat > /etc/systemd/system/mysql-backup.timer << EOF
[Unit]
Description=Run MySQL backup daily at 2 AM
Requires=mysql-backup.service

[Timer]
OnCalendar=daily
RandomizedDelaySec=30m
Persistent=true

[Install]
WantedBy=timers.target
EOF

echo "[8/9] Enabling and starting services..."
systemctl daemon-reload
systemctl enable mysql-backup.timer
systemctl start mysql-backup.timer

echo "[9/9] Verifying installation..."
if systemctl is-active --quiet mysql-backup.timer; then
    log_info "MySQL backup timer is active"
else
    log_error "MySQL backup timer failed to start"
    exit 1
fi

if [[ -x /usr/local/bin/mysql-backup-full.sh ]]; then
    log_info "Backup script is executable"
else
    log_error "Backup script is not executable"
    exit 1
fi

if [[ -f $MYSQL_CONFIG_DIR/backup.cnf ]]; then
    log_info "Configuration file created successfully"
else
    log_error "Configuration file not found"
    exit 1
fi

log_info "MySQL backup automation installation completed successfully!"
log_info "Backup schedule: Daily at 2 AM"
log_info "Backup location: $BACKUP_DIR"
log_info "Retention period: $RETENTION_DAYS days"
log_info "Next backup: $(systemctl list-timers mysql-backup.timer --no-pager | grep mysql-backup | awk '{print $1, $2}')"
log_warn "Please save the backup user password: $MYSQL_BACKUP_PASS"

Review the script before running. Execute with: bash install.sh

#mysql #xtrabackup #backup automation #systemd timers #database backup #hot backup

Implement MySQL backup automation with Percona XtraBackup and systemd timers