什么是MySQL binlog

126次閱讀

共計 8290 個字符，預計需要花費 21 分鐘才能閱讀完成。

這篇文章主要講解了“什么是 MySQL binlog”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“什么是 MySQL 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 日志 eventshow 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
在指定庫中恢復指定位點間的操作

感謝各位的閱讀，以上就是“什么是 MySQL binlog”的內容了，經過本文的學習后，相信大家對什么是 MySQL binlog 這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完