mysql備份還原-基於binlog的增量備份還原

1 簡介
    基於binlog的恢復需要建立在全量備份恢復基礎上。使用mysqldump的全量備份的方法在我前面的文章裡有詳細介紹，可以參考： http://blog.csdn.net/jesseyoung/article/details/41078947
    我們可以將增量的binlog備份下來，用來恢復增量資料。
2 還原增量binlog的方法
    2.1 時間點的恢復建立在一下規則的基礎上:
    1 mysql伺服器需要開啟二進位制日誌--log-bin
    檢視所有binlog檔案

mysql> SHOW BINARY LOGS;

    檢視當前binlog檔案資訊（包含檔名，當前位置等）

mysql> SHOW MASTER STATUS;

    2 mysqlbinlog工具將binary log檔案由二進位制轉換為可讀的文字檔案，可以選擇基於時間或位置的事件。
    3 從binary log檔案中執行事件會導致資料的修改，表示要重做資料。命令為：

[root@localhost /]# mysqlbinlog binlog_files | mysql -u root -p

    4 檢視binlog的時間及位置資訊我們可以講binlog內容輸出到終端或文字檔案

[root@localhost /]# mysqlbinlog binlog_files | more
[root@localhost /]# mysqlbinlog binlog_files > tmpfile

    5 將二進位制日誌輸出到檔案非常有用，比如我們想要刪除某些我們不想執行的語句。
    通過文字檔案匯入到資料庫

[root@localhost /]# mysql -u root -p < tmpfile

    2.2 多個binlog log日誌的還原最好將所有檔案使用一個連線完成，如果使用不同連線的話有時會導致不安全
    例如：

[root@localhost /]# mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
[root@localhost /]# mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

    如果第一個日誌包含建立臨時表語句CREATE TEMPORARY TABLE，第二個日誌要使用該臨時表，第一個匯入binlog日誌的程式退出後臨時表會被刪除，執行第二個日誌檔案要使用臨時表時會因找不到而報 “unknown table.”
    建議的方法：
    方法1：
    所有二進位制檔案放在單個連線裡

[root@localhost /]# mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

    方法2：
    將所有二進位制檔案寫在一個檔案裡執行

[root@localhost /]# mysqlbinlog binlog.000001 >  /tmp/statements.sql
[root@localhost /]# mysqlbinlog binlog.000002 >> /tmp/statements.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/statements.sql"

    使用方法二如果二進位制檔案裡包含GTID資訊需要過濾掉

[root@localhost /]# mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/dump.sql"

3 通過時間點或位置點的恢復
    如果我們要跳過某個時間段或位置段，需要指定起止時間或位置資訊
    3.1 通過事件的時間來恢復
    我們可以通過引數--start-datetime 和 --stop-datetime指定恢復binlog日誌的起止時間點，時間使用DATETIME格式。
    比如在時間點2005-04-20 10:00:00我們刪除掉一個庫，我們要恢復該時間點前的所有日誌

[root@localhost /]# mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root -p

    我們可能幾個小時後才發現該錯誤，後面又有一系列的增刪查改等操作，我們還需要恢復後續的binlog，我們可以指定起始時間

[root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 10:01:00" /usr/local/mysql/data/binlog.123456 | mysql -u root -p

    通過該種方法恢復我們需要通過檢視binlog日誌知道發生誤操作的確切時間點，檢視日誌我們可以先將日誌輸出到文字里

[root@localhost /]# mysqlbinlog /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql

    3.2 通過事件的位置來恢復
    我們可以通過引數--start-position 和 --stop-position指定恢復binlog日誌的起止位置點，通過位置的恢復需要我們有更加精細的操作，例如在某個時間點我們執行了錯誤的語句，且這個時間點前後都有大併發操作，要確定破壞性sql的時間點，我們可以先匯出大致的時間段的日誌到檔案以縮小查詢範圍，再去分析和確定

[root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 9:55:00"  --stop-datetime="2005-04-20 10:05:00" /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql

    確定好需要跳過的位置之後，我們就可以進行恢復了

[root@localhost /]# mysqlbinlog --stop-position=368312 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
[root@localhost /]# mysqlbinlog --start-position=368315 /usr/local/mysql/data/binlog.123456 | mysql -u root -p

    注：mysqlbinlog工具的輸出會在每條sql語句前增加 SET TIMESTAMP語句，恢復的資料及mysql日誌反映當前時間。

****************************************************************************************
    原文地址：http://blog.csdn.net/jesseyoung/article/details/41211841
    部落格主頁：http://blog.csdn.net/jesseyoung
****************************************************************************************