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資料一致的目的
- 資料恢復：通過使用 mysqlbinlog工具來使恢復資料
啟用 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=，則預設名稱為主機名。（注：名稱若帶有小數點，則只取第一個小數點前的部分作為名稱）
[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資料儲存目錄
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';
複製程式碼寫 Binlog 的時機
對支援事務的引擎如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 檔案以及擴充套件
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 的情況。
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
每一條會修改資料的sql都會記錄在binlog中
優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO, 提高了效能。
缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確執行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行的時候相同的結果。另外mysql的複製，像一些特定函式的功能，slave與master要保持一致會有很多相關問題。
Row
5.1.5版本的MySQL才開始支援 row level 的複製,它不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。
優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以row的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程，或function，以及trigger的呼叫和觸發無法被正確複製的問題.
缺點:所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容。
注：將二進位制日誌格式設定為ROW時，有些更改仍然使用基於語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。
Mixed
從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。 在Mixed模式下，一般的語句修改使用statment格式儲存binlog，如一些函式，statement無法完成主從複製的操作，則採用row格式儲存binlog，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種。
mysqlbinlog 命令的使用
伺服器以二進位制格式將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 事件型別
binlog 事件的結構主要有3個版本：
- v1: 在 MySQL 3.23 中使用
- v3: 在 MySQL 4.0.2 到 4.1 中使用
- v4: 在 MySQL 5.0 及以上版本中使用
現在一般不會使用MySQL5.0以下版本，所以下面僅介紹v4版本的binlog事件型別。binlog 的事件型別較多，本文在此做一些簡單的彙總
| 事件型別 | 說明 |
|---|---|
| UNKNOWN_EVENT | 此事件從不會被觸發，也不會被寫入binlog中；發生在當讀取binlog時，不能被識別其他任何事件，那被視為UNKNOWN_EVENT |
| START_EVENT_V3 | 每個binlog檔案開始的時候寫入的事件，此事件被用在MySQL3.23 – 4.1，MYSQL5.0以後已經被 FORMAT_DESCRIPTION_EVENT 取代 |
| QUERY_EVENT | 執行更新語句時會生成此事件，包括：create，insert，update，delete； |
| STOP_EVENT | 當mysqld停止時生成此事件 |
| ROTATE_EVENT | 當mysqld切換到新的binlog檔案生成此事件，切換到新的binlog檔案可以通過執行flush logs命令或者binlog檔案大於 max_binlog_size 引數配置的大小； |
| INTVAR_EVENT | 當sql語句中使用了AUTO_INCREMENT的欄位或者LAST_INSERT_ID()函式；此事件沒有被用在binlog_format為ROW模式的情況下 |
| LOAD_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL 3.23版本中使用 |
| SLAVE_EVENT | 未使用 |
| CREATE_FILE_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL4.0和4.1版本中使用 |
| APPEND_BLOCK_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL4.0版本中使用 |
| EXEC_LOAD_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL4.0和4.1版本中使用 |
| DELETE_FILE_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL4.0版本中使用 |
| NEW_LOAD_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL4.0和4.1版本中使用 |
| RAND_EVENT | 執行包含RAND()函式的語句產生此事件，此事件沒有被用在binlog_format為ROW模式的情況下 |
| USER_VAR_EVENT | 執行包含了使用者變數的語句產生此事件，此事件沒有被用在binlog_format為ROW模式的情況下 |
| FORMAT_DESCRIPTION_EVENT | 描述事件，被寫在每個binlog檔案的開始位置，用在MySQL5.0以後的版本中，代替了START_EVENT_V3 |
| XID_EVENT | 支援XA的儲存引擎才有，本地測試的資料庫儲存引擎是innodb，所有上面出現了XID_EVENT；innodb事務提交產生了QUERY_EVENT的BEGIN宣告，QUERY_EVENT以及COMMIT宣告，如果是myIsam儲存引擎也會有BEGIN和COMMIT宣告，只是COMMIT型別不是XID_EVENT |
| BEGIN_LOAD_QUERY_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL5.0版本中使用 |
| EXECUTE_LOAD_QUERY_EVENT | 執行LOAD DATA INFILE 語句時產生此事件，在MySQL5.0版本中使用 |
| TABLE_MAP_EVENT | 用在binlog_format為ROW模式下，將表的定義對映到一個數字，在行操作事件之前記錄（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT） |
| PRE_GA_WRITE_ROWS_EVENT | 已過期，被 WRITE_ROWS_EVENT 代替 |
| PRE_GA_UPDATE_ROWS_EVENT | 已過期，被 UPDATE_ROWS_EVENT 代替 |
| PRE_GA_DELETE_ROWS_EVENT | 已過期，被 DELETE_ROWS_EVENT 代替 |
| WRITE_ROWS_EVENT | 用在binlog_format為ROW模式下，對應 insert 操作 |
| UPDATE_ROWS_EVENT | 用在binlog_format為ROW模式下，對應 update 操作 |
| DELETE_ROWS_EVENT | 用在binlog_format為ROW模式下，對應 delete 操作 |
| INCIDENT_EVENT | 主伺服器發生了不正常的事件，通知從伺服器並告知可能會導致資料處於不一致的狀態 |
| HEARTBEAT_LOG_EVENT | 主伺服器告訴從伺服器，主伺服器還活著，不寫入到日誌檔案中 |
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)) 位元組
Binlog Event 簡要分析
從一個最簡單的例項來分析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 的主從複製實踐