MySQL binlog(二進制日志)解析

Binlog 顧名思義就是一種二進制日志，是一種與innodb引擎中redo/undo log完全不同的日志。它主要是用來記錄對Mysql數據更新或潛在發生更新的SQL語句，并以"事務"的形式保存在磁盤中。

Binlog 主要作用

復制：Mysql Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves并回放來達到master-slave數據一致的目的

數據恢復：通過mysqlbinlog工具恢復數據

增量備份

binlog管理

開啟binlogmy.cnf配置中設置：log_bin="存放binlog路徑目錄"。binlog信息查詢binlog開啟后，可以在配置文件中查看其位置信息，也可以在myslq命令行中查看：

1

2

3

4

5

6

7

8

9

10

11

show variables?like '%log_bin%';

+---------------------------------+-------------------------------------+

| Variable_name?????????????????? | Value?????????????????????????????? |

+---------------------------------+-------------------------------------+

| log_bin???????????????????????? |?ON????????????????????????????????? |

| log_bin_basename??????????????? | /var/lib/mysql/3306/mysql-bin?????? |

| log_bin_index?????????????????? | /var/lib/mysql/3306/mysql-bin.index |

| log_bin_trust_function_creators |?OFF???????????????????????????????? |

| log_bin_use_v1_row_events?????? |?OFF???????????????????????????????? |

| sql_log_bin???????????????????? |?ON????????????????????????????????? |

+---------------------------------+-------------------------------------+

binlog文件開啟binlog后，會在數據目錄（默認）生產host-bin.n（具體binlog信息）文件及host-bin.index索引文件（記錄binlog文件列表）。當binlog日志寫滿(binlog大小max_binlog_size，默認1G),或者數據庫重啟才會生產新文件，但是也可通過手工進行切換讓其重新生成新的文件（flush logs）；另外，如果正使用大的事務，由于一個事務不能橫跨兩個文件，因此也可能在binlog文件未滿的情況下刷新文件。

查看binlog文件列表的SQL語句如下：

1

2

3

4

5

6

7

8

9

10

11

12

mysql> show?binary logs;

+------------------+-----------+

| Log_name???????? | File_size |

+------------------+-----------+

| mysql-bin.000001 |?????? 177 |

| mysql-bin.000002 |?????? 177 |

| mysql-bin.000003 |? 10343266 |

| mysql-bin.000004 |? 10485660 |

| mysql-bin.000005 |???? 53177 |

| mysql-bin.000006 |????? 2177 |

| mysql-bin.000007 |????? 1383 |

+------------------+-----------+

show master status語句可以顯示binlog的狀態，包含當前二進制日志文件的狀態，正在寫入的二進制文件，及當前position等信息。

1

2

3

4

5

6

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000007 |????? 120 |????????????? |????????????????? |?????????????????? |

+------------------+----------+--------------+------------------+-------------------+

reset master語句用于清空binlog日志文件。

binlog 內容

默認情況下binlog日志是二進制格式，無法直接查看。可使用兩種方式進行查看，下面我分別列舉一下！

第一種是使用mysqlbinlog工具，用法：mysqlbinlog: /usr/bin/mysqlbinlog ?mysql-bin.000007。

mysqlbinlog是mysql官方提供的一個binlog查看工具，也可使用–read-from-remote-server從遠程服務器讀取二進制日志，還可使用–start-position –stop-position、–start-time= –stop-time精確解析binlog日志。

第二種是直接使用命令行解析。語法如下：

1

2

3

4

SHOW BINLOG EVENTS

[IN 'log_name'] //要查詢的binlog文件名

[FROM pos]

[LIMIT [offset,] row_count]

下面看一個例子：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> show binlog events?in 'mysql-bin.000007' from 1190 limit 2\G

*************************** 13. row ***************************

Log_name: mysql-bin.000007

Pos: 1190

Event_type: Query? //事件類型

Server_id: 123

End_log_pos: 1352?? //結束pose點，下個事件的起點

Info: use `test`;?insert into tb_person??set name="name__2", address="beijing", sex="man", other="nothing"

*************************** 14. row ***************************

Log_name: mysql-bin.000007

Pos: 1352

Event_type: Xid

Server_id: 123

End_log_pos: 1383

Info:?COMMIT /* xid=51 */

binlog 格式

Mysql binlog日志有ROW,Statement,MiXED三種格式；可通過my.cnf配置文件及 ==set global binlog_format='ROW/STATEMENT/MIXED'== 進行修改，命令行 ==show variables like 'binlog_format'== 命令查看binglog格式。

Row level: 僅保存記錄被修改細節，不記錄sql語句上下文相關信息優點：能非常清晰的記錄下每行數據的修改細節，不需要記錄上下文相關信息，因此不會發生某些特定情況下的procedure、function、及trigger的調用觸發無法被正確復制的問題，任何情況都可以被復制，且能加快從庫重放日志的效率，保證從庫數據的一致性 缺點:由于所有的執行的語句在日志中都將以每行記錄的修改細節來記錄，因此，可能會產生大量的日志內容，干擾內容也較多；比如一條update語句，如修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日志量會很大，特別是當執行alter table之類的語句的時候，由于表結構修改，每條記錄都發生改變，那么該表每一條記錄都會記錄到日志中，實際等于重建了表。 tip: – row模式生成的sql編碼需要解碼，不能用常規的辦法去生成，需要加上相應的參數(–base64-output=decode-rows -v)才能顯示出sql語句; – 新版本binlog默認為ROW level，且5.6新增了一個參數：binlog_row_image；把binlog_row_image設置為minimal以后，binlog記錄的就只是影響的列，大大減少了日志內容

Statement level: 每一條會修改數據的sql都會記錄在binlog中優點：只需要記錄執行語句的細節和上下文環境，避免了記錄每一行的變化，在一些修改記錄較多的情況下相比ROW level能大大減少binlog日志量，節約IO，提高性能；還可以用于實時的還原；同時主從版本可以不一樣，從服務器版本可以比主服務器版本高 缺點：為了保證sql語句能在slave上正確執行，必須記錄上下文信息，以保證所有語句能在slave得到和在master端執行時候相同的結果；另外，主從復制時，存在部分函數（如sleep）及存儲過程在slave上會出現與master結果不一致的情況，而相比Row level記錄每一行的變化細節，絕不會發生這種不一致的情況

Mixedlevel level: 以上兩種level的混合使用經過前面的對比，可以發現ROW level和statement level各有優勢，如能根據sql語句取舍可能會有更好地性能和效果；Mixed level便是以上兩種leve的結合。不過，新版本的MySQL對row level模式也被做了優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改數據的語句，那么還是會記錄所有行的變更；因此，現在一般使用row level即可。

選取規則如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄 如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那么無論如何都采用statement模式記錄

參考資料

https://dev.mysql.com/doc/internals/en/binary-log-versions.html

http://www.php.cn/mysql-tutorials-361643.html

https://www.jianshu.com/p/c16686b35807

https://www.cnblogs.com/jackluo/p/3336585.html

http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。