MySQL 8.0 Automated Backup Script: Encrypt, Rotate & Email in One Shot

It’s 1:03 a.m., your disk is at 99 %, and the only sound is your heartbeat. You realize you never set up that MySQL 8.0 backup script. Been there? Me too. After one too many panic attacks, I glued together a tiny Bash monster that:

• Dumps aaa, bbb, ccc every night
• ZIP-encrypts each dump with its own password
• Pushes the archive to a local backup server on port 12345
• Rotates files after 3 days
• Emails you “all good” (or “panic now”)

Works on MySQL 8.0 and MariaDB 10.6+. Copy, paste, sleep again.

1. One-Time Setup (Debian 10.6 → Ubuntu 22.04 Backup Box)

# Install the essentials
sudo apt update && sudo apt install -y zip bsd-mailx sshpass cron

# Create backup folder
sudo mkdir -p /home/backup/mysql8
sudo chown -R $USER:$USER /home/backup

# Tell bsd-mailx how to reach your SMTP
sudo tee -a /etc/mail.rc <<'EOF'
set from="sender@mgrei.com"
set smtp="smtp.mgrei.com:587"
set smtp-use-starttls
set smtp-auth=login
set smtp-auth-user="sender@mgrei.com"
set smtp-auth-password="YOUR_SMTP_PASS"
EOF

# Allow root@127.0.0.1 to dump (fixes 1130 error)
mysql -uroot -p -e "
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1' IDENTIFIED BY 'YOUR_MYSQL_ROOT_PASS';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;"

2. The Script (Drop-in Ready)

#!/bin/bash
# /home/backup/mysql8/backup.sh
set -e
LOCAL_DIR="/home/backup/mysql8"
BACKUP_HOST="192.168.1.100"
BACKUP_PORT="12345"
BACKUP_USER="ubuntu"
BACKUP_PASS="BACKUP_SSH_PASSWORD"
BACKUP_DIR="/home/backup/mysql8-bak"

declare -A DB_PASS=(
  [aaa]="aaaa"
  [bbb]="bbbb"
  [ccc]="cccc"
)
MYSQL_USER="root"
MYSQL_PASS="YOUR_MYSQL_ROOT_PASS"
MAIL_TO="web@mgrei.com"
RETAIN=3
DATE=$(date +%Y%m%d_%H%M%S)

cleanup() { find "$1" -type f -name "*.zip" -mtime +$RETAIN -delete; }

for DB in "${!DB_PASS[@]}"; do
  SQL="${DB}_${DATE}.sql"
  ZIP="${DB}_${DATE}.zip"

  mysqldump -h127.0.0.1 -u"$MYSQL_USER" -p"$MYSQL_PASS" \
            --single-transaction --routines --triggers "$DB" > "$LOCAL_DIR/$SQL"
  zip -P "${DB_PASS[$DB]}" "$LOCAL_DIR/$ZIP" "$LOCAL_DIR/$SQL"
  rm -f "$LOCAL_DIR/$SQL"

  # Push to backup server (port 12345, password login)
  sshpass -p "$BACKUP_PASS" scp -P "$BACKUP_PORT" "$LOCAL_DIR/$ZIP" \
          "$BACKUP_USER@$BACKUP_HOST:$BACKUP_DIR/"
done

cleanup "$LOCAL_DIR"
sshpass -p "$BACKUP_PASS" ssh -p "$BACKUP_PORT" "$BACKUP_USER@$BACKUP_HOST" \
        "find $BACKUP_DIR -type f -name '*.zip' -mtime +$RETAIN -delete"

echo "MySQL backup finished $(date +%F)" | mail -s "MySQL Backup OK" "$MAIL_TO"

3. Cron Job (Daily 01:00)

(crontab -l 2>/dev/null; echo "0 1 * * * /home/backup/mysql8/backup.sh >> /home/backup/mysql8/backup.log 2>&1") | crontab -

4. Smoke Test

bash /home/backup/mysql8/backup.sh
tail -f /home/backup/mysql8/backup.log

If you see “MySQL backup finished” in your inbox, you’re golden.

https://www.mgrei.com