MySQL資料庫災難恢複

當 MySQL Server
因為各種無法預期的原因而損壞(Crash)的時候，你就必須要進行災難恢複。如果你有做好週期性Database Backup那麼災難還原的時候應該會輕鬆很多，只要將備
份起來的資料還原回去即可，但光是這樣子還是會造成部份資料的遺失，例如 "現在" 至 "最後一次備份" 之間的資料，這時我們可以通過 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 進行災難恢複的步驟：
啟用 Binary Log
使用 mysqlbinlog 將 Binary Log 轉換成可執行檔 SQL 命令

在接下來的文章中會使用的範例與假設：
最後一次備份的時間點為 1/1 AM 0:00
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：

• 執行 Flush Logs 命令
• MySQL Server 重新啟動
• 設定檔案中有進行額外的設定

註：
請注意，當你使用 mysqldump 進行Database Backup時請記得加上 --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
即可。

目前看到的情況有：
Comment 沒有正確標示
Comment 語法錯誤
不正確的使用 DELIMITER
奇怪的 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=0s//(###.*###/)//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 命令實質上的用途是什麼。

本文地址: http://www.21andy.com/blog/20090610/1323.html