MySQL 定時備份 Shell 腳本：本地打包 + SCP 遠程免密傳輸 + 定時清理

核心思路

1. mysqldump 導出 .sql
2. tar 壓縮打包
3. SSH 密鑰對實現 scp 免密傳輸至備份機
4. crontab 定時執行
5. 保留最近 3 天備份，自動清理過期文件

免密鑰配置

ssh-keygen -t rsa  # 一路回車
chmod 755 ~/.ssh
scp -P 1110 ~/.ssh/id_rsa.pub user@remote:/home/user/.ssh/authorized_keys

備份腳本 dump.sh

#!/bin/bash
USER="root"
PASSWORD="*****"
DATABASE="myblogdb"
MAIL="admin@example.com"
BACKUP_DIR=/home/user/data_backup/
LOGFILE=$BACKUP_DIR/data_backup.log
DATE=$(date +%Y%m%d-%H%M)
DUMPFILE=$DATE.sql
ARCHIVE=$DATE.sql.tar.gz
OPTIONS="-u$USER -p$PASSWORD $DATABASE"

[ ! -d $BACKUP_DIR ] && mkdir -p $BACKUP_DIR
cd $BACKUP_DIR

echo "--------------------" >> $LOGFILE
echo "BACKUP DATE: $(date +'%y-%m-%d %H:%M:%S')" >> $LOGFILE

mysqldump $OPTIONS > $DUMPFILE
if [[ $? == 0 ]]; then
    tar czvf $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1
    echo "[$ARCHIVE] Backup Successful!" >> $LOGFILE
    rm -f $DUMPFILE
    scp -P 1110 $ARCHIVE user@remote:/home/user/data_backup/
else
    echo "Database Backup Fail!" >> $LOGFILE
    mail -s "database:$DATABASE Daily Backup Fail!" $MAIL
fi

echo "Backup Process Done"
find $BACKUP_DIR -type f -mtime +2 -name "*.tar.gz" -exec rm -f {} \;

定時任務

# /etc/crontab
00 03 * * * root /data/backup/dump.sh
/etc/rc.d/init.d/crond restart

每天凌晨 3 點自動備份。