備份與復原

為什麼要備份

備份是在另一個地方保留一份資料的副本，原本的資料損壞時，可以用副本把資料找回來。資料庫可能因為下面這些原因損壞或遺失：

• 硬體故障：硬碟壞軌、伺服器主機板燒毀
• 軟體瑕疵：程式的錯誤把資料寫錯
• 惡意攻擊：勒索軟體把檔案加密、入侵者刪除資料
• 人為疏失：最常見的一種，例如 DELETE FROM employeeSkills; 忘了寫 WHERE

備份只能把資料還原到備份的那個時間點。每天凌晨備份一次，下午出事時，早上到下午之間的變更就沒有了； 兩次備份間隔越長，可能遺失的資料就越多。MySQL 的做法是「定期完整備份」再加上「二進位日誌」，把兩次備份之間的每一筆變更也記下來。

原課程介紹的 mysqlhotcopy（複製 MyISAM 資料檔的 Perl 腳本）在 MySQL 5.7 已經移除； BACKUP TABLE、RESTORE TABLE 兩個敘述在 MySQL 5.5 就已移除，在 MySQL 8 執行會得到 ERROR 1064 語法錯誤。本課改用目前的做法。

mysqldump：把資料庫轉成 SQL 檔

mysqldump 連上伺服器，把表格的結構轉成 CREATE TABLE、資料轉成 INSERT，寫成一個 SQL 檔。還原時，把這個檔案交給 mysql 執行一次即可：

mysqldump -u root -p --single-transaction employee > employee.sql   # 備份
mysql -u root -p employee < employee.sql                            # 還原

> 與 < 是作業系統終端機的「重新導向」：> 把程式的輸出寫進檔案，< 把檔案內容當成程式的輸入。

幾個要注意的地方：

• 原講義的 -p password（-p 和密碼之間有空格）是錯的：mysqldump 會要你輸入密碼，再把 password 當成資料庫名稱。-p 後面不寫密碼，執行時再輸入最安全
• 沒有 --single-transaction 時，mysqldump 預設在備份每個資料庫時鎖住它的表格（--lock-tables），備份期間別人不能寫入，所以原講義說它不適合在尖峰時段使用。 全部是 InnoDB 表格時，加上 --single-transaction 就沒有這個問題；MyISAM 表格不支援交易，仍然要鎖表格
• 備份進行中不要執行 ALTER TABLE、DROP TABLE 這類修改結構的敘述，--single-transaction 無法保護它們

SELECT … INTO OUTFILE 與 LOAD DATA INFILE

mysqldump 備份整個資料庫。只想把一個表格的資料存成文字檔（例如交給試算表分析，或搬到另一個表格），可以在 SQL 裡完成：

SELECT * FROM department INTO OUTFILE '/var/lib/mysql-files/department.txt';   -- 匯出
LOAD DATA INFILE '/var/lib/mysql-files/department.txt' INTO TABLE department_restore;   -- 匯入

• 檔案是由伺服器（作業系統帳號 mysql）寫在伺服器那台主機上，不是寫在你的電腦；需要 FILE 權限
• MySQL 8 的 secure_file_priv 限制只能使用某個目錄，Ubuntu 預設是 /var/lib/mysql-files/，用其他目錄會出現 ERROR 1290
• INTO OUTFILE 不會覆蓋已經存在的檔案，會出現 ERROR 1086，避免不小心蓋掉重要檔案
• 預設格式：欄位之間是 tab，每列一行，NULL 寫成 \N（反斜線加大寫 N），字串裡的 tab、換行、反斜線前面會加上反斜線
• LOAD DATA 遇到重複的主鍵，預設整個敘述失敗，一列也不會加入。加上 IGNORE 會略過重複的列（並產生警告）； 加上 REPLACE 會用檔案裡的資料取代原本的列

用上面的指令匯出 department，檔案內容是（→ 代表 tab）：

42→Finance
128→Research and Development
129→Human Resources
130→Marketing

要給試算表使用，可以指定成 CSV 格式，LOAD DATA 也要寫相同的格式選項：

SELECT * FROM client INTO OUTFILE '/var/lib/mysql-files/client.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

1,"Telco Inc","1 Collins St Melbourne","Fred Smith","95551234"
2,"The Bank","100 Bourke St Melbourne","Jan Tristan","95559876"
3,"Startup Co",\N,\N,\N

LOAD DATA LOCAL INFILE 讀取的是用戶端電腦上的檔案，不受 secure_file_priv 限制，但 MySQL 8 預設關閉（伺服器的 local_infile 是 OFF）， 直接使用會出現 ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides，要伺服器與用戶端兩邊都開啟才能用。

這種匯出只有資料，沒有 CREATE TABLE，也沒有索引與外鍵，所以範例都先用 CREATE TABLE … LIKE 建立結構相同的空表格。 直譯器把檔案存在記憶體裡，同一個範例之內有效。

二進位日誌與時間點復原

二進位日誌（binary log，簡稱 binlog）是伺服器自動記錄的「修改紀錄」：每一個新增、修改、刪除資料或改變結構的事件，都依發生順序寫進日誌，SELECT 不會記錄。 MySQL 8 預設就開啟，檔案在資料目錄，名稱是 binlog.000001、binlog.000002…：

• 伺服器每次重新啟動、檔案超過 max_binlog_size（預設 1 GB）或執行 mysqladmin flush-logs，就換一個新檔案
• binlog_expire_logs_seconds 預設 2592000 秒（30 天），超過的舊檔會自動刪除，所以完整備份的間隔一定要比它短
• 預設格式 binlog_format=ROW：記錄每一列「改之前」和「改之後」的值，而不是原本的 SQL 敘述
• SHOW BINARY LOGS; 列出目前的日誌檔，SHOW MASTER STATUS; 顯示正在寫的檔案與位置

binlog 是二進位格式，要用 mysqlbinlog 轉成看得懂、也可以直接執行的 SQL 文字。原課程的寫法 mysqlbinlog logfile > updates.sql 現在仍然可用。

時間點復原（point-in-time recovery）的想法是：

1. 先還原最近一次的完整備份（例如今天凌晨 3 點的 mysqldump），資料回到凌晨 3 點
2. 再用 mysqlbinlog 重播凌晨 3 點之後、出事之前的日誌，把這段時間的正常變更補回來
3. 出事的那個敘述不重播，資料就回到出事前的那一刻

所以備份時要知道「這份備份對應到日誌的哪個位置」，mysqldump 的 --source-data=2 就是把這個位置寫進備份檔。 （SQL 直譯器沒有 binlog，下面用導覽說明；導覽裡的輸出是在真實的 MySQL 8.0.44 上操作的結果，時間與位置依你的伺服器而不同。）

檢查與維護表格

結果都是 Table、Op、Msg_type、Msg_text 四欄：status 加上 OK 表示正常；note 是說明；Error 是錯誤。一次可以寫好幾個表格，用逗號分隔。

InnoDB 為什麼不能 REPAIR？ InnoDB 每次寫入都先記在重做日誌（redo log），伺服器當機後重新啟動時會自動依日誌把資料修好（crash recovery），一般不需要手動修復。 真的發生 InnoDB 資料檔損壞，正確的做法是從備份還原。原講義說 CHECK 與 REPAIR「可用於 InnoDB 及 MyISAM 表格」，在 MySQL 8 只有 CHECK 對兩者都成立。

命令列也有對應的工具：

MySQL 8 的系統表格與預設引擎都已經是 InnoDB，大部分情況用 mysqlcheck 或 SQL 敘述即可。

線上實體備份工具與備份策略

資料量很大（數百 GB 以上）時，mysqldump 備份與還原都太慢，又不能每次都停機，這時會用線上實體備份工具： 在伺服器運作中複製資料檔，同時記錄複製期間的變更，最後產生一份一致的備份。本課只介紹，不實際操作：

3-2-1 備份原則是規劃備份時常用的檢查清單：

• 3 份資料：正式資料加上至少 2 份備份
• 2 種不同的儲存媒體：例如伺服器的硬碟和另一台備份主機（或磁帶），不會因為同一顆硬碟壞掉而一起遺失
• 1 份放在異地：另一棟建築、另一個機房或雲端儲存，火災、水災、勒索軟體才不會把所有副本一起毀掉

其他常見的做法：

• 完整備份＋二進位日誌：例如每天一次 mysqldump（或實體備份）並保留 binlog，就能還原到任何一個時間點
• 備份檔也要保護：備份檔裡有全部的資料，要限制存取權限，必要時加密
• 定期測試還原：沒有實際還原過的備份，不能確定它真的能用。至少每學期（或每次改變備份方式時）還原到測試用的資料庫一次，並記錄還原需要多少時間

重點整理

• 已移除：mysqlhotcopy（5.7）、BACKUP TABLE／RESTORE TABLE（5.5）；--opt 預設已開啟
• 3-2-1：3 份資料、2 種媒體、1 份異地；而且要定期測試還原

下一課討論資料庫的安全性：root 密碼、匿名帳號、最小權限與連線安全。