MySQL backup script

Introduction

This page is to document my mysql backup.

Setup

Make a /root/.my.cnf

[client]
user=root
password=your_secure_password

Create a cronjob

0 0 * * THU  /usr/local/bin/backupDB.sh >/dev/null

Script

Here is the script

#!/bin/bash
set -e

BACKUP_DIR="/mnt/RAID/RAID/BACKUPS/MYSQL"
LOG_FILE="$BACKUP_DIR/backup_log_$(date +'%Y_%m').txt"
RETENTION_DAYS=8
DB_USER="root"
DB_NAMES=("mail" "roundcubemail" "my_wiki" "COVID" "meals_db" "gitea_db")

# Load password from ~/.my.cnf or environment
MYSQL_OPTS="--defaults-extra-file=/root/.my.cnf --single-transaction --default-character-set=utf8"

do_backup() {
    local db="$1"
    local timestamp
    timestamp="$(date +'%d_%m_%Y_%H_%M_%S')"
    local backup_file="db_backup_${db}_${timestamp}.gz"
    local full_path="$BACKUP_DIR/$backup_file"

    echo "[$(date +'%F %T')] Starting backup for $db" | tee -a "$LOG_FILE"

    if ! mysqldump $MYSQL_OPTS "$db" | gzip > "$full_path"; then
        echo "[$(date +'%F %T')] ERROR: Backup failed for $db" | tee -a "$LOG_FILE" >&2
        return 1
    fi

    chown iwiseman "$full_path" "$LOG_FILE"
    echo "[$(date +'%F %T')] Backup completed for $db → $backup_file" | tee -a "$LOG_FILE"
}

# Run backups
for db in "${DB_NAMES[@]}"; do
    do_backup "$db"
done

# Cleanup old backups
find "$BACKUP_DIR" -name 'db_backup_*' -mtime +"$RETENTION_DAYS" -exec rm -v {} \; | tee -a "$LOG_FILE"
echo "[$(date +'%F %T')] Old backups cleaned (>$RETENTION_DAYS days)" | tee -a "$LOG_FILE"
echo "[$(date +'%F %T')] All operations finished" | tee -a "$LOG_FILE"