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