自動恢複MySQL資料庫的日志檔案全教程

轉載自baoz

xd同學把資料庫給drop了，有binlog，xtt提供了這個文章以供參考，希望能恢複回來吧，轉載開始。

如果MySQL伺服器啟用了二進制日志，你可以使用mysqlbinlog工具來恢複從指定的時間點開始 (例如，從你最後一次備份)直到現在或另一個指定的時間點的資料。“mysqlbinlog：用于處理二進制日志檔案的實用工具”。

要想從二進制日志恢複資料，你需要知道目前二進制日志檔案的路徑和檔案名。一般可以從選項檔案(即my.cnf or my.ini，取決于你的系統)中找到路徑。如果未包含在選項檔案中，當伺服器啟動時，可以在指令行中以選項的形式給出。啟用二進制日志的選項為–log-bin。要想确定目前的二進制日志檔案的檔案名，輸入下面的MySQL語句：

SHOW BINLOG EVENTS G

你還可以從指令行輸入下面的内容：

mysql –user=root -pmy_pwd -e ‘SHOW BINLOG EVENTS G’

将密碼my_pwd替換為伺服器的root密碼。

1. 指定恢複時間

對于MySQL 4.1.4，可以在mysqlbinlog語句中通過–start-date和–stop-date選項指定DATETIME格式的起止時間。舉例說明，假設在今天上午10:00(今天是2006年4月20日)，執行SQL語句來删除一個大表。要想恢複表和資料，你可以恢複前晚上的備份，并輸入：

mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

該指令将恢複截止到在–stop-date選項中以DATETIME格式給出的日期和時間的所有資料。如果你沒有檢測到幾個小時後輸入的錯誤的SQL語句，可能你想要恢複後面發生的活動。根據這些，你可以用起使日期和時間再次運作mysqlbinlog：

mysqlbinlog –start-date=”2005-04-20 10:01:00″ /var/log/mysql/bin.123456

在該行中，從上午10:01登入的SQL語句将運作。組合執行前夜的轉儲檔案和mysqlbinlog的兩行可以将所有資料恢複到上午10:00前一秒鐘。你應檢查日志以確定時間确切。下一節介紹如何實作。

2. 指定恢複位置

也可以不指定日期和時間，而使用mysqlbinlog的選項–start-position和–stop-position來指定日志位置。它們的作用與起止日選項相同，不同的是給出了從日志起的位置号。使用日志位置是更準确的恢複方法，特别是當由于破壞性SQL語句同時發生許多事務的時候。要想确定位置号，可以運作mysqlbinlog尋找執行了不期望的事務的時間範圍，但應将結果重新指向文本檔案以便進行檢查。操作方法為：

mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20 10:05:00″

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

該指令将在/tmp目錄建立小的文本檔案，将顯示執行了錯誤的SQL語句時的SQL語句。你可以用文本編輯器打開該檔案，尋找你不要想重複的語句。如果二進制日志中的位置号用于停止和繼續恢複操作，應進行注釋。用log_pos加一個數字來标記位置。使用位置号恢複了以前的備份檔案後，你應從指令行輸入下面内容：

mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456

mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456

上面的第1行将恢複到停止位置為止的所有事務。下一行将恢複從給定的起始位置直到二進制日志結束的所有事務。因為mysqlbinlog的輸出包括每個SQL語句記錄之前的SET TIMESTAMP語句，恢複的資料和相關MySQL日志将反應事務執行的原時間。