Linux 定時自動備份 MySQL 腳本與 crontab 部署實戰

利用 Linux crontab 搭配 Shell 腳本實現 MySQL 定時自動備份，提供兩種備份模式（tar 打包資料目錄 / mysqldump 導出 SQL）完整部署步驟。

部署步驟

1. 建立備份儲存目錄

mkdir -p /mysqldata

2. 建立備份腳本 /usr/sbin/bakmysql

vi /usr/sbin/bakmysql

模式 A：tar 打包資料目錄（適用 MyISAM、停機或鎖表窗口）

#!/bin/bash
rq=$(date +%Y%m%d)
tar zcvf /mysqldata/mysql_${rq}.tar.gz /var/lib/mysql

模式 B：mysqldump 導出 SQL（通用、不停機、支援 InnoDB）

#!/bin/bash
rq=$(date +%Y%m%d)
mysqldump --all-databases -u root -p'YourPassword' --single-transaction --hex-blob \
  | gzip > /mysqldata/mysql_${rq}.sql.gz

注意：
- /var/lib/mysql 為資料目錄，部分安裝為 /usr/local/mysql/data，請確認實際路徑。
- 密碼含特殊字元請用單引號包裹。
- 生產環境建議建立專用備份帳號，僅授 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD 權限。

3. 賦予執行權限

chmod +x /usr/sbin/bakmysql

4. 配置 crontab 定時任務

vi /etc/crontab

新增行（每天凌晨 3:01 執行）：

01 3 * * * root /usr/sbin/bakmysql

格式：分 時 日 月 週 使用者 命令。

5. 重啟 cron 服務

/etc/rc.d/init.d/crond restart
# 或 systemctl restart crond (CentOS 7+)

結果驗證

次日於 /mysqldata 目錄將看到：

mysql_20240101.tar.gz
# 或
mysql_20240101.sql.gz

進階增強建議

• 保留策略：腳本開頭加入 find /mysqldata -mtime +7 -name "mysql_*.gz" -delete 自動刪除 7 天前備份。
• 異地同步：備份完成後 rsync -avz /mysqldata/ user@backup-server:/remote/path/ 推送至異地。
• 成功/失敗通知：腳本結尾以 mail 或 Webhook 發送狀態。
• 日誌記錄：exec >> /var/log/mysql_backup.log 2>&1 記錄詳細輸出。
• mysqldump 專用帳號：GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'strongpass';

常見坑點提醒

1. 腳本首行必須有 #!/bin/bash，否則 cron 執行失敗。
2. 環境變數：cron 環境極簡，腳本內所有命令請用絕對路徑（/usr/bin/mysqldump、/bin/tar、/bin/date）。
3. 密碼安全：避免明文寫在腳本，可用 --defaults-extra-file=/root/.my.cnf 指定密碼檔（權限 600）。
4. 大資料庫：mysqldump --single-transaction --quick 避免記憶體暴漲；超大庫建議改用 xtrabackup（參見文章 247001）。