ssh-keygen + ssh-copy-id)。ssh -l root -p 8080 202.xxx.xxx.xxx
# -l 用戶名,-p 端口,IP 為遠端地址
mkdir -p /mysql/mysqldata_backup
/usr/sbin/bakmysql#!/bin/bash
# 檔名日期變數(反引號或 $())
FN=$(date +%Y%m%d)
# 方案 A:打包資料目錄(需停庫或鎖表,不推薦生產用)
# tar zcvf /mysql/mysqldata_backup/mysql${FN}.tar.gz /mysql/data
# 方案 B:mysqldump 邏輯備份(推薦,熱備份)
mysqldump -u root -p'YourPassword' --single-transaction --routines --events --triggers \
--databases your_db > /mysql/mysqldata_backup/mysql${FN}.sql
# 壓縮
gzip -9 /mysql/mysqldata_backup/mysql${FN}.sql
# 刪除 7 天前備份
find /mysql/mysqldata_backup -name "mysql*.sql.gz" -type f -mtime +7 -delete
關鍵點:
FN=$(date +%Y%m%d) 反引號或 $(),非單引號。--single-transaction 保證 InnoDB 一致性視圖。find ... -delete 比 -exec rm {} \; 高效。chmod +x /usr/sbin/bakmysql
vi /etc/crontab
# 每天凌晨 03:01 執行
01 3 * * * root /usr/sbin/bakmysql
systemctl status crond
systemctl enable --now crond
# 或舊版
/etc/init.d/crond restart
/usr/sbin/bakmysql 檢查輸出與產出檔案。/var/log/cron 確認排程觸發。zcat backup.sql.gz | mysql test_db。rsync -avz /mysql/mysqldata_backup/ user@offsite:/backup/。使用 PuTTY / MobaXterm / Tabby / Windows Terminal + SSH 連線,操作步驟完全相同。
#!/bin/bash
INSTANCES=("3306:db1" "3307:db2")
for inst in "${INSTANCES[@]}"; do
PORT=${inst%:*}
DB=${inst#*:}
FN=$(date +%Y%m%d_%H%M)
mysqldump -P $PORT -u backup -p'Pwd' --single-transaction $DB | gzip -9 > /backup/${DB}_${FN}.sql.gz
done
find /backup -name "*.sql.gz" -mtime +7 -delete