MySQL Binlog 介紹

Binlog 簡介

Mysql中一般有以下幾種日志：

日志類型

寫入日志的信息

錯誤日志

記錄在啟動，運行或停止Mysqld時遇到的問題

通用查詢日志

記錄建立的客戶端連接和執行的語句

二進制日志

記錄更改數據的語句

中繼日志

從復制主服務器接收的數據更改

慢查詢日志

記錄所有執行時間超過 long_query_time 秒的所有查詢或不使用索引的查詢

DDL日志（元數據日志）

元數據操作由DDL語句執行

本文主要介紹二進制日志 binlog。

MySQL 的二進制日志 binlog 可以說是 MySQL 最重要的日志，它記錄了所有的 DDL 和 DML 語句（除了數據查詢語句select、show等），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進制日志是事務安全型的。binlog 的主要目的是復制和恢復。

Binlog日志的兩個最重要的使用場景

MySQL主從復制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到master-slave數據一致的目的

Binlog 簡介

MySQL中一般有以下幾種日志：

本文主要介紹二進制日志 binlog。

MySQL 的二進制日志 binlog 可以說是 MySQL 最重要的日志，它記錄了所有的 DDL 和 DML 語句（除了數據查詢語句select、show等），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進制日志是事務安全型的。binlog 的主要目的是復制和恢復。

MySQL主從復制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到master-slave數據一致的目的

數據恢復：通過使用 mysqlbinlog工具來使恢復數據

注：筆者實驗的MySQL版本為：5.7.22

一般來說開啟binlog日志大概會有1%的性能損耗。

啟用binlog，通過配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 選項：

在配置文件中加入 log-bin 配置，表示啟用binlog，如果沒有給定值，寫成 log-bin=，則默認名稱為主機名。（注：名稱若帶有小數點，則只取第一個小數點前的部分作為名稱）

[mysqld]

log-bin=my-binlog-name

也可以通過 SET SQL_LOG_BIN=1 命令來啟用 binlog，通過 SET SQL_LOG_BIN=0 命令停用 binlog。啟用 binlog 之后須重啟MySQL才能生效。

#?是否啟用binlog日志

show?variables?like?'log_bin';

#?查看詳細的日志配置信息

show?global?variables?like?'%log%';

#?mysql數據存儲目錄

show?variables?like?'%dir%';

#?查看binlog的目錄

show?global?variables?like?"%log_bin%";

#?查看當前服務器使用的biglog文件及大小

show?binary?logs;

#?查看主服務器使用的biglog文件及大小

#?查看最新一個binlog日志文件名稱和Position

show?master?status;

#?事件查詢命令

#?IN?'log_name'?：指定要查詢的binlog文件名(不指定就是第一個binlog文件)

#?FROM?pos?：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)

#?LIMIT?[offset,]?：偏移量(不指定就是0)

#?row_count?：查詢總條數(不指定就是所有行)

show?binlog?events?[IN?'log_name']?[FROM?pos]?[LIMIT?[offset,]?row_count];

#?查看?binlog?內容

show?binlog?events;

#?查看具體一個binlog文件的內容?（in?后面為binlog的文件名）

show?binlog?events?in?'master.000003';

#?設置binlog文件保存事件，過期刪除，單位天

set?global?expire_log_days=3;

#?刪除當前的binlog文件

reset?master;

#?刪除slave的中繼日志

reset?slave;

#?刪除指定日期前的日志索引中binlog日志文件

purge?master?logs?before?'2019-03-09?14:00:00';

#?刪除指定日志文件

purge?master?logs?to?'master.000003';

對支持事務的引擎如InnoDB而言，必須要提交了事務才會記錄binlog。binlog 什么時候刷新到磁盤跟參數 sync_binlog 相關。

如果設置為0，則表示MySQL不控制binlog的刷新，由文件系統去控制它緩存的刷新；

如果設置為不為0的值，則表示每 sync_binlog 次事務，MySQL調用文件系統的刷新操作刷新binlog到磁盤中。

設為1是最安全的，在系統故障時最多丟失一個事務的更新，但是會對性能有所影響。

如果 sync_binlog=0 或 sync_binlog大于1，當發生電源故障或操作系統崩潰時，可能有一部分已提交但其binlog未被同步到磁盤的事務會被丟失，恢復程序將無法恢復這部分事務。

在MySQL 5.7.7之前，默認值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默認值1，這是最安全的選擇。一般情況下會設置為100或者0，犧牲一定的一致性來獲取更好的性能。

binlog日志包括兩類文件:

二進制日志索引文件（文件名后綴為.index）用于記錄所有有效的的二進制文件

二進制日志文件（文件名后綴為.00000*）記錄數據庫所有的DDL和DML語句事件

binlog是一個二進制文件集合，每個binlog文件以一個4字節的魔數開頭，接著是一組Events:

魔數：0xfe62696e對應的是0xfebin；

Event：每個Event包含header和data兩個部分；header提供了Event的創建時間，哪個服務器等信息，data部分提供的是針對該Event的具體信息，如具體數據的修改；

第一個Event用于描述binlog文件的格式版本，這個格式就是event寫入binlog文件的格式；

其余的Event按照第一個Event的格式版本寫入；

最后一個Event用于說明下一個binlog文件；

binlog的索引文件是一個文本文件，其中內容為當前的binlog文件列表

當遇到以下3種情況時，MySQL會重新生成一個新的日志文件，文件序號遞增：

MySQL服務器停止或重啟時

使用 flush logs 命令；

當 binlog 文件大小超過 max_binlog_size 變量的值時；

max_binlog_size 的最小值是4096字節，最大值和默認值是 1GB (1073741824字節)。事務被寫入到binlog的一個塊中，所以它不會在幾個二進制日志之間被拆分。因此，如果你有很大的事務，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的日志都記錄到當前日志文件中，直到事務結束，你可能會看到binlog文件大于 max_binlog_size 的情況。

記錄在二進制日志中的事件的格式取決于二進制記錄格式。支持三種格式類型：

STATEMENT：基于SQL語句的復制（statement-based replication, SBR）

ROW：基于行的復制（row-based replication, RBR）

MIXED：混合模式復制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默認的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默認值是 ROW。日志格式通過 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

每一條會修改數據的sql都會記錄在binlog中

優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO, 提高了性能。

缺點：由于記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在slave得到和在master端執行的時候相同的結果。另外mysql的復制，像一些特定函數的功能，slave與master要保持一致會有很多相關問題。

5.1.5版本的MySQL才開始支持 row level 的復制,它不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。

優點： binlog中可以不記錄執行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以row的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題.

缺點:所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容。

注：將二進制日志格式設置為ROW時，有些更改仍然使用基于語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。

在Mixed模式下，一般的語句修改使用statment格式保存binlog，如一些函數，statement無法完成主從復制的操作，則采用row格式保存binlog，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。

服務器以二進制格式將binlog日志寫入binlog文件，如何要以文本格式顯示其內容，可以使用 mysqlbinlog 命令。

#?mysqlbinlog?的執行格式

mysqlbinlog?[options]?log_file?...

#?查看bin-log二進制文件（shell方式）

mysqlbinlog?-v?--base64-output=decode-rows?/var/lib/mysql/master.000003

#?查看bin-log二進制文件（帶查詢條件）

mysqlbinlog?-v?--base64-output=decode-rows?/var/lib/mysql/master.000003?\

--start-datetime="2019-03-01?00:00:00"??\

--stop-datetime="2019-03-10?00:00:00"???\

--start-position="5000"????\

--stop-position="20000"

設置日志格式為ROW時，在我的機器上輸出了以下信息

/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003?SET?@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER?/*!*/;

#?at?4

#190308?10:05:03?server?id?1??end_log_pos?123?CRC32?0xff02e23d?????Start:?binlog?v?4,?server?v?5.7.22-log?created?190308?10:05:03

#?Warning:?this?binlog?is?either?in?use?or?was?not?closed?properly.

#?at?123

#190308?10:05:03?server?id?1??end_log_pos?154?CRC32?0xb81da4c5?????Previous-GTIDs

#?[empty]

#?at?154

#190308?10:05:09?server?id?1??end_log_pos?219?CRC32?0xfb30d42c?????Anonymous_GTID??last_committed=0????sequence_number=1???rbr_only=yes

/*!50718?SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED*//*!*/;

SET?@@SESSION.GTID_NEXT=?'ANONYMOUS'/*!*/;

#?at?219

...

...

#?at?21019

#190308?10:10:09?server?id?1??end_log_pos?21094?CRC32?0x7a405abc?????Query???thread_id=113???exec_time=0?error_code=0

SET?TIMESTAMP=1552011009/*!*/;

BEGIN

/*!*/;

#?at?21094

#190308?10:10:09?server?id?1??end_log_pos?21161?CRC32?0xdb7a2b35?????Table_map:?`maxwell`.`positions`?mapped?to?number?110

#?at?21161

#190308?10:10:09?server?id?1??end_log_pos?21275?CRC32?0xec3be372?????Update_rows:?table?id?110?flags:?STMT_END_F

###?UPDATE?`maxwell`.`positions`

###?WHERE

###???@1=1

###???@2='master.000003'

###???@3=20262

###???@4=NULL

###???@5='maxwell'

###???@6=NULL

###???@7=1552011005707

###?SET

###???@1=1

###???@2='master.000003'

###???@3=20923

###???@4=NULL

###???@5='maxwell'

###???@6=NULL

###???@7=1552011009790

#?at?21275

#190308?10:10:09?server?id?1??end_log_pos?21306?CRC32?0xe6c4346d?????Xid?=?13088

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*/;

截取其中的一段進行分析：

#?at?21019

#190308?10:10:09?server?id?1??end_log_pos?21094?CRC32?0x7a405abc?????Query???thread_id=113???exec_time=0?error_code=0

SET?TIMESTAMP=1552011009/*!*/;

BEGIN

/*!*/;

上面輸出包括信息：

position: 位于文件中的位置，即第一行的（# at 21019）,說明該事件記錄從文件第21019個字節開始

timestamp: 事件發生的時間戳，即第二行的（#190308 10:10:09）

server id: 服務器標識（1）

end_log_pos 表示下一個事件開始的位置（即當前事件的結束位置+1）

thread_id: 執行該事件的線程id （thread_id=113）

exec_time: 事件執行的花費時間

error_code: 錯誤碼，0意味著沒有發生錯誤

type:事件類型Query

binlog 事件的結構主要有3個版本：

v1: 在 MySQL 3.23 中使用

v3: 在 MySQL 4.0.2 到 4.1 中使用

v4: 在 MySQL 5.0 及以上版本中使用

現在一般不會使用MySQL5.0以下版本，所以下面僅介紹v4版本的binlog事件類型。binlog 的事件類型較多，本文在此做一些簡單的匯總

一個事件對象分為事件頭和事件體，事件的結構如下：

+=====================================+

|?event??|?timestamp?????????0?:?4????|

|?header?+----------------------------+

|????????|?type_code?????????4?:?1????|

|????????+----------------------------+

|????????|?server_id?????????5?:?4????|

|????????+----------------------------+

|????????|?event_length??????9?:?4????|

|????????+----------------------------+

|????????|?next_position????13?:?4????|

|????????+----------------------------+

|????????|?flags????????????17?:?2????|

|????????+----------------------------+

|????????|?extra_headers????19?:?x-19?|

+=====================================+

|?event??|?fixed?part????????x?:?y????|

|?data???+----------------------------+

|????????|?variable?part??????????????|

+=====================================+

如果事件頭的長度是 x 字節，那么事件體的長度為 (event_length - x) 字節；設事件體中 fixed part 的長度為 y 字節，那么 variable part 的長度為 (event_length - (x + y)) 字節

從一個最簡單的實例來分析Event，包括創建表，插入數據，更新數據，刪除數據；

CREATE?TABLE?`test`?(

`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,

`age`?int(11)?DEFAULT?NULL,

`name`?varchar(255)?DEFAULT?NULL,

PRIMARY?KEY?(`id`)

)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

insert?into?test?values(1,22,"小旋鋒");

update?test?set?name='whirly'?where?id=1;

delete?from?test?where?id=1;

日志格式為STATEMENT，查看所有的Event

日志格式為ROW時是下面這樣，可以發現又有一些不同

關于Event的分析，有需要可以查看參考文檔進行推算。

MySQL 5.7參考手冊.二進制日志

MySQL Internals Manual.The Binary Log

朱小廝.MySQL Binlog解析

七把刀.MySQL binlog格式解析

散盡浮華.Mysql之binlog日志說明及利用binlog日志恢復數據操作記錄

MySql Binlog 初識

MySQL5.7殺手級新特性：GTID原理與實戰

MySQL 5.7 基于 GTID 的主從復制實踐

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。