什么是MySQL binlog

發布時間：2021-11-01 09:33:18 來源：億速云閱讀：174 作者：iii 欄目：MySQL數據庫

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

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

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

什么是MySQL binlog

1.binlog簡介

2.binlog相關參數

3.解析binlog內容

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

什么是MySQL binlog

1.binlog簡介

2.binlog相關參數

3.解析binlog內容

猜你喜歡

最新資訊

相關推薦

相關標簽