Set up MySQL backup encryption and remote storage with rsync

Update system packages

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

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install required backup tools

Install MySQL client tools, GPG for encryption, and rsync for remote synchronization. These tools provide the foundation for secure backup operations.

sudo apt install -y mysql-client gnupg2 rsync openssh-client gzip

sudo dnf install -y mysql rsync openssh-clients gnupg2 gzip

Create backup user and directories

Create a dedicated backup user with minimal privileges and secure directory structure. This follows the principle of least privilege for backup operations.

sudo useradd -r -s /bin/bash -d /var/backups/mysql mysqlbackup
sudo mkdir -p /var/backups/mysql/{local,scripts,logs}
sudo chown -R mysqlbackup:mysqlbackup /var/backups/mysql
sudo chmod 750 /var/backups/mysql

Configure MySQL backup user

Create a MySQL user with only the necessary privileges for backup operations. This user should have SELECT and LOCK TABLES permissions.

mysql -u root -p -e "CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SecureBackupPass2024!';"
mysql -u root -p -e "GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON . TO 'backup_user'@'localhost';"
mysql -u root -p -e "FLUSH PRIVILEGES;"

Create MySQL credentials file

Store MySQL credentials securely in a configuration file with restricted permissions. This prevents password exposure in process lists.

sudo -u mysqlbackup tee /var/backups/mysql/.my.cnf << 'EOF'
[client]
user=backup_user
password=SecureBackupPass2024!
host=localhost
EOF

sudo chmod 600 /var/backups/mysql/.my.cnf

Generate GPG encryption key

Create a GPG key pair for encrypting database backups. Use a strong passphrase and store it securely for backup recovery operations.

sudo -u mysqlbackup gpg --batch --generate-key << 'EOF'
Key-Type: RSA
Key-Length: 4096
Subkey-Type: RSA
Subkey-Length: 4096
Name-Real: MySQL Backup
Name-Email: backup@example.com
Expire-Date: 2y
Passphrase: SecureGPGPassphrase2024!
%commit
EOF

Export and secure GPG key

Export the GPG public key for backup verification and store the key information securely. This enables backup recovery on different systems.

sudo -u mysqlbackup gpg --list-keys --keyid-format LONG
sudo -u mysqlbackup gpg --armor --export backup@example.com > /var/backups/mysql/backup-public-key.asc
sudo chmod 644 /var/backups/mysql/backup-public-key.asc

Generate SSH key for remote backup

Create SSH key pair for passwordless authentication to the remote backup server. This enables automated remote synchronization.

sudo -u mysqlbackup ssh-keygen -t ed25519 -f /var/backups/mysql/.ssh/id_backup -N "" -C "mysql-backup@$(hostname)"
sudo chmod 700 /var/backups/mysql/.ssh
sudo chmod 600 /var/backups/mysql/.ssh/id_backup

Configure SSH client settings

Create SSH client configuration for the backup connection with security hardening and connection optimization.

Host backup-server
    HostName 203.0.113.10
    User backupuser
    IdentityFile ~/.ssh/id_backup
    IdentitiesOnly yes
    Compression yes
    ServerAliveInterval 60
    ServerAliveCountMax 3
    StrictHostKeyChecking yes

sudo chown mysqlbackup:mysqlbackup /var/backups/mysql/.ssh/config
sudo chmod 600 /var/backups/mysql/.ssh/config

Create backup script

Create the main backup script that handles database dumping, compression, encryption, and remote synchronization with error handling and logging.

#!/bin/bash

MySQL Encrypted Backup Script
set -euo pipefail

Configuration
BACKUP_DIR="/var/backups/mysql/local"
LOG_FILE="/var/backups/mysql/logs/backup.log"
RETENTION_DAYS=7
REMOTE_HOST="backup-server"
REMOTE_PATH="/backups/mysql/$(hostname)"
GPG_RECIPIENT="backup@example.com"

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" -name "*.sql.gz.gpg" -mtime +$RETENTION_DAYS -delete
}

backup_database() {
    local db_name="$1"
    local timestamp=$(date '+%Y%m%d_%H%M%S')
    local backup_file="${BACKUP_DIR}/${db_name}_${timestamp}.sql"
    local compressed_file="${backup_file}.gz"
    local encrypted_file="${compressed_file}.gpg"
    
    log "Starting backup of database: $db_name"
    
    # Create database dump
    mysqldump --defaults-file=/var/backups/mysql/.my.cnf \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --add-drop-database \
        --databases "$db_name" > "$backup_file"
    
    # Compress backup
    gzip "$backup_file"
    
    # Encrypt compressed backup
    gpg --trust-model always --cipher-algo AES256 --compress-algo 2 \
        --recipient "$GPG_RECIPIENT" --encrypt "$compressed_file"
    
    # Remove unencrypted compressed file
    rm "$compressed_file"
    
    log "Backup completed: $encrypted_file"
    echo "$encrypted_file"
}

sync_to_remote() {
    log "Syncing backups to remote server"
    
    # Create remote directory if it doesn't exist
    ssh "$REMOTE_HOST" "mkdir -p $REMOTE_PATH"
    
    # Sync encrypted backups to remote server
    rsync -avz --delete \
        --include='*.gpg' \
        --exclude='*' \
        "$BACKUP_DIR/" \
        "${REMOTE_HOST}:${REMOTE_PATH}/"
    
    log "Remote sync completed"
}

verify_backup() {
    local backup_file="$1"
    
    log "Verifying backup: $backup_file"
    
    # Verify GPG encryption
    if gpg --quiet --verify-options show-unusable-uids --verify "$backup_file" 2>/dev/null; then
        log "GPG verification successful"
    else
        log "GPG verification failed"
        return 1
    fi
    
    # Check file size (should be > 1KB for valid backup)
    local file_size=$(stat -f%z "$backup_file" 2>/dev/null || stat -c%s "$backup_file")
    if [ "$file_size" -gt 1024 ]; then
        log "Backup size verification passed: ${file_size} bytes"
    else
        log "Backup size verification failed: ${file_size} bytes"
        return 1
    fi
}

Main execution
main() {
    log "=== MySQL Backup Started ==="
    
    # Ensure backup directory exists
    mkdir -p "$BACKUP_DIR"
    
    # Get list of databases (excluding system databases)
    databases=$(mysql --defaults-file=/var/backups/mysql/.my.cnf \
        -Bse "SHOW DATABASES;" | grep -v -E '^(information_schema|performance_schema|mysql|sys)$')
    
    if [ -z "$databases" ]; then
        log "No user databases found to backup"
        exit 1
    fi
    
    # Backup each database
    for db in $databases; do
        backup_file=$(backup_database "$db")
        verify_backup "$backup_file"
    done
    
    # Clean up old backups
    cleanup_old_backups
    
    # Sync to remote server
    sync_to_remote
    
    log "=== MySQL Backup Completed Successfully ==="
}

Error handling
trap 'log "Backup failed with error code $?"' ERR

Run main function
main "$@"

Make backup script executable

Set proper permissions for the backup script and ensure it's owned by the backup user.

sudo chown mysqlbackup:mysqlbackup /var/backups/mysql/scripts/mysql-backup.sh
sudo chmod 750 /var/backups/mysql/scripts/mysql-backup.sh

Create backup restoration script

Create a script for restoring encrypted backups when needed. This script handles decryption and database restoration.

#!/bin/bash

MySQL Backup Restoration Script
set -euo pipefail

if [ $# -ne 2 ]; then
    echo "Usage: $0  "
    echo "Example: $0 mydb_20241201_120000.sql.gz.gpg mydb_restored"
    exit 1
fi

ENCRYPTED_FILE="$1"
TARGET_DB="$2"
LOG_FILE="/var/backups/mysql/logs/restore.log"

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

log "=== Starting restore of $ENCRYPTED_FILE to $TARGET_DB ==="

Verify encrypted file exists
if [ ! -f "$ENCRYPTED_FILE" ]; then
    log "Error: Encrypted backup file not found: $ENCRYPTED_FILE"
    exit 1
fi

Create temporary directory for restoration
TEMP_DIR=$(mktemp -d)
trap "rm -rf $TEMP_DIR" EXIT

Decrypt and decompress backup
log "Decrypting backup file"
gpg --quiet --decrypt "$ENCRYPTED_FILE" | gunzip > "$TEMP_DIR/restore.sql"

Create target database
log "Creating target database: $TARGET_DB"
mysql --defaults-file=/var/backups/mysql/.my.cnf \
    -e "CREATE DATABASE IF NOT EXISTS \$TARGET_DB\;"

Restore database
log "Restoring database from backup"
mysql --defaults-file=/var/backups/mysql/.my.cnf \
    "$TARGET_DB" < "$TEMP_DIR/restore.sql"

log "=== Restore completed successfully ==="

sudo chown mysqlbackup:mysqlbackup /var/backups/mysql/scripts/mysql-restore.sh
sudo chmod 750 /var/backups/mysql/scripts/mysql-restore.sh

Set up automated scheduling

Create a systemd service and timer for automated backup execution. This provides better logging and service management than cron.

[Unit]
Description=MySQL Encrypted Backup
After=mysql.service
Requires=network-online.target

[Service]
Type=oneshot
User=mysqlbackup
Group=mysqlbackup
ExecStart=/var/backups/mysql/scripts/mysql-backup.sh
StandardOutput=append:/var/backups/mysql/logs/systemd.log
StandardError=append:/var/backups/mysql/logs/systemd.log

[Unit]
Description=Run MySQL backup daily
Requires=mysql-backup.service

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

[Install]
WantedBy=timers.target

Enable and start backup automation

Enable the systemd timer to run backups automatically and start it immediately.

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

Configure remote server SSH access

Copy the SSH public key to the remote backup server and test the connection. Replace the IP address and username with your actual remote server details.

sudo -u mysqlbackup ssh-copy-id -i /var/backups/mysql/.ssh/id_backup.pub backupuser@203.0.113.10