Binlog 簡(jiǎn)介

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

本文主要介紹二進(jìn)制日志 binlog。

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

Binlog日志的兩個(gè)最重要的使用場(chǎng)景

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

啟用 Binlog

注：筆者實(shí)驗(yàn)的MySQL版本為：5.7.22

一般來(lái)說開啟binlog日志大概會(huì)有1%的性能損耗。

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

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

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

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

常用的Binlog操作命令

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

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

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

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

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

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

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


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

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

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

# 設(shè)置binlog文件保存事件，過期刪除，單位天
set global expire_log_days=3; 

# 刪除當(dāng)前的binlog文件
reset master; 

# 刪除slave的中繼日志
reset slave;

# 刪除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 刪除指定日志文件
purge master logs to 'master.000003';

寫 Binlog 的時(shí)機(jī)

對(duì)支持事務(wù)的引擎如InnoDB而言，必須要提交了事務(wù)才會(huì)記錄binlog。binlog 什么時(shí)候刷新到磁盤跟參數(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)故障時(shí)最多丟失一個(gè)事務(wù)的更新，但是會(huì)對(duì)性能有所影響。

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

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

Binlog 文件以及擴(kuò)展

binlog日志包括兩類文件:

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

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

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

當(dāng)遇到以下3種情況時(shí)，MySQL會(huì)重新生成一個(gè)新的日志文件，文件序號(hào)遞增：

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

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

Binlog 的日志格式

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

• STATEMENT：基于SQL語(yǔ)句的復(fù)制（statement-based replication, SBR）
• ROW：基于行的復(fù)制（row-based replication, RBR）
• MIXED：混合模式復(fù)制（mixed-based replication, MBR）

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

Statement

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

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

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

Row

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

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

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

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

Mixed

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

mysqlbinlog 命令的使用

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

# mysqlbinlog 的執(zhí)行格式
mysqlbinlog [options] log_file ...

# 查看bin-log二進(jìn)制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003

# 查看bin-log二進(jìn)制文件（帶查詢條件）
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時(shí)，在我的機(jī)器上輸出了以下信息

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

截取其中的一段進(jìn)行分析：

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

Binlog 事件類型

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

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

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

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

一個(gè)事件對(duì)象分為事件頭和事件體，事件的結(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              |
+=====================================+

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

Binlog Event 簡(jiǎn)要分析

從一個(gè)最簡(jiǎn)單的實(shí)例來(lái)分析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時(shí)是下面這樣，可以發(fā)現(xiàn)又有一些不同

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

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

參考文檔

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

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