解析MySQL 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模式更安全，可以清楚記錄每行數據修改的細節。
二、binlog相關參數binlog默認情況下是不開啟的，不過一般情況下，初始化的時候建議在配置文件中增加log-bin參數來開啟binlog。
# 配置文件中增加log-bin配置[mysqld]log-bin = binlog# 不指定路徑默認在data目錄下，也可以指定路徑[mysqld]log-bin = /data/mysql/logs/binlog# 查看數據庫是否開啟了binlogshow 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也有很多，下面介紹下部分常用的語句：
前面說過，所有對數據庫的修改都會記錄在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 = 1514use `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=2sequence_number=3 rbr_only=yes original_committed_timestamp=1594198365741300 immediate_commit_timestamp=1594198365741300transaction_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=0SET 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 = 1515COMMIT/*!*/;...# at 1596#200708 16:54:35 server id 1003306 end_log_pos 1681 CRC32 0x111539b6 Query thread_id=85 exec_time=0 error_code=0SET 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 = 1516COMMIT/*!*/;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有更深的認識。其實最重要的還是實踐，只有多學多用才能更好的掌握。這么硬核的知識，希望大家用到的時候可以拿來讀讀，歡迎各位轉發分享，讓更多人看到。
以上就是解析MySQL binlog的詳細內容，更多關于MySQL binlog的資料請關注好吧啦網其它相關文章！
相關文章：
網公網安備