前言：

我們都知道，binlog可以說是MySQL中比較重要的日志了，在日常學習及運維過程中，也經常會遇到。不清楚你對binlog了解多少呢？本篇文章將從binlog作用、binlog相關參數、解析binlog內容三個方面帶你了解binlog。

? 1.binlog簡介

binlog即binary log，二進制日志文件。它記錄了數據庫所有執行的DDL和DML語句(除了數據查詢語句select、show等)，以事件形式記錄并保存在二進制文件中。

binlog主要有兩個應用場景，一是用于復制，master把它的二進制日志傳遞給slaves來達到master-slave數據一致的目的。二是用于數據恢復，例如還原備份后，可以重新執行備份后新產生的binlog，使得數據庫保持最新狀態。除去這兩個主要用途外，binlog還可以用于異構系統之間數據的交互，binlog完整保存了一條記錄的前項和后項記錄，可以用DTS服務，將MySQL數據以準實時的方式抽取到底層數據平臺，比如HBase、Hive、Spark等，打通OLTP和OLAP。

binlog日志可以選擇三種模式，分別是 STATEMENT、 ROW、 MIXED，下面簡單介紹下這三種模式：

• STATEMENT：基于SQL語句的復制，每一條會修改數據的sql語句會記錄到binlog中。該模式下產生的binlog日志量會比較少，但可能導致主從數據不一致。
• ROW：基于行的復制，不記錄每一條具體執行的SQL語句，僅需記錄哪條數據被修改了，以及修改前后的樣子。該模式下產生的binlog日志量會比較大，但優點是會非常清楚的記錄下每一行數據修改的細節，主從復制不會出錯。
• Mixed：混合模式復制，以上兩種模式的混合使用，一般的復制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇日志保存方式。

binlog模式在MySQL 5.7.7之前，默認為 STATEMENT，在之后的版本中，默認為ROW。這里建議采用ROW模式，因為ROW模式更安全，可以清楚記錄每行數據修改的細節。

? 2.binlog相關參數

binlog默認情況下是不開啟的，不過一般情況下，初始化的時候建議在配置文件中增加log-bin參數來開啟binlog。

#?配置文件中增加log-bin配置
[mysqld]
log-bin?=?binlog

#?不指定路徑默認在data目錄下，也可以指定路徑
[mysqld]
log-bin?=?/data/mysql/logs/binlog

#?查看數據庫是否開啟了binlog
show?variables?like?'log_bin%';

開啟binlog后，還需注意一些與binlog相關的參數，下面簡單介紹下相關參數：

binlog_format

設置binlog模式，建議設為ROW。

binlog_do_db

此參數表示只記錄指定數據庫的二進制日志，默認全部記錄，一般情況下不建議更改。

binlog_ignore_db

此參數表示不記錄指定的數據庫的二進制日志，同上，一般不顯式指定。

expire_logs_days

此參數控制二進制日志文件保留天數，默認值為0，表示不自動刪除，可設置為0~99。可根據實際情況設置，比如保留15天或30天。MySQL8.0版本可用binlog_expire_logs_seconds參數代替。

max_binlog_size

控制單個二進制日志大小，當前日志文件大小超過此變量時，執行切換動作。此參數的最大和默認值是1GB，該設置并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進當前日志，直到事務結束。一般情況下可采取默認值。

log_bin_trust_function_creators

當二進制日志啟用后，此參數就會啟用。它控制是否可以信任存儲函數創建者，不會創建寫入二進制日志引起不安全事件的存儲函數。如果設置為0(默認值)，用戶不得創建或修改存儲函數，除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權之外的SUPER權限。建議設置為1。

sync_binlog

控制MySQL服務端將二進制日志同步到磁盤的頻率，默認值為1。

設置為0，表示MySQL不控制binlog的刷新，由文件系統自己控制它的緩存的刷新；

設置為1，表示每次事務提交，MySQL都會把binlog刷下去，這是最安全的設置，但由于磁盤寫入次數增加，可能會對性能產生負面影響；

設置為n，其中n為0或1以外的值，在進行n次事務提交以后，Mysql將執行一次fsync之類的磁盤同步指令，將Binlog文件緩存刷新到磁盤。

推薦設置為1，出于性能考慮也可酌情調整。

關于binlog操作與管理相關的SQL也有很多，下面介紹下部分常用的語句：

SQL語句語句含義

show master status	查看當前最新的一個binlog日志的編號名稱，及最后一個事件結束的位置
show binary logs	查看目前保留的所有binlog日志列表及大小
flush logs	刷新binlog，此刻開始產生一個新編號的binlog日志文件
purge binary logs before '2020-07-01 12:00:00'	手動清理指定時間之前的binlog日志
purge binary logs to 'binlog.000012'	將指定日志文件之前的日志清除
reset master	清空所有的binlog日志，慎用
show binlog events in 'binlog.000030'	查看指定的binlog日志event
show binlog events in 'binlog.000030' from 931	從指定的位置開始，查看指定的binlog日志
show binlog events in 'binlog.000030' from 931 limit 2	從指定的位置開始，查看指定的binlog日志，限制查詢的enent數

? 3.解析binlog內容

前面說過，所有對數據庫的修改都會記錄在binglog中。但binlog是二進制文件，無法直接查看，想要更直觀的觀測它就要借助mysqlbinlog命令工具了，下面的內容主要介紹如何使用mysqlbinlog來解析binlog日志內容。

為了故事的順利發展，我們首先切換下binlog，然后創建測試庫、測試表，執行插入數據，更新數據。這些前置操作暫不展示，下面我們來看下如何解析并查看生成的binlog內容：

#?本次解析基于MySQL8.0版本，實例已開啟gtid，模式為ROW

[root@centos?logs]#?mysqlbinlog??--no-defaults?--base64-output=decode-rows?-vv?binlog.000013
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003?SET?@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...
...
#200708?16:52:09?server?id?1003306??end_log_pos?1049?CRC32?0xbcf3de39???Query???thread_id=85????exec_time=0?????error_code=0????Xid?=?1514
use?`bindb`/*!*/;
SET?TIMESTAMP=1594198329/*!*/;
SET?@@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013?SET?@@session.sql_require_primary_key=0*//*!*/;
CREATE?TABLE??`bin_tb`?(
??`increment_id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??`stu_id`?int(11)?NOT?NULL?COMMENT?'學號',
??`stu_name`?varchar(20)?DEFAULT?NULL?COMMENT?'學生姓名',
??`create_time`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'創建時間',
??`update_time`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'修改時間',
??PRIMARY?KEY?(`increment_id`)
)?ENGINE=InnoDB??DEFAULT?CHARSET=utf8?COMMENT='測試binlog'
/*!*/;
#?at?1049
#200708?16:52:45?server?id?1003306??end_log_pos?1128?CRC32?0xf19ea0a9???GTID????last_committed=2????????sequence_number=3???????rbr_only=yes????original_committed_timestamp=1594198365741300???immediate_commit_timestamp=1594198365741300????????transaction_length=468
/*!50718?SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED*//*!*/;
#?original_commit_timestamp=1594198365741300?(2020-07-08?16:52:45.741300?CST)
#?immediate_commit_timestamp=1594198365741300?(2020-07-08?16:52:45.741300?CST)
/*!80001?SET?@@session.original_commit_timestamp=1594198365741300*//*!*/;
/*!80014?SET?@@session.original_server_version=80019*//*!*/;
/*!80014?SET?@@session.immediate_server_version=80019*//*!*/;
SET?@@SESSION.GTID_NEXT=?'0032d819-2d32-11ea-91b5-5254002ae61f:24883'/*!*/;
#?at?1128
#200708?16:52:45?server?id?1003306??end_log_pos?1204?CRC32?0x5b4b03db???Query???thread_id=85????exec_time=0?????error_code=0
SET?TIMESTAMP=1594198365/*!*/;
BEGIN
/*!*/;
#?at?1204
#200708?16:52:45?server?id?1003306??end_log_pos?1268?CRC32?0xd4755d50???Table_map:?`bindb`.`bin_tb`?mapped?to?number?139
#?at?1268
#200708?16:52:45?server?id?1003306??end_log_pos?1486?CRC32?0x274cf734???Write_rows:?table?id?139?flags:?STMT_END_F
###?INSERT?INTO?`bindb`.`bin_tb`
###?SET
###???@1=1?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1001?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3='from1'?/*?VARSTRING(60)?meta=60?nullable=1?is_null=0?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###?INSERT?INTO?`bindb`.`bin_tb`
###?SET
###???@1=2?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1002?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3='dfsfd'?/*?VARSTRING(60)?meta=60?nullable=1?is_null=0?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
...
#?at?1486
#200708?16:52:45?server?id?1003306??end_log_pos?1517?CRC32?0x0437e777???Xid?=?1515
COMMIT/*!*/;
...
#?at?1596
#200708?16:54:35?server?id?1003306??end_log_pos?1681?CRC32?0x111539b6???Query???thread_id=85????exec_time=0?????error_code=0
SET?TIMESTAMP=1594198475/*!*/;
BEGIN
/*!*/;
#?at?1681
#200708?16:54:35?server?id?1003306??end_log_pos?1745?CRC32?0x6f0664ee???Table_map:?`bindb`.`bin_tb`?mapped?to?number?139
#?at?1745
#200708?16:54:35?server?id?1003306??end_log_pos?1939?CRC32?0xfafe7ae8???Update_rows:?table?id?139?flags:?STMT_END_F
###?UPDATE?`bindb`.`bin_tb`
###?WHERE
###???@1=5?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1005?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3='dsfsdg'?/*?VARSTRING(60)?meta=60?nullable=1?is_null=0?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###?SET
###???@1=5?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1005?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3=NULL?/*?VARSTRING(60)?meta=60?nullable=1?is_null=1?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198475?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###?UPDATE?`bindb`.`bin_tb`
###?WHERE
###???@1=6?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1006?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3='fgd'?/*?VARSTRING(60)?meta=60?nullable=1?is_null=0?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###?SET
###???@1=6?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@2=1006?/*?INT?meta=0?nullable=0?is_null=0?*/
###???@3=NULL?/*?VARSTRING(60)?meta=60?nullable=1?is_null=1?*/
###???@4=1594198365?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
###???@5=1594198475?/*?TIMESTAMP(0)?meta=0?nullable=0?is_null=0?*/
...
#?at?1939
#200708?16:54:35?server?id?1003306??end_log_pos?1970?CRC32?0x632a82b7???Xid?=?1516
COMMIT/*!*/;
SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;
DELIMITER?;
#?End?of?log?file
/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;

#?可以看出，binlog中詳細記錄了每條sql執行產生的變化，
并且包括執行時間、pos位點、server_id等系統值。

關于mysqlbinlog工具的使用技巧還有很多，例如只解析對某個庫的操作或者某個時間段內的操作等。簡單分享幾個常用的語句，更多操作可以參考官方文檔。

mysqlbinlog?--no-defaults?--base64-output=decode-rows?-vv?binlog.000013?>?/tmp/bin13.sql
將解析到的SQL導入文件中

mysqlbinlog?--no-defaults?--base64-output=decode-rows?-vv?--database=testdb?binlog.000013
只解析某個庫的操作

mysqlbinlog?--no-defaults?--base64-output=decode-rows?-vv?--start-datetime="2020-01-11?01:00:00"?--stop-datetime="2020-01-11?23:59:00"?binlog.000008
解析指定時間段內的操作

mysqlbinlog?--no-defaults?--base64-output=decode-rows?-vv?--start-position=204136360?--stop-position=204136499?binlog.000008
解析指定pos位點內的操作

mysqlbinlog?--no-defaults?--start-position=204136360?--stop-position=204136499?binlog.000008?|?mysql?-uroot?-pxxxx?testdb
在指定庫中恢復指定位點間的操作

總結：

不知不覺寫的挺長了，本文講述了各類binlog相關知識點，希望你讀完會對binlog有更深的認識。其實最重要的還是實踐，只有多學多用才能更好的掌握。這么硬核的知識，希望大家用到的時候可以拿來讀讀，歡迎各位轉發分享，讓更多人看到。

推薦閱讀

(點擊標題可跳轉閱讀)

大數據如何基于 Flink 進行實時計算？

一文解決MySQL時區相關問題

手把手教你看MySQL官方文檔

— END —

如果你喜歡我的文章

請在文末右下角點一下在看?

總結

以上是生活随笔為你收集整理的mysqlbinlog工具_带你解析MySQL binlog的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。