SQL - MySQL 資料庫災難復原

[Admin1]

當 MySQL Server 因為各種無法預期的原因而損毀(Crash)的時候，你就必須要進行災難復原。若是您有做好定期的資料庫備份那麼災難還原的時候應該會輕鬆很多，只要將備份起來的資料還原回去即可，但光是這樣子還是會造成部份資料的遺失，例如 "現在" 至 "最後一次備份" 之間的資料，這時我們可以透過 MySQL 提供的 Binary Log 機制將可能遺失的資料降至最低。

Binary Log 的運作原理很簡單，它只是單純的將所有會修改到資料庫內容的操作記錄在 Log 檔案中，然後透過這個 Binary Log 你就可以重新執行所有會修改到資料庫內容的操作。例如若你最後一次備份的時間是 1/1 AM 0:00 ，並且有啟用 Binary Log 功能記錄 1/1 AM 0:00 這個時間點以後所有會修改到資料庫內容的操作，假設你的 MySQL Server 在 1/2 AM 10:00 故障，你就可以將 1/1 AM 0:00 備份的資料還原回去，然後利用 Binary Log 將 1/1 AM 0:00 ~ 1/2 AM 10:00 之間所有的操作重新執行一次，這樣子一來你就可以將資料庫還原到當機的那個時間點。



使用 Binary Log 進行災難復原的步驟：

1. 啟用 Binary Log
2. 使用 mysqlbinlog 將 Binary Log 轉換成可執行的 SQL 指令

在接下來的文章中會使用的範例與假設：

1. 最後一次備份的時間點為 1/1 AM 0:00
2. MySQL Server 在 1/2 AM 10:00 故障

一、啟用 Binary Log

修改 MySQL Server 的系統設定檔(eg. /etc/my.cnf)，在 [mysqld] 區塊中加上 log-bin=mysql-bin 選項，然後重新啟動 MySQL Server，例如：

[mysqld]
log-bin=mysql-bin

啟用後你應該可以在 MySQL 的 Data Dir 裡面發現如下的檔案：

mysql-bin.index
mysql-bin.000001
mysql-bin.000002
...............
mysql-bin.00000X

MySQL 在以下幾種情況會進行 lograrote：

1. 執行 Flush Logs 指令
2. MySQL Server 重新啟動
3. 設定檔中有進行額外的設定

註：
請注意，當您使用 mysqldump 進行資料庫備份時請記得加上 --flush-logs 選項，例如：

mysqldump --flush-logs -u root -p 資料庫名稱 > example.sql

這麼做的目的是在備份時讓 MySQL Server 進行 logrotate，這樣子日後要辨別 "最後一次備份時間點" 之後的 Binary Log 會比較方便，因為若你沒有主動(或透過設定)去刪除 Binary Log，則只要你的硬碟空間夠大，MySQL 會無限期的保存 Binary Log，也就是說你的 Binary Log 裡面所記載的資料有可能包含 "最後一次備份時間點" 之前的資料。

二、使用 mysqlbinlog 將 Binary Log 轉換成可執行的 SQL 指令

Binary Log 是無法被 MySQL Server 直接執行、也無法直接以人眼去閱讀的，必須要先使用 MySQL 所提供的 mysqlbinlog 程式，將 Binary Log 轉換為 MySQL Server 可以執行的 SQL 指令。mysqlbinlog 的語法如下：

mysqlbinlog -H --set-charset="utf8" --start-datatime="2007-01-01 00:00:00" --stop-datatime="2007-01-02 10:00:00" mysql-bin.[0-9]* > example.sql

-H：Display a hex dump of the log in comments.

--set-charset：設定編碼

--start-datatime：要轉換的開始時間點

--stop-datatime：要轉換的結束時間點

mysql-bin.[0-9]*：這裡要注意的是，要一次處理所有的 Binary Log，因為儲存在 Binary Log 中的資料有可能會 "跨檔案"，例如從 mysql-bin.000001 的結尾接到 mysql-bin.000002 的開頭。

example.sql：轉換出來的文字檔的檔案名稱，這個名稱可以自已取。

需要加 -H 選項的原因如下：

mysqlbinlog didn't escape the string content of user variables, and did not deal well when these variables were in non-ASCII character sets; this is now fixed by always printing the string content of user variables in hexadecimal. The character set and collation of the string is now also printed. (Bug #3875)

實際執行轉換後的 Binary Log

很簡單，只要一行簡單的指令：

mysql < example.sql

如果沒有什麼錯誤訊息發生，那麼只要等它執行完就大功告成了。話又說回來，要是執行失敗呢？這是有可能的。MySQL 在處理 Binary Log 時有一些 Bug 存在，它的 Bug Report 似乎是說在最新版本的 MySQL Server 中已修正此 Bug，我沒有實際測試過所以不清楚，但若是你和我一樣也遇到這個 Bug 的話，也不用太擔心。這些問題其實不難解決，自己 Workaround 即可。

目前我看到的情況有：

1. Comment 沒有正確標示
2. Comment 語法錯誤
3. 不正確的使用 DELIMITER
4. 奇怪的 STOP 指令(我不太確定這是做什麼用的)

自己用 sed 去修改轉換過後的 example.sql 即可。

sed -f replace.rules example.sql > final.sql

replace.rules檔案的內容：

s/\(Query.*thread\)/#\1/g
s/\(###.*###\)//g
s/DELIMITER ;//g
s/Stop//g

上面幾行的意義：

s/\(Query.*thread\)/#\1/g

MySQL 的 Binary Log 在處理 Comment 的時候，有的時候會漏加 "#" 符號在 Comment Line 的最前面。例如本來是：

Query thread_id=227528 exec_time=- error_code=0

要改成：

#Query thread_id=227528 exec_time=- error_code=0

s/\(###.*###\)//g

在某些 SQL statement(例如 REPLACE INTO search)的最後面會有一些 Comment 存在，但這些 Comment 的語法不正確反而會造成執行失敗，故刪除之。
類似以下的行都應該刪除：

### Bitfield: user.options ###
### SAVE ORDERED IDS TO SEARCH CACHE ###

........等等

s/DELIMITER ;//g

刪除不正確的 DELIMITER 指令，像以下這樣就是不正確的：

DELIMITER ;

s/Stop//g

有的時候會在 Binary Log 中出現 Stop 這個指令而導致執行失敗，故刪除之。但我不太確定這個 Stop 指令實質上的用途是什麼。