MySQL通過binlog來恢複資料

來源:互聯網
上載者:User

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

本文永久更新連結地址:

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.