MySQL Auto-Backup & SCP over Key: A No-BS Guide for Lazy Admins

Tired of typing mysqldump and dragging zip files around like it’s 1999? Here’s the full Euro-engineer stack:

Key-only SSH on port 12345 — brute forcers can go cry.
Encrypted dumps pushed via SCP, zero passwords in clear text.
Old backups auto-purged so your disk never explodes.

Copy-paste, grab coffee, done.

Stack

Main DB	Ubuntu 20.04 + MariaDB 10.6
Backup box	Debian 11, IP 192.168.1.100, SSH 12345
Key pass-phrase	1122334455 (change if you want)

1. Lock the Door — Key-Only SSH

# On backup box
ssh-keygen -t rsa -b 4096 -f ~/.ssh/mysql_backup_key
ssh-copy-id -i ~/.ssh/mysql_backup_key.pub -p 12345 user@192.168.1.100

# Disable password logins
sudo nano /etc/ssh/sshd_config
----
PasswordAuthentication no
PermitRootLogin prohibit-password
----
sudo systemctl restart ssh

2. The Backup Script (drop in /opt/mysql_backup.sh)

#!/bin/bash
set -e

LOCAL_DIR="/home/backup/mysql8"
REMOTE_HOST="192.168.1.100"
REMOTE_PORT="12345"
REMOTE_USER="backup"
IDENTITY="/home/backup/.ssh/mysql_backup_key"
REMOTE_DIR="/data/backup/mysql"
KEEP_DAYS=7
MAIL="dba@mgrei.com"

declare -A DB_PASS=([blog]="blog2024" [shop]="shop2024" [dddd]="dddddd")
ROOT_DB_PASS="your_mysql_root_pass"

DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$LOCAL_DIR"

find "$LOCAL_DIR" -name "*.zip" -mtime +$KEEP_DAYS -delete

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

    mysqldump -uroot -p"$ROOT_DB_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"

    scp -i "$IDENTITY" -P "$REMOTE_PORT" \
        "$LOCAL_DIR/$ZIP" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"
done

ssh -i "$IDENTITY" -p "$REMOTE_PORT" \
    "$REMOTE_USER@$REMOTE_HOST" \
    "find $REMOTE_DIR -name '*.zip' -mtime +$KEEP_DAYS -delete"

echo "Backup finished $(date)" | mail -s "MySQL Backup OK" "$MAIL"

Make it executable:

chmod +x /opt/mysql_backup.sh

3. Keep Agent Alive (keychain)

sudo apt update && sudo apt install keychain -y
echo 'eval $(keychain --eval /home/backup/.ssh/mysql_backup_key)' >> ~/.bashrc
source ~/.bashrc

4. Schedule It

crontab -e
# 2 AM daily
0 2 * * * /opt/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

5. Troubles? Quick Wins

mysqldump: Host '127.0.0.1' is not allowed — remove -h127.0.0.1, use socket.
scp: Permission denied (publickey) — key not in agent or wrong perms (chmod 600).
agent not found — run eval $(keychain --eval ...) before script.

That’s it. Go grab another espresso while databases back themselves up.