mysql通過binlog來恢復數據

一、什麼是binlog

 

1、binlog基本設定:二進制日志,也成為二進制日志,記錄對數據發生或潛在發生更改的SQL語句,並以二進制的形式保存在磁盤中;

 

二進制日志的信息:

 

文件位置:默認存放位置為資料庫文件所在目錄下

 

文件的命名方式: 名稱為hostname-bin.xxxxx (重啟mysql一次將會自動生成一個新的binlog)

 

2、配置binlog,在配置文件my.cnf中設置,並重啟mysql

3、狀態的查看:mysql> show variables like '%log_bin%';

二、mysql binlog的三種格式(Statement、MiXED、ROW)

 

1.Statement:每一條會修改數據的sql都會記錄在binlog中。

 

優點:不需要記錄每一行的變化,減少瞭binlog日志量,節約瞭IO,提高性能。(相比row能節約多少性能與日志量,這個取決於應用的SQL情況,正常同一條記錄修改或者插入row格式所產生的日志量還小於Statement產生的日志量,但是考慮到如果帶條件的update操作,以及整表刪除,alter表等操作,ROW格式會產生大量日志,因此在考慮是否使用ROW格式日志時應該跟據應用的實際情況,其所產生的日志量會增加多少,以及帶來的IO性能問題。)

 

缺點:由於記錄的隻是執行語句,為瞭這些語句能在slave上正確運行,因此還必須記錄每條語句在執行的時候的一些相關信息,以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的復制,像一些特定函數功能,slave可與master上要保持一致會有很多相關問題(如sleep()函數, last_insert_id(),以及user-defined functions(udf)會出現問題).

 

使用以下函數的語句也無法被復制:

 

* LOAD_FILE()

 

* UUID()

 

* USER()

 

* FOUND_ROWS()

 

* SYSDATE() (除非啟動時啟用瞭 –sysdate-is-now 選項)

 

同時在INSERT …SELECT 會產生比 RBR 更多的行級鎖

 

2.Row:不記錄sql語句上下文相關信息,僅保存哪條記錄被修改。

 

優點: binlog中可以不記錄執行的sql語句的上下文相關的信息,僅需要記錄那一條記錄被修改成什麼瞭。所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程,或function,以及trigger的調用和觸發無法被正確復制的問題

 

缺點:所有的執行的語句當記錄到日志中的時候,都將以每行記錄的修改來記錄,這樣可能會產生大量的日志內容,比如一條update語句,修改多條記錄,則binlog中每一條修改都會有記錄,這樣造成binlog日志量會很大,特別是當執行alter table之類的語句的時候,由於表結構修改,每條記錄都發生改變,那麼該表每一條記錄都會記錄到日志中。

 

3.Mixedlevel: 是以上兩種level的混合使用,一般的語句修改使用statment格式保存binlog,如一些函數,statement無法完成主從復制的操作,則采用row格式保存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式,也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做瞭優化,並不是所有的修改都會以row level來記錄,像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改數據的語句,還是會記錄所有行的變更  

 

三、線上實例

 

1、實例介紹:DB機器因為硬件故障突然掛掉,緊急開機竟然也沒成功,在反復重啟機器之後造成瞭mysql數據損壞,並且還是線上機器,沒辦法線上業務隻能緊急維護,

 

原想用mysqldump進行對數據拷貝然後換一下機器,沒想到數據損壞,備份竟然都沒成功,數據都無法提取出來,忽然想到當初開啟的時候,我打開瞭binlog,我可以通過binlog來提取數據

 

2、恢復數據

 

通過把binlog傳輸到正常運行的機器上面,通過binlog來恢復

/app/mysql/bin/mysqlbinlog bin-log.xxx | /app/mysql/bin/mysql

 

3、通過binlog來查詢在某個時間段執行的sql

 

當bin-log的模式設置為 row時,不僅日志長得快 並且查看執行的sql時 也稍微麻煩一點:

 

binlog_format=row

 

1.幹擾語句多;

 

2生成sql的編碼需要解碼。

直接mysqlbinlog出來的文件執行sql部分的sql顯示為base64編碼格式,固生成sql記錄的時候 不能用常規的辦法去生成 需要加上相應的參數才能顯示出sql語句

    –base64-output=decode-rows -v

    –start-date=‘開始時間’

    –stop-date=‘結束時間’

 

例如:

/opt/mysql/bin/mysqlbinlog  –base64-output=decode-rows -v –start-date='2014-09-16 14:00:00' –stop-date='2014-09-16 14:20:00'  /opt/mysql/log/mysql-bin.000017 >/opt/mysql_bak/mysqlbinlogsql_restore_2014091614.sql

You May Also Like