Linux Crontab 定時備份 MySQL 實戰：tar 物理備份 vs mysqldump 邏輯備份雙模式

利用系統 crontab 實現 MySQL 定時備份，提供**物理備份 (tar datadir)** 與**邏輯備份 (mysqldump)** 兩種模式，按業務 RTO/RPO 選擇。

模式一：物理備份 (tar 數據目錄) — 速度快、需停服務/只讀

# 1. 創建備份腳本 /usr/sbin/bak_mysql_physical.sh
cat > /usr/sbin/bak_mysql_physical.sh <<'EOF'
#!/bin/bash
set -euo pipefail
DATE=$(date +%Y%m%d)
BAK_DIR="/mysqlbackup"
DATADIR="/var/lib/mysql"  # 確認實際 datadir
mkdir -p "${BAK_DIR}"

# 可選：只讀鎖定 (InnoDB 熱備無需停機，但需確保一致性)
# mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
# systemctl stop mysql  # 純物理冷備最簡單

tar czf "${BAK_DIR}/mysql_${DATE}.tar.gz" -C / "${DATADIR#/}"

# mysql -u root -p -e "UNLOCK TABLES;"
# systemctl start mysql

echo "[$(date)] Physical backup done: ${BAK_DIR}/mysql_${DATE}.tar.gz"
EOF
chmod +x /usr/sbin/bak_mysql_physical.sh

# 2. 加入 crontab (每日 03:01)
echo "1 3 * * * root /usr/sbin/bak_mysql_physical.sh" >> /etc/crontab
systemctl restart crond

模式二：邏輯備份 (mysqldump) — 不停服務、跨版本、文件小

# 1. 創建備份腳本 /usr/sbin/bak_mysql_logical.sh
cat > /usr/sbin/bak_mysql_logical.sh <<'EOF'
#!/bin/bash
set -euo pipefail
DATE=$(date +%Y%m%d)
BAK_DIR="/mysqlbackup"
mkdir -p "${BAK_DIR}"

mysqldump --single-transaction --master-data=2 \
  --routines --triggers --events --hex-blob --quick \
  --all-databases -u root -p'PASSWORD' | \
gzip > "${BAK_DIR}/mysql_${DATE}.sql.gz"

echo "[$(date)] Logical backup done: ${BAK_DIR}/mysql_${DATE}.sql.gz"
EOF
chmod +x /usr/sbin/bak_mysql_logical.sh

# 2. 加入 crontab (每日 03:00)
echo "0 3 * * * root /usr/sbin/bak_mysql_logical.sh" >> /etc/crontab
systemctl restart crond

兩模式對比選擇指南

進階增強 (必做)

1. 保留策略：find /mysqlbackup -name "mysql_*.tar.gz" -mtime +7 -delete (腳本末尾添加)。
2. 校驗和：sha256sum backup_file >> /mysqlbackup/checksums.log。
3. 異地同步：rsync -avz /mysqlbackup/ offsite:/offsite/mysql/ (腳本末尾或單獨 crontab)。
4. 監控告警：腳本返回非 0 觸發郵件/鉑釘/企業微信告警；文件大小異常 (比前日 ±50%) 告警。
5. 月度恢復演練：單獨 crontab 每月 1 號觸發恢復腳本到測試環境。

避坑清單

• 密碼明文寫在 crontab/腳本 → 用 --defaults-extra-file=/root/.my.cnf (chmod 600)。
• datadir 路徑錯誤 → mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';" 確認。
• tar 相對路徑導致解壓路徑錯誤 → 用 tar -C / -czf ... 絕對路徑打包。
• crontab 環境變量缺失 → 腳本內显式 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin。

**建議**：中小庫首選 **邏輯備份 (mysqldump)**，大庫 (TB 級) 上 **XtraBackup 物理熱備份**，tar 冷備份僅作最後手段/遷移用。

1. 上一篇文章： mysqldump 全量+增量混合備份體系：Binlog 刷新、增量抽取、自動清理、異地傳輸完整實現
2. 下一篇文章： MySQL 命令列入門：連線、提示符、基礎查詢與多行輸入技巧

MySQL 資料庫相關知識

SQL Server 批量終止用戶進程存儲過程 kill2 實現

原生 KILL 命令僅支援單一 SPID 終止，面對批量清理

MySQL 連接報錯「Can't get hostname for your address」一鍵解決

現象：使用 IP 或域名連接 MySQL，報錯： #1042

MySQL 索引失效五大經典場景深度解析與排查實戰

索引失效 = 全表掃描 = 性能災難 通過 EXPLAIN

MySQL LONGBLOB 存儲二進制數據：Prepared Statement 分片傳輸 650MB 實測

對比 LONGTEXT 方案，LONGBLOB + mysq