解析MySQL binlog

一、binlog簡介

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

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

binlog日志可以選擇三種模式,分別是 STATEMENTROWMIXED,下面簡單介紹下這三種模式:

  • 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

# 查看數據庫是否開啟瞭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也有很多,下面介紹下部分常用的語句:

三、解析binlog內容

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

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

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

[[email protected] logs]# mysqlbinlog  --no-defaults --base64-output=decode-rows -vv binlog.000013
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @[email protected]@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 [email protected]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的資料請關註WalkonNet其它相關文章!