資料庫備份與還原的技術要點

兩種備份方式

對多數企業網站，邏輯備份就足夠——資料量通常不大，還原時間可以接受，而且跨版本或跨主機的相容性較好。

邏輯備份的常用做法

基本備份

mysqldump -u user -p \
  --single-transaction \
  --default-character-set=utf8mb4 \
  dbname > backup.sql

幾個重要選項

• --single-transaction——對 InnoDB 取得一致性快照，不會鎖表。這是最重要的一項
• --default-character-set=utf8mb4——避免匯出時發生編碼問題
• --routines --triggers --events——若有預存程序、觸發器、排程事件，必須加上，否則不會被備份
• --no-tablespaces——某些權限受限的環境需要

第三項很常被漏掉。 還原後才發現觸發器不見了，通常已經是事後。

壓縮輸出

mysqldump -u user -p --single-transaction dbname \
  | gzip > backup_$(date +%F).sql.gz

SQL 文字的壓縮率很高，通常能省下大量空間。

只備份結構或只備份資料

mysqldump --no-data dbname > schema.sql    -- 只要結構
mysqldump --no-create-info dbname > data.sql -- 只要資料

還原

mysql -u user -p dbname < backup.sql

# 壓縮檔
gunzip < backup.sql.gz | mysql -u user -p dbname

還原前的注意事項

• 確認目標資料庫的字元集正確——否則可能造成亂碼
• 還原會覆蓋現有資料——先備份現況
• 大型備份的還原可能很久——需評估停機時間

加速還原

大型資料還原時，可暫時調整部分設定以提升速度，但這些調整會降低耐久性保證，還原完成後必須改回。

備份的四個必要條件

1. 不與資料庫存在同一台主機——主機故障或被入侵時，備份也會一起消失
2. 包含完整的資料庫——不只是檔案，還有預存程序與觸發器
3. 保留多個時間點——問題可能數週後才被發現，最新的備份可能已包含問題
4. 測試過能還原

第四項最常被忽略

沒有實際測試過的備份不算備份。 常見的狀況是備份檔案一直在產生，真的要用時才發現：

• 檔案損毀或不完整
• 缺少觸發器或預存程序
• 字元集錯誤導致還原後亂碼
• 備份腳本其實早就失敗了，只是沒人發現

建議每半年實際還原一次到測試環境驗證。

備份頻率的判斷

問一個問題：如果還原到昨天的狀態，會損失什麼？

• 純展示型網站——每週或每月即可
• 經常更新內容——每日
• 有訂單、會員、表單累積——每日，並考慮時間點還原機制

時間點還原

若需要還原到「某個特定時刻」而非「最近一次備份」，需要搭配二進位日誌。

原理

• 定期做完整備份
• 二進位日誌記錄期間的所有變更
• 還原時先套用完整備份，再重播日誌到指定時間點

啟用

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

注意日誌會佔用磁碟空間，需設定保留期限並納入磁碟監控。

什麼情況需要

典型的用途是「誤刪資料」——例如今天下午三點有人誤刪了一批訂單，可以還原到二點五十九分的狀態。

對有交易的網站，這個機制的價值很高。

自動化備份腳本

基本要素：

• 密碼不要寫在指令列中——會出現在行程列表。應使用設定檔或環境變數
• 檔名包含日期
• 自動清理過期備份
• 失敗時要通知——這一項最常被忽略
• 備份完成後傳到異地

失敗通知為什麼重要

備份腳本靜默失敗是很常見的情況——磁碟滿了、權限改了、密碼變了，腳本每天照跑但什麼都沒產生。

應該同時監控「有沒有失敗」與「有沒有正常產生新檔案」——只監控失敗是不夠的，因為排程若根本沒執行，就不會有失敗記錄。

還原演練的檢查項目

1. 備份檔案能正常解壓縮與讀取
2. 還原過程沒有錯誤
3. 資料筆數與來源相符
4. 中文與特殊字元顯示正常
5. 預存程序、觸發器、檢視表都在
6. 應用程式能正常連線與運作

業主端的備份觀念見操作紀錄、備份與資料救回。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。