MySQL 定時備份實戰：Linux crontab + mysqldump/mydumper 自動化方案

核心命令：mysqldump 熱備份

# 單庫備份（含結構、資料、觸發器、事件、常式）
mysqldump -u root -p'pass' --single-transaction --routines --triggers --events --master-data=2 db_name > /backup/db_name_$(date +%F_%H%M%S).sql

# 壓縮備份（管道直通 gzip，節省空間 90%+）
mysqldump -u root -p'pass' --single-transaction db_name | gzip > /backup/db_name_$(date +%F_%H%M%S).sql.gz

# 多庫備份
mysqldump -u root -p'pass' --databases db1 db2 | gzip > /backup/multi_$(date +%F_%H%M%S).sql.gz

還原命令

# 解壓並還原
gunzip < /backup/db_name_20240115_020000.sql.gz | mysql -u root -p'pass' db_name

# 或先建庫再導入
mysql -u root -p'pass' -e "CREATE DATABASE db_name;"
mysql -u root -p'pass' db_name < /backup/db_name_20240115_020000.sql

備份腳本：/root/mysql_dump/mysql_back.sh

#!/bin/bash
# File: /root/mysql_dump/mysql_back.sh
# Desc: MySQL 定時備份腳本，支援壓縮、多庫、保留策略

set -euo pipefail

DB_USER="root"
DB_PASS="mypasssecret"
DB_NAME="cncounter"
BIN_DIR="/usr/bin"
BCK_DIR="/root/mysql_dump/data"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

mkdir -p "$BCK_DIR"

# 備份並壓縮
"$BIN_DIR/mysqldump" --opt --single-transaction --routines --triggers --events \
  -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BCK_DIR/${DB_NAME}_dump_${DATE}.sql.gz"

# 刪除超過保留天數的舊備份
find "$BCK_DIR" -type f -name "${DB_NAME}_dump_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Backup completed: ${DB_NAME}_dump_${DATE}.sql.gz"

加入 crontab 定時執行

# 編輯 root 的 crontab
crontab -e

# 每天凌晨 1:01 執行
1 1 * * * /root/mysql_dump/mysql_back.sh >> /var/log/mysql_backup.log 2>&1

# crontab 時間格式說明
# * * * * *  command
# 分 時 日 月 週

進階方案：mydumper 多線程備份（大庫首選）

# 安裝（Ubuntu）
apt-get install mydumper

# 備份（4 線程，壓縮）
mydumper -u root -p'pass' -h 127.0.0.1 -B db_name -o /backup/mydumper -t 4 -c

# 還原（4 線程）
myloader -u root -p'pass' -h 127.0.0.1 -B db_name -d /backup/mydumper -t 4

優勢：多線程並行導出/導入，速度提升 5-10 倍；支援一致性快照、Binlog 導出、Daemon 模式持續備份。

備份驗證與監控

1. 校驗和：sha256sum backup.sql.gz > backup.sha256，異地存儲校驗檔。
2. 定期演練：每月至少一次在測試環境完整還原驗證。
3. 監控告警：腳本返回碼非 0、備份檔大小異常（< 1KB）、磁碟空間不足 → 發送告警。
4. 異地複製：rsync -avz /backup/ user@offsite:/backup/ 或上傳至 S3/OSS。

結語

MySQL 定時備份三要素：正確命令（單事務/壓縮/完整物件）+ 可靠調度（crontab/systemd timer）+ 驗證閉環（校驗/演練/監控/異地）。小庫用 mysqldump，大庫上 mydumper，腳本化、標準化、納入運維體系。