解析MySQL binlog
一、binlog簡(jiǎn)介
binlog即binary log，二進(jìn)制日志文件。它記錄了數(shù)據(jù)庫(kù)所有執(zhí)行的DDL和DML語(yǔ)句（除了數(shù)據(jù)查詢語(yǔ)句select、show等），以事件形式記錄并保存在二進(jìn)制文件中。
binlog主要有兩個(gè)應(yīng)用場(chǎng)景，一是用于復(fù)制，master把它的二進(jìn)制日志傳遞給slaves來(lái)達(dá)到master-slave數(shù)據(jù)一致的目的。二是用于數(shù)據(jù)恢復(fù)，例如還原備份后，可以重新執(zhí)行備份后新產(chǎn)生的binlog，使得數(shù)據(jù)庫(kù)保持最新?tīng)顟B(tài)。除去這兩個(gè)主要用途外，binlog可以用于異構(gòu)系統(tǒng)之間數(shù)據(jù)的交互，binlog完整保存了一條記錄的前項(xiàng)和后項(xiàng)記錄，可以用DTS服務(wù)，將MySQL數(shù)據(jù)以準(zhǔn)實(shí)時(shí)的方式抽取到底層數(shù)據(jù)平臺(tái)，比如HBase、Hive、Spark等，打通OLTP和OLAP。
binlog日志可以選擇三種模式，分別是 STATEMENT、 ROW、 MIXED，下面簡(jiǎn)單介紹下這三種模式：
- STATEMENT：基于SQL語(yǔ)句的復(fù)制，每一條會(huì)修改數(shù)據(jù)的sql語(yǔ)句會(huì)記錄到binlog中。該模式下產(chǎn)生的binlog日志量會(huì)比較少，但可能導(dǎo)致主從數(shù)據(jù)不一致。
- ROW：基于行的復(fù)制，不記錄每一條具體執(zhí)行的SQL語(yǔ)句，僅需記錄哪條數(shù)據(jù)被修改了，以及修改前后的樣子。該模式下產(chǎn)生的binlog日志量會(huì)比較大，但優(yōu)點(diǎn)是會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，主從復(fù)制不會(huì)出錯(cuò)。
- Mixed：混合模式復(fù)制，以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式。
binlog模式在MySQL 5.7.7之前，默認(rèn)為 STATEMENT，在之后的版本中，默認(rèn)為ROW。這里建議采用ROW模式，因?yàn)镽OW模式更安全，可以清楚記錄每行數(shù)據(jù)修改的細(xì)節(jié)。
二、binlog相關(guān)參數(shù)
binlog默認(rèn)情況下是不開(kāi)啟的，不過(guò)一般情況下，初始化的時(shí)候建議在配置文件中增加log-bin參數(shù)來(lái)開(kāi)啟binlog。
# 配置文件中增加log-bin配置 [mysqld] log-bin = binlog # 不指定路徑默認(rèn)在data目錄下，也可以指定路徑 [mysqld] log-bin = /data/mysql/logs/binlog # 查看數(shù)據(jù)庫(kù)是否開(kāi)啟了binlog show variables like 'log_bin%';
開(kāi)啟binlog后，還需注意一些與binlog相關(guān)的參數(shù)，下面簡(jiǎn)單介紹下相關(guān)參數(shù)：
binlog_format
設(shè)置binlog模式，建議設(shè)為ROW。binlog_do_db
此參數(shù)表示只記錄指定數(shù)據(jù)庫(kù)的二進(jìn)制日志，默認(rèn)全部記錄，一般情況下不建議更改。binlog_ignore_db
此參數(shù)表示不記錄指定的數(shù)據(jù)庫(kù)的二進(jìn)制日志，同上，一般不顯式指定。expire_logs_days
此參數(shù)控制二進(jìn)制日志文件保留天數(shù)，默認(rèn)值為0，表示不自動(dòng)刪除，可設(shè)置為0~99?？筛鶕?jù)實(shí)際情況設(shè)置，比如保留15天或30天。MySQL8.0版本可用binlog_expire_logs_seconds參數(shù)代替。max_binlog_size
控制單個(gè)二進(jìn)制日志大小，當(dāng)前日志文件大小超過(guò)此變量時(shí)，執(zhí)行切換動(dòng)作。此參數(shù)的最大和默認(rèn)值是1GB，該設(shè)置并不能?chē)?yán)格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個(gè)比較大事務(wù)時(shí)，為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束。一般情況下可采取默認(rèn)值。log_bin_trust_function_creators
當(dāng)二進(jìn)制日志啟用后，此參數(shù)就會(huì)啟用。它控制是否可以信任存儲(chǔ)函數(shù)創(chuàng)建者，不會(huì)創(chuàng)建寫(xiě)入二進(jìn)制日志引起不安全事件的存儲(chǔ)函數(shù)。如果設(shè)置為0（默認(rèn)值），用戶不得創(chuàng)建或修改存儲(chǔ)函數(shù)，除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權(quán)之外的SUPER權(quán)限。建議設(shè)置為1。sync_binlog
控制MySQL服務(wù)端將二進(jìn)制日志同步到磁盤(pán)的頻率，默認(rèn)值為1。
設(shè)置為0，表示MySQL不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新；
設(shè)置為1，表示每次事務(wù)提交，MySQL都會(huì)把binlog刷下去，這是最安全的設(shè)置，但由于磁盤(pán)寫(xiě)入次數(shù)增加，可能會(huì)對(duì)性能產(chǎn)生負(fù)面影響；
設(shè)置為n，其中n為0或1以外的值，在進(jìn)行n次事務(wù)提交以后，Mysql將執(zhí)行一次fsync之類的磁盤(pán)同步指令，將Binlog文件緩存刷新到磁盤(pán)。
推薦設(shè)置為1，出于性能考慮也可酌情調(diào)整。
關(guān)于binlog操作與管理相關(guān)的SQL也有很多，下面介紹下部分常用的語(yǔ)句：
三、解析binlog內(nèi)容
前面說(shuō)過(guò)，所有對(duì)數(shù)據(jù)庫(kù)的修改都會(huì)記錄在binglog中。但binlog是二進(jìn)制文件，無(wú)法直接查看，想要更直觀的觀測(cè)它就要借助mysqlbinlog命令工具了，下面的內(nèi)容主要介紹如何使用mysqlbinlog來(lái)解析binlog日志內(nèi)容。
為了故事的順利發(fā)展，我們首先切換下binlog，然后創(chuàng)建測(cè)試庫(kù)、測(cè)試表，執(zhí)行插入數(shù)據(jù)，更新數(shù)據(jù)。這些前置操作暫不展示，下面我們來(lái)看下如何解析并查看生成的binlog內(nèi)容：
# 本次解析基于MySQL8.0版本，實(shí)例已開(kāi)啟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 '學(xué)號(hào)', `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間', PRIMARY KEY (`increment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測(cè)試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中詳細(xì)記錄了每條sql執(zhí)行產(chǎn)生的變化， 并且包括執(zhí)行時(shí)間、pos位點(diǎn)、server_id等系統(tǒng)值。
關(guān)于mysqlbinlog工具的使用技巧還有很多，例如只解析對(duì)某個(gè)庫(kù)的操作或者某個(gè)時(shí)間段內(nèi)的操作等。簡(jiǎn)單分享幾個(gè)常用的語(yǔ)句，更多操作可以參考官方文檔。
mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql
將解析到的SQL導(dǎo)入文件中mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013
只解析某個(gè)庫(kù)的操作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
解析指定時(shí)間段內(nèi)的操作mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008
解析指定pos位點(diǎn)內(nèi)的操作mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb
在指定庫(kù)中恢復(fù)指定位點(diǎn)間的操作
四、總結(jié)
不知不覺(jué)寫(xiě)的挺長(zhǎng)了，本文講述了各類binlog相關(guān)知識(shí)點(diǎn)，希望你讀完會(huì)對(duì)binlog有更深的認(rèn)識(shí)。其實(shí)最重要的還是實(shí)踐，只有多學(xué)多用才能更好的掌握。這么硬核的知識(shí)，希望大家用到的時(shí)候可以拿來(lái)讀讀，歡迎各位轉(zhuǎn)發(fā)分享，讓更多人看到。
以上就是解析MySQL binlog的詳細(xì)內(nèi)容，更多關(guān)于MySQL binlog的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信