深入理解MySQL binlog
binlog 是 MySQL 最為重要的日志文件，MySQL 數據的備份恢復、主從復制都依賴 binlog，相比于其他日志文件，binlog 可以說是剛需了。
binlog 常見命令
為了對 binlog 有一個直觀的認知，我們先來看幾個命令。
首先，默認情況下 binlog 并不會開啟，我們可以通過下面的 show variabkes like‘log_bin’命令查看 binlog 的開啟狀態：
MySQL> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+如果你需要開啟 binlog，可以在 my.ini 配置文件中可以加入下面的配置來開啟：
log_bin=/home/MySQL/bin-log.log開啟 binlog 以后，我們就可以看看 binlog 的內容了。由于 binlog 本身是二進制文件，我們不能直接打開查看，但我們只關心 binlog 內容的意義而不是內容本身，因此我們可以用 MySQL 提供的如下命令來查看 binlog 內容：
show binlog events;在 MySQL 中，輸入上述命令后，我們能看到如下所示的返回結果：
MySQL> show binlog events;
+----------------+------+-------------+-----------+-------------+---------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
+----------------+------+-------------+-----------+-------------+---------------
| bin-log.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.7.29-log, Binlog ver: 4 |
| bin-log.000001 | 99 | Query | 1 | 175 | BEGIN
|
| bin-log.000001 | 166 | Intvar | 1 | 203 | INSERT_ID=9
|
| bin-log.000001 | 200 | Query | 1 | 315 |insert into user (age,name) values(1,"test") |
| bin-log.000001 | 331 | Xid | 1 | 342 | COMMIT /* xid=22 */ |
+----------------+------+-------------+-----------+-------------+---------------在這個返回的結果中，最重要的是 Event_type 和 Info 這兩個字段，其余字段不影響我們學習理解 binlog，就不贅述了。其中，Info 是命令的具體內容，可以看到 Info 里面是具體執行的 SQL 語句。而 Event_type 是事件類型，它的作用是記錄我們對 MySQL 所有變更操作的類型。
目前 MySQL 有 30 多種事件類型，歸類了你對 MySQL 的所有變更操作。這些操作日志的內容共同組成了我們的 binlog，其中 Event_type 是 binlog 日志中的一個字段，因此認識 Event_type 對理解 binlog 的內容非常重要，我們后面會挑選幾個 Event_type 類型介紹。
通過上面的返回的結果，我們可以看到，每個指令、每個動作都是一行記錄，而在 binlog 里每一行被稱作一個事件。也就是說 binlog 是由一個又一個事件組成的。
binlog 文件格式
在講解 binlog 事件之前，我們先看看 binlog 的文件格式分類。binlog 有 3 種格式類型，分別是 STATEMENT、ROW、MIXED：
- STATEMENT
STATEMENT 是 binlog 的默認格式，我們后面對 binlog 的分析也是基于 STATEMENT 格式進行的。這個格式下，每一條修改數據的 sql 都會被記錄到 binlog 中，slave 端再根據 sql 語句重現。
但 STATEMENT 的缺點也很明顯，比如你用了 uuid 或者 now 這些函數，你在主庫上執行的結果并不是你在從庫執行的結果，這種隨時在變的函數會導致復制的數據不一致。
- ROW
在 ROW 模式下，我們只需要知道行數據最終被修改成什么樣了，不會出現 STATEMENT 下動態函數的問題。但 ROW 的缺點是每行數據的變化結果都會被記錄，比如執行批量 update 語句，更新多少行數據就會產生多少條記錄，使 binlog 文件過大，而在 STATEMENT 格式下只會記錄一個 update 語句而已。
- MIXED
MIXED 包含了 STATEMENT 和 ROW 模式，它會根據不同的情況自動使用 ROW 模式和 STATEMENT 模式，MIXED 除了包含 STATEMENT 和 ROW 特性之外，還有一些針對自身優化的特性，比如壓縮方式和一些特殊標記等等。
binlog 事件
在前面我們說了，binlog 由若干個事件組成，其中開頭的第一個事件叫 Format_description，中文翻譯為格式描述事件，文件結尾的最后一個事件叫做 rotate，中文翻譯為日志輪換事件。其中 Format_description 包含了 binlog 的服務器信息、文件狀態的關鍵信息等。
如果 MySQL 服務關閉或者重啟，那么 MySQL 進程會自動創建一個新的 binlog，同時寫入一個新的 Format_description。簡單來說 Format_description 就是一個文件頭。rotate 則包含下一個 binlog 的文件信息，它由 MySQL 寫完 binlog 后添加到 binlog 的末尾，注意 rotate 只有當 binlog 寫完才會有，binlog 沒有寫完的情況下是沒有 rotate 的。
binlog 的基礎結構如下，中間黃色部分就是我們執行的每一個事件。除了 select，剩下所有操作基本都會被記錄，binlog 不記錄 select 是因為 select 不會產生變更。
圖片
binlog 對所有產生變化的操作做了分類，我們挑幾類常見的介紹一下：
- QUERY
這是最常見的類型，執行更新語句時會生成此事件，包括：create，insert，update，delete 等等。比如我們手動執行一個插入語句，然后再使用上面提到的 show binlog events 看一下下面的 binlog 文件內容：
insert into test values(1,'yafeng');
| bin-log.000001 | 412 | Query | 1 | 536 | insert into test values(1,'yafeng'); |
| bin-log.000001 | 520 | Xid | 1 | 563 | COMMIT /* xid=30 */和前面的日志內容相比，我們可以看到，經過 insert 操作，binlog 文件中多了一個 Query 類型的記錄。
- XID
事務提交時產生的事件，上面 insert 語句就是一個事務，因此除了 QUERY 記錄還產生了一條 XID 記錄。
- FORMAT_DESCRIPTION
我們在前面提到了 FORMAT_DESCRIPTION，它是 binlog 的文件頭記錄。
- ROTATE
當 binlog 寫完后會另啟一個新 binlog 來記錄日志，舊的 binlog 文件末尾會追加這個事件，什么情況下 binlog 會寫完呢？有 3 種情況，一個是手動執行 flush logs 命令，第二是重啟 MySQL，第三是 binlog 文件大于 max_binlog_size 參數配置的大小。
- INTVAR
SQL 中使用了 AUTO_INCREMENT 的字段，就會產生這個事件。
- STOP
當 MySQL 停止時會生成此事件，是的，你沒聽錯，連 MySQL 的停止也會被記錄到 binlog。
- RAND
SQL 中包含隨機數函數的語句將產生 RAND 事件。
以上幾個事件是 STATEMENT 格式下常見的幾個，還有一些事件工作在 ROW 和 MIXED 格式下。binlog 在記錄每個事件的時候也使用了 header 和 body 的格式，即 header 存儲元數據，body 存儲具體執行的語句。當然這并不是很重要，因為計算機領域中從網絡協議到虛擬機到操作系統幾乎都采用了類似的設計。下面我們來介紹 binlog 的變種文件 relay log。
relay log
relay log 中文名是中繼日志，在主從復制的時候會輔助 binlog 完成復制任務。relay log 有著和 binlog 類似的格式和結構，可以看作是 binlog 的親兄弟。唯一不同的地方是 relay log 多了 master.info 和 relay-log.info 兩個文件。
master.info 和 relay-log.info 的文件內容非常小，一般以 info 為后綴結尾的文件都不大，里面記錄了文件的指針。其中 master.info 記錄 I/O 線程讀取 binlog 的實時位置指針，relay-log.info 記錄了 SQL 線程讀取 relay log 的實時文件指針。 I/O 線程和 SQL 線程可以看作是在從庫上工作的兩個流水線工人，I/O 線程負責原材料的運輸，寫入本地的 relay log 中，SQL 線程負責從 relay log 獲取原材料并加工。
現在我們再來回顧一下主從復制的流程，實際上這個流程有很多隱藏細節，既然我們今天提到了 master.info 和 relay-log.info，我們就來深入講一下這個流程：
- 首先主庫收到客戶端請求語句，在語句結束之前向 binlog 寫入事件。
- 之后，從庫連接到主庫，主庫的 dump 線程從 binlog 讀取日志并發送到從庫的 IO 線程。
- I/O 線程從 master.info 讀取到上一次寫入的最后的位置。
- I/O 線程寫入日志到 relay log，更新 master.info 的最后位置。
- SQL 線程從 relay-log.info 讀取進上一次讀取的位置，然后在數據庫中執行 sql，執行完更新 relay-log.info 的最后位置。
總結
今天我們聊了 MySQL 的 binlog，binlog 是 MySQL 最重要的日志文件。同時，由于 binlog 本身是二進制的，所以它的結構很神秘。好在 MySQL 為我們提供了一些命令去查看它的內容。
通過今天的學習，我們也知道了 binlog 有 STATEMENT、ROW 和 MIXED 3 種模式，不同的格式下會有不同的事件，他們各有優缺點，MySQL 默認使用 STATEMENT 模式。
每個 binlog 文件都是由不同的事件組成的，幾乎所有變更操作都是事件。MySQL 有 30 多種事件，其中我們介紹了 QUERY、XID、FORMAT_DESCRIPTION 等幾個事件。binlog 有個孿生兄弟 relay log，這是從庫從主庫克隆過來的 binlog，它只是用在主從復制場景。relay log 有兩個 info 文件 master.info 和 relay-log.info，其中 master.info 服務于從庫的 I/O 線程，relay-log.info 服務于從庫的 SQL 線程。結合這兩個 info 文件，我們回顧了主從復制的完成過程，加深了印象。
