بکاپ خودکار روزانه از MySQL با cron و حذف بکاپ‌های قدیمی

sudo mkdir -p /var/backups/mysql
sudo chmod 700 /var/backups/mysql

sudo tee /root/.my.cnf > /dev/null <<'EOF'
[client]
user=backup
password=YOUR_STRONG_PASSWORD
EOF
sudo chmod 600 /root/.my.cnf

sudo tee /usr/local/bin/mysql-backup.sh > /dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

DB_NAME="appdb"
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=14
STAMP=$(date +%F_%H-%M)
OUT="$BACKUP_DIR/${DB_NAME}_${STAMP}.sql.gz"

trap 'rm -f "$OUT.tmp"' ERR

mysqldump --single-transaction --quick --routines --triggers --events \
  --no-tablespaces "$DB_NAME" | gzip > "$OUT.tmp"

gzip -t "$OUT.tmp"
mv "$OUT.tmp" "$OUT"

# حذف بکاپ‌های قدیمی‌تر از KEEP_DAYS روز
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +"$KEEP_DAYS" -delete

echo "$(date '+%F %T') OK  $OUT ($(du -h "$OUT" | cut -f1))"
EOF
sudo chmod 700 /usr/local/bin/mysql-backup.sh

sudo /usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/

sudo crontab -e

# هر شب ساعت 03:00
0 3 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

sudo tee /etc/logrotate.d/mysql-backup > /dev/null <<'EOF'
/var/log/mysql-backup.log {
    weekly
    rotate 8
    compress
    missingok
    notifempty
}
EOF

rsync -az --delete -e "ssh -i /root/.ssh/backup_key" \
  /var/backups/mysql/ backup@203.0.113.20:/srv/backups/mysql/

mysql -e "CREATE DATABASE restore_test"
zcat /var/backups/mysql/appdb_2026-09-29_03-00.sql.gz | mysql restore_test
mysql -e "SELECT COUNT(*) FROM restore_test.users"
mysql -e "DROP DATABASE restore_test"