MySQL 二進位日誌格式深入理解

MySQL二制進日誌用於記錄資料庫的變更記錄，這裡從結構上討論一下日誌的格式。

每個日誌都包含4個位元組的magic number 和event的描述包

日誌有前四個位元組是magic number： oxfe ox62 0×69 0x6e = 0xfe ‘b”i”n’ 轉成整數：1852400382  用處就是讀4個位元組對比不是這個數，說明就不是二進位日誌，就不用處理了。

 log_event.sh中可以查到

 /* 4 bytes which all binlogs should begin with */
    #define BINLOG_MAGIC        "\xfe\x62\x69\x6e"

每個event的header大概如下：

-每個event中包含：event的類型，什麼時間，由哪版本的MySQL產生的
-從header頭能找到該event的大小及一些變更資訊

第一個event稱為：format descriptor event(Event描述結構:FDE) 用於說明日誌的格式

其它event就是依賴於描述結構不同，用不同的結構記錄資料

最後一個event是： 日誌切換事件（log-rotation event）用於指點定下個日誌的檔案名稱

每個event的結構大概如下：

+===================+
|event header      |
+===================+
|event data        |
+===================+

現在大多使用的V4結構，從MySQL 5.0起，具體如下：

+=====================================+
|event  |timestamp         0:4    |
|header+––––––––––––––+
|        |type_code         4:1    |
|        +––––––––––––––+
|        |server_id         5:4    |
|        +––––––––––––––+
|        |event_length      9:4    |
|        +––––––––––––––+
|        |next_position    13:4    |
|        +––––––––––––––+
|        |flags            17:2    |
|        +––––––––––––––+
|        |extra_headers    19:x-19|
+=====================================+
|event  |fixed part        x:y    |
|data   +––––––––––––––+
|        |variable part              |
+=====================================+
第一個event是FDE結構沒有extra_headers部分，所以固定為19個位元組。

FDE的event_data中定長部分為：

2位元組的的日誌格式版本，從MySQL 5.0後都是4

50位元組 用於記錄MySQL的版本號碼 如：5.6.16-64.2-rel64.2-log 不夠50位元組用0×00填充

4位元組 日誌產生的時間

1位元組 header長度。一般是19，如果大於19，則下面的event都有extra_header欄位
對於FDE變長部分一般為空白

其它Event計算

header length = x byte

data length = (event_lenth -x )byte

資料區裡定長部分長度

 fixed_part=ybyte
 variable_part=(event_length-(x+y))byte

補充：二進位操作

開啟mysql二進位日誌：
編輯my.cnf，添加
log-bin=/var/log/mysql/mysql-bin.log
開啟日誌後需要myssqladmin flush logs才會生效。
需要注意的是log-bin指定副檔名是無效的，當mysql建立二進位記錄檔時，首先建立一個以“mysql_log_bin”為名稱，以“.index”為尾碼的檔案；再建立一個以“mysql_log_bin”為名稱，以 “.000001”為尾碼的檔案。當mysql服務重新啟動一次以“.000001”為尾碼的檔案會增加一個，並且尾碼名加1遞增；如果日誌長度超過了 max_binlog_size的上限（預設是1G）也會建立一個新的記錄檔；使用flush logs（mysql命令符）或者執行mysqladmin –u –p flush-logs（windows命令提示字元）也會建立一個新的記錄檔。
查看：
由於日誌是以二進位方式儲存的，不能直接讀取，需要使用mysql內建的mysqlbinlog工具來進行查看
mysqlbinlog mysql-bin.000002 -d test
mysqlbinlog有一些選項可以使用,簡單說明常用選項:
-d,--database=name :指定資料庫名稱,只列出指定資料庫的操作.
-D, --disable-log-bin :執行恢複的時候,禁止二進位日誌.可以防止同一台MySQL加上-t時進入死迴圈
-o,--offset=n :忽略掉日誌前n行命令
-r,--result-file=name :將輸出日誌到指定檔案
-R, --read-from-remote-server :從一個MySQL伺服器上讀取二進位
-s,--short-form :顯示簡單格式,省略一些資訊
-S, --socket=name  :socket檔案串連path.
-t, --to-last-log  :和-R一起使用,在二進位日誌結束的時候並不會停止,而是在MySQL伺服器最後產生的binlog結束,如果輸出和輸入都在一台MySQL上可能會導致死迴圈.
--set-charset=char-name :在輸出文字格式設定的時候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=# :指定輸出起始日期的日誌.
--start-position=# --stop-position=# :指定起始日誌的位置.
清理：
刪除全部二進位日誌：
reset master
刪除部分日誌：
PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO 'mysql-bin.******'命令，是將'******'編號之前的所有日誌進行刪除
PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是將在'yyyy-mm-dd hh:mm:ss'時間之前的所有日誌進行刪除
設定日誌到期時間：
修改my.cnf
expire_log_day=5
這裡設定儲存5天的日誌，超過5天的日誌會被自動刪除
恢複：
完全恢複：
mysqlbinlog mysql-bin.00001|mysql -uroot -p
基於時間點的恢複：
如果誤刪了一張表，使用完全恢複是沒有用的，因為日誌裡同樣也保留著刪除的sql語句，所以我們需要恢複到誤操作前的狀態，然後跳過誤操作的語句。
假如我在20:00誤刪了一張表，可以使用以下語句恢複：
mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p
跳過誤刪除的時間點，再執行：
mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p
基於位置點的恢複：
基於位置點的恢複可以得到更為精確的資料。
binlog
如上圖，drop table test這條語句的起始位置是889107，終止位置是889189，那麼我們可以使用於以下語句進行恢複：
mysqlbinlog --stop-position='889107' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
mysqlbinlog --start-position='889189' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
有時有可能因為系統版本的問題，以上方法行不通，可以將二進位匯出到一個sql檔案中，再直接根據sql語句進行恢複
mysqlbinlog  mysqlbinlog.000001 >log.sql