MySQL 二進制日志格式深入理解

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