Implement MariaDB backup encryption with Mariabackup and automated restoration

Update system packages

Start by updating your package manager to ensure you get the latest versions of MariaDB backup tools.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install MariaDB backup tools

Install Mariabackup and required encryption utilities for secure backup operations.

sudo apt install -y mariadb-backup openssl qpress

sudo dnf install -y mariadb-backup openssl qpress

Create backup directories and set permissions

Set up dedicated directories for backups with proper ownership for the mysql user.

sudo mkdir -p /var/backup/mariadb/{full,incremental,keys}
sudo chown -R mysql:mysql /var/backup/mariadb
sudo chmod 750 /var/backup/mariadb
sudo chmod 700 /var/backup/mariadb/keys

Generate encryption keys

Create a strong encryption key for backup encryption and store it securely.

sudo openssl rand -base64 32 | sudo tee /var/backup/mariadb/keys/backup.key
sudo chown mysql:mysql /var/backup/mariadb/keys/backup.key
sudo chmod 600 /var/backup/mariadb/keys/backup.key

Configure MariaDB backup user

Create a dedicated database user with minimum required privileges for backup operations.

sudo mysql -u root -p

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SecureBackupPass2024!';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO 'backup_user'@'localhost';
GRANT PROCESS ON . TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Create backup credentials file

Store database credentials securely to avoid exposing passwords in scripts.

[client]
user=backup_user
password=SecureBackupPass2024!
host=localhost
port=3306

sudo chown mysql:mysql /var/backup/mariadb/.my.cnf
sudo chmod 600 /var/backup/mariadb/.my.cnf

Create encrypted backup script

Develop a comprehensive backup script with encryption, compression, and error handling.

#!/bin/bash

MariaDB Encrypted Backup Script
Usage: mariadb-encrypted-backup.sh [full|incremental]

set -euo pipefail

Configuration
BACKUP_DIR="/var/backup/mariadb"
KEY_FILE="${BACKUP_DIR}/keys/backup.key"
CREDENTIALS="${BACKUP_DIR}/.my.cnf"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${BACKUP_DIR}/backup_${DATE}.log"
RETENTION_DAYS=30

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

cleanup_old_backups() {
    log "Cleaning up backups older than ${RETENTION_DAYS} days"
    find "${BACKUP_DIR}/full" -name "*.tar.gz.enc" -mtime +${RETENTION_DAYS} -delete
    find "${BACKUP_DIR}/incremental" -name "*.tar.gz.enc" -mtime +${RETENTION_DAYS} -delete
    find "${BACKUP_DIR}" -name "backup_*.log" -mtime +${RETENTION_DAYS} -delete
}

perform_backup() {
    local backup_type="$1"
    local backup_subdir="${BACKUP_DIR}/${backup_type}"
    local backup_name="mariadb_${backup_type}_${DATE}"
    local temp_backup="${backup_subdir}/${backup_name}"
    
    log "Starting ${backup_type} backup: ${backup_name}"
    
    mkdir -p "$backup_subdir"
    
    # Perform backup based on type
    if [ "$backup_type" = "full" ]; then
        mariabackup --defaults-file="$CREDENTIALS" \
                   --backup \
                   --compress \
                   --compress-threads=4 \
                   --target-dir="$temp_backup" 2>&1 | tee -a "$LOG_FILE"
    else
        # Incremental backup
        local latest_full=$(find "${BACKUP_DIR}/full" -name "mariadb_full_*" -type d | sort -r | head -n1)
        if [ -z "$latest_full" ]; then
            log "ERROR: No full backup found for incremental backup"
            exit 1
        fi
        
        mariabackup --defaults-file="$CREDENTIALS" \
                   --backup \
                   --compress \
                   --compress-threads=4 \
                   --target-dir="$temp_backup" \
                   --incremental-basedir="$latest_full" 2>&1 | tee -a "$LOG_FILE"
    fi
    
    # Compress and encrypt backup
    log "Compressing and encrypting backup"
    tar -czf "${temp_backup}.tar.gz" -C "$backup_subdir" "$(basename "$temp_backup")"
    
    openssl enc -aes-256-cbc -salt -in "${temp_backup}.tar.gz" \
                -out "${temp_backup}.tar.gz.enc" -pass file:"$KEY_FILE"
    
    # Verify encryption
    if [ -f "${temp_backup}.tar.gz.enc" ]; then
        log "Backup encrypted successfully: ${temp_backup}.tar.gz.enc"
        log "Backup size: $(du -h "${temp_backup}.tar.gz.enc" | cut -f1)"
        
        # Clean up temporary files
        rm -rf "$temp_backup" "${temp_backup}.tar.gz"
    else
        log "ERROR: Encryption failed"
        exit 1
    fi
}

Main execution
main() {
    local backup_type="${1:-full}"
    
    # Validate backup type
    if [ "$backup_type" != "full" ] && [ "$backup_type" != "incremental" ]; then
        echo "Usage: $0 [full|incremental]"
        exit 1
    fi
    
    # Check prerequisites
    if [ ! -f "$KEY_FILE" ]; then
        log "ERROR: Encryption key not found at $KEY_FILE"
        exit 1
    fi
    
    if [ ! -f "$CREDENTIALS" ]; then
        log "ERROR: Credentials file not found at $CREDENTIALS"
        exit 1
    fi
    
    log "Starting MariaDB ${backup_type} backup process"
    
    perform_backup "$backup_type"
    cleanup_old_backups
    
    log "Backup process completed successfully"
}

main "$@"

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

Create automated restoration script

Build a restoration script that handles decryption, decompression, and database preparation.

#!/bin/bash

MariaDB Encrypted Restore Script
Usage: mariadb-encrypted-restore.sh  [target_directory]

set -euo pipefail

Configuration
BACKUP_DIR="/var/backup/mariadb"
KEY_FILE="${BACKUP_DIR}/keys/backup.key"
RESTORE_DIR="/var/restore/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${BACKUP_DIR}/restore_${DATE}.log"

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

verify_backup() {
    local encrypted_file="$1"
    
    log "Verifying encrypted backup file: $encrypted_file"
    
    if [ ! -f "$encrypted_file" ]; then
        log "ERROR: Backup file not found: $encrypted_file"
        exit 1
    fi
    
    # Test decryption without extracting
    if ! openssl enc -aes-256-cbc -d -in "$encrypted_file" \
                     -pass file:"$KEY_FILE" | head -c 1 > /dev/null 2>&1; then
        log "ERROR: Cannot decrypt backup file. Check encryption key."
        exit 1
    fi
    
    log "Backup file verification successful"
}

restore_backup() {
    local encrypted_file="$1"
    local target_dir="${2:-${RESTORE_DIR}}"
    local temp_dir="${target_dir}/temp_${DATE}"
    
    log "Starting restoration process"
    log "Source: $encrypted_file"
    log "Target: $target_dir"
    
    # Create restoration directory
    mkdir -p "$temp_dir"
    
    # Decrypt and decompress
    log "Decrypting and extracting backup"
    openssl enc -aes-256-cbc -d -in "$encrypted_file" \
                -pass file:"$KEY_FILE" | tar -xzf - -C "$temp_dir"
    
    # Find the backup directory
    local backup_content=$(find "$temp_dir" -maxdepth 1 -type d -name "mariadb_*" | head -n1)
    
    if [ -z "$backup_content" ]; then
        log "ERROR: No backup content found in extracted archive"
        exit 1
    fi
    
    log "Backup extracted to: $backup_content"
    
    # Prepare backup (decompress individual files)
    log "Preparing backup for restoration"
    mariabackup --decompress --target-dir="$backup_content"
    
    # Remove compressed files
    find "$backup_content" -name "*.qp" -delete
    
    # Prepare the backup
    log "Applying log files to backup"
    mariabackup --prepare --target-dir="$backup_content"
    
    log "Backup prepared successfully at: $backup_content"
    log "To complete restoration, stop MariaDB and copy files to datadir"
    log "Example commands:"
    log "  sudo systemctl stop mariadb"
    log "  sudo mv /var/lib/mysql /var/lib/mysql.backup"
    log "  sudo mariabackup --copy-back --target-dir=$backup_content"
    log "  sudo chown -R mysql:mysql /var/lib/mysql"
    log "  sudo systemctl start mariadb"
}

Main execution
main() {
    if [ $# -lt 1 ]; then
        echo "Usage: $0  [target_directory]"
        echo "Example: $0 /var/backup/mariadb/full/mariadb_full_20240115_120000.tar.gz.enc"
        exit 1
    fi
    
    local encrypted_file="$1"
    local target_dir="${2:-${RESTORE_DIR}}"
    
    # Check prerequisites
    if [ ! -f "$KEY_FILE" ]; then
        log "ERROR: Encryption key not found at $KEY_FILE"
        exit 1
    fi
    
    verify_backup "$encrypted_file"
    restore_backup "$encrypted_file" "$target_dir"
    
    log "Restoration preparation completed successfully"
}

main "$@"

sudo chmod 750 /usr/local/bin/mariadb-encrypted-restore.sh
sudo chown mysql:mysql /usr/local/bin/mariadb-encrypted-restore.sh

Set up automated scheduling

Configure systemd timers for regular encrypted backups with different frequencies.

[Unit]
Description=MariaDB Full Encrypted Backup
Requires=mariadb.service
After=mariadb.service

[Service]
Type=oneshot
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mariadb-encrypted-backup.sh full
TimeoutStartSec=3600
PrivateTmp=true
ProtectHome=true
ProtectSystem=strict
ReadWritePaths=/var/backup/mariadb

[Unit]
Description=Run MariaDB full backup weekly
Requires=mariadb-backup-full.service

[Timer]
OnCalendar=Sun 02:00
Persistent=true
RandomizedDelaySec=1800

[Install]
WantedBy=timers.target

[Unit]
Description=MariaDB Incremental Encrypted Backup
Requires=mariadb.service
After=mariadb.service

[Service]
Type=oneshot
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mariadb-encrypted-backup.sh incremental
TimeoutStartSec=1800
PrivateTmp=true
ProtectHome=true
ProtectSystem=strict
ReadWritePaths=/var/backup/mariadb

[Unit]
Description=Run MariaDB incremental backup daily
Requires=mariadb-backup-incremental.service

[Timer]
OnCalendar=--* 02:30
Persistent=true
RandomizedDelaySec=900

[Install]
WantedBy=timers.target

Enable backup timers

Activate the systemd timers to start automated backup scheduling.

sudo systemctl daemon-reload
sudo systemctl enable --now mariadb-backup-full.timer
sudo systemctl enable --now mariadb-backup-incremental.timer

Create backup verification script

Build a verification script to regularly test backup integrity and restoration capability.

#!/bin/bash

MariaDB Backup Verification Script

set -euo pipefail

BACKUP_DIR="/var/backup/mariadb"
VERIFY_LOG="${BACKUP_DIR}/verify_$(date +%Y%m%d_%H%M%S).log"

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

verify_latest_backup() {
    local backup_type="$1"
    local latest_backup=$(find "${BACKUP_DIR}/${backup_type}" -name "*.tar.gz.enc" -type f | sort -r | head -n1)
    
    if [ -z "$latest_backup" ]; then
        log "WARNING: No ${backup_type} backup found"
        return 1
    fi
    
    log "Verifying ${backup_type} backup: $(basename "$latest_backup")"
    
    # Test decryption
    if /usr/local/bin/mariadb-encrypted-restore.sh "$latest_backup" "/tmp/verify_${backup_type}_$$" > /dev/null 2>&1; then
        log "✓ ${backup_type} backup verification successful"
        rm -rf "/tmp/verify_${backup_type}_$$"
        return 0
    else
        log "✗ ${backup_type} backup verification failed"
        return 1
    fi
}

Main verification
log "Starting backup verification"
verify_latest_backup "full"
verify_latest_backup "incremental"
log "Backup verification completed"

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