Binlog 簡介

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

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

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

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

• MySQL主從復制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到master-slave數(shù)據(jù)一致的目的
• 數(shù)據(jù)恢復：通過使用 mysqlbinlog工具來使恢復數(shù)據(jù)

啟用 Binlog

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

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

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

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

[mysqld]
log-bin=my-binlog-name

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

常用的Binlog操作命令

# 是否啟用binlog日志
show variables like 'log_bin';

# 查看詳細的日志配置信息
show global variables like '%log%';

# mysql數(shù)據(jù)存儲目錄
show variables like '%dir%';

# 查看binlog的目錄
show global variables like "%log_bin%";

# 查看當前服務(wù)器使用的biglog文件及大小
show binary logs;

# 查看主服務(wù)器使用的biglog文件及大小

# 查看最新一個binlog日志文件名稱和Position
show master status;


# 事件查詢命令
# IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個binlog文件)
# FROM pos ：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查詢總條數(shù)(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 內(nèi)容
show binlog events;

# 查看具體一個binlog文件的內(nèi)容 （in 后面為binlog的文件名）
show binlog events in 'master.000003';

# 設(shè)置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';

寫 Binlog 的時機

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

• 如果設(shè)置為0，則表示MySQL不控制binlog的刷新，由文件系統(tǒng)去控制它緩存的刷新；
• 如果設(shè)置為不為0的值，則表示每 sync_binlog 次事務(wù)，MySQL調(diào)用文件系統(tǒng)的刷新操作刷新binlog到磁盤中。
• 設(shè)為1是最安全的，在系統(tǒng)故障時最多丟失一個事務(wù)的更新，但是會對性能有所影響。

如果 sync_binlog=0 或 sync_binlog大于1，當發(fā)生電源故障或操作系統(tǒng)崩潰時，可能有一部分已提交但其binlog未被同步到磁盤的事務(wù)會被丟失，恢復程序?qū)o法恢復這部分事務(wù)。

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

Binlog 文件以及擴展

binlog日志包括兩類文件:

• 二進制日志索引文件（文件名后綴為.index）用于記錄所有有效的的二進制文件
• 二進制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML語句事件

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

• 魔數(shù)：0xfe62696e對應(yīng)的是0xfebin；
• Event：每個Event包含header和data兩個部分；header提供了Event的創(chuàng)建時間，哪個服務(wù)器等信息，data部分提供的是針對該Event的具體信息，如具體數(shù)據(jù)的修改；
• 第一個Event用于描述binlog文件的格式版本，這個格式就是event寫入binlog文件的格式；
• 其余的Event按照第一個Event的格式版本寫入；
• 最后一個Event用于說明下一個binlog文件；
• binlog的索引文件是一個文本文件，其中內(nèi)容為當前的binlog文件列表

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

• MySQL服務(wù)器停止或重啟時
• 使用 flush logs 命令；
• 當 binlog 文件大小超過 max_binlog_size 變量的值時；

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

Binlog 的日志格式

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

• 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。

Statement

每一條會修改數(shù)據(jù)的sql都會記錄在binlog中

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

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

Row

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

優(yōu)點： binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以row的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調(diào)用和觸發(fā)無法被正確復制的問題.

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

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

Mixed

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結(jié)合。
在Mixed模式下，一般的語句修改使用statment格式保存binlog，如一些函數(shù)，statement無法完成主從復制的操作，則采用row格式保存binlog，MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。

mysqlbinlog 命令的使用

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

# mysqlbinlog 的執(zhí)行格式
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"

設(shè)置日志格式為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個字節(jié)開始
• timestamp: 事件發(fā)生的時間戳，即第二行的（#190308 10:10:09）
• server id: 服務(wù)器標識（1）
• end_log_pos 表示下一個事件開始的位置（即當前事件的結(jié)束位置+1）
• thread_id: 執(zhí)行該事件的線程id （thread_id=113）
• exec_time: 事件執(zhí)行的花費時間
• error_code: 錯誤碼，0意味著沒有發(fā)生錯誤
• type:事件類型Query

Binlog 事件類型

binlog 事件的結(jié)構(gòu)主要有3個版本：

• v1: 在 MySQL 3.23 中使用
• v3: 在 MySQL 4.0.2 到 4.1 中使用
• v4: 在 MySQL 5.0 及以上版本中使用

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

Binlog 事件的結(jié)構(gòu)

一個事件對象分為事件頭和事件體，事件的結(jié)構(gòu)如下：

+=====================================+
| 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 字節(jié)，那么事件體的長度為 (event_length - x) 字節(jié)；設(shè)事件體中 fixed part 的長度為 y 字節(jié)，那么 variable part 的長度為 (event_length - (x + y)) 字節(jié)

Binlog Event 簡要分析

從一個最簡單的實例來分析Event，包括創(chuàng)建表，插入數(shù)據(jù)，更新數(shù)據(jù)，刪除數(shù)據(jù)；

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

STATEMENT格式下create、insert、update、delete操作產(chǎn)生的binlog事件

日志格式為ROW時是下面這樣，可以發(fā)現(xiàn)又有一些不同

ROW格式下create、insert、update、delete操作產(chǎn)生的binlog事件

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

參考文檔

• MySQL 5.7參考手冊.二進制日志
• MySQL Internals Manual.The Binary Log
• 朱小廝.MySQL Binlog解析
• 七把刀.MySQL binlog格式解析
• 散盡浮華.Mysql之binlog日志說明及利用binlog日志恢復數(shù)據(jù)操作記錄
• MySql Binlog 初識
• MySQL5.7殺手級新特性：GTID原理與實戰(zhàn)
• MySQL 5.7 基于 GTID 的主從復制實踐

關(guān)注_小旋鋒_微信公眾號