InnoDB學習（三）之BinLog

御狐神發表於2021-12-09

BinLog又稱為二進位制日誌，是MySQL服務層的資料日誌，MySQL所有的儲存引擎都支援BinLog。BinLog記錄了MySQL中的資料更新和可能導致資料更新的事件，可以用於主從複製或資料恢復。本文會對BinLog的原理進行詳細介紹。

BinLog

MySQL的BinLog用於記錄MySQL的所有資料變更和可能造成資料變更的事件，這些BinLog以二進位制日誌的形式順序儲存在磁碟中。使用者不能直接通過文字編輯器檢視BinLog的內容，需要藉助MySQL提供的mysqlbinlog工具才能檢視檔案。

需要注意的是，MySQL的BinLog位於Server層，所有的資料庫引擎都支援BinLog。MySQL的分層結構如下所示：

MySQL

BinLog的開啟

MySQL中可以通過以下命令檢視BinLog是否開啟，預設情況下MySQL5.7的BinLog處於關閉狀態：

show variables like '%log_bin%';

BinLog狀態

可以通過在MySQL配置檔案[mysqld]中新增如下配置，然後重啟MySQL服務，達到開啟BinLog的目的：

[mysqld]
log-bin=mysql-bin

新增配置並重啟容器後，可以看到BinLog的狀態已經變為ON：

BinLog狀態

BinLog的切換

如果在my.cnf裡面只設定log-bin=mysql-bin，但是不指定file_name，重啟資料庫後，MySQL的BinLog檔名稱為mysql-bin格式，我們可以通過以下命令檢視正在寫的日誌檔名：

show master status

如果你希望切換當前寫的日誌檔案為下一個檔案，可以通過執行以下命令進行切換：

flush logs;

BinLog切換

每次重啟MySQL服務也會生成一個新的二進位制日誌檔案，相當於二進位制日誌切換。切換二進位制日誌時，你會看到日誌檔案末尾的數字會不斷遞增。另外，除了這些BinLog檔案外，MySQL還會生成了一個DB-Server-bin.index的檔案，這個檔案中儲存所有二進位制日誌檔案的清單，又稱為二進位制檔案的索引。

BinLogs刪除

我們可以通過以下命令檢視所有二進位制檔案的檔名稱：

show binary logs;

BinLog列表

MySQL的BinLog可以手工刪除，也可以設定自動清理，手工刪除有以下刪除命令：

  • purge binary logs to mysql-bin.000001：刪除某個日誌之前的所有二進位制日誌檔案。這個命令會修改index中相關資料；
  • purge binary logs before '2017-03-10 10:10:00'：清除某個時間點以前的二進位制日誌檔案；
  • purge master logs before date_sub( now( ), interval 7 day)：清除7天前的二進位制日誌檔案；
  • reset master：清除所有的二進位制日誌檔案（當前不存在主從複製關係）；

自動清理可以通過設定expire_logs_days變數來啟用，預設值為0,表示不啟用過期自動刪除功能，如果啟用了自動清理功能，表示超出此天數的二進位制日誌檔案將被自動刪除，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時。

BinLog過期

BinLog的格式

MySQL有三種BinLog格式，各有優劣：

  1. Statement格式的BinLog：此模式下MySQL會記錄所有可能會變更資料的SQL語句；
  2. Row格式的BinLog：：此模式下會記錄資料庫每一行資料的變化情況；
  3. Mixed格式的BinLog：Statement和Row格式的混合；

MySQL中可以通過以下命令檢視BinLog的格式：

show variables like 'binlog_format'

BinLog格式

Statement格式的BinLog

Statement格式的BinLog會記錄每一條可能修改資料庫資料的sql語句，主從複製或資料恢復時可以在對應機器上執行同樣的SQL來達到資料的一致。然而Statement不支援一些特殊的SQL語句，如語句中包含UUID函式/LOAD DATA IN FILE語句等。

和啟用BinLog的方式類似，我們可以通過設定MySQL的配置檔案來修改BinLog的格式，通過如下配置我們可以設定MySQL的BinLog格式為Statement格式：

[mysqld]
log-bin=mysql-bin
binlog-format="STATEMENT"

修改配置檔案之後，重啟MySQL，新生成的BinLog就是Statement格式了：

BinLog Statement

也可以在MySQL啟動時新增引數--binlog-format=STATEMENT設定BinLog的格式為Statement.

BinLog格式為Statement格式下，我們切換到新的BinLog檔案，並向資料庫的表中插入資料：

flush logs;
insert into user_info (age, name) VALUES (1,'ssss')

上述語句執行完之後，MySQL會生成一個新的BinLog檔案，通過show binlog events in 'mysql-bin.000004'語句，我們可以看到BinLog中儲存了上述的Insert語句以及對應的資料庫等資訊：

Statement Demo

Row格式的BinLog

Row格式的BinLog會記錄每一行資料被修改的情況，但是Row格式的BinLog往往會比較大。比如對於SQL語句update user_info set name='test' where 1=1，Statement格式的BinLog只會儲存這條SQL語句，但是對於Row格式的BinLog，生成日誌的大小就取決於表的大小，如果表中有1億條資料，那麼就需要生成1億條BinLog記錄。

和Statement格式類似，我們可以通過如下配置設定MySQL的BinLog格式為Row格式：

[mysqld]
log-bin=mysql-bin
binlog-format="ROW"

也可以在MySQL啟動時新增引數--binlog-format=ROW設定BinLog的格式為Row.

修改配置檔案之後，重啟MySQL，新生成的BinLog就是ROW格式了。同樣的，我們向資料庫的表中插入資料，切換搭到新的BinLog檔案，並一次更新多條的資料：

flush logs;
insert into user_info (age, name) VALUES (2,'aaaa');
insert into user_info (age, name) VALUES (1,'aaaa');

flush logs;
update user_info set name='sss' where 1=1;

通過mysqlbinlog mysql-bin.000012 -vv語句，我們可以看檢視到上述的Insert語句的BinLog資訊。Row格式下，BinLog記錄了每一行資料值的變更情況：

Statement Demo

Row格式的BinLog也有不同的記錄方式，可以通過引數binlog_row_format設定。FULL: 記錄修改行的所有列資料；MINIMAL: 僅記錄修改行中有發生資料變化的列；NOBOLB: 和FULL方式相似，僅僅是當blog或text這些列沒有進行修改時，不會記錄這些屬性的列

Mixed格式的BinLog

通過上面的分析，我們知道BinLog的Statement和Row格式各有優缺點：

  • Statement格式：優點：日誌量小，節約磁碟和網路IO；缺點：需要記錄語句的上下文(如時間等)，不具有確定性的函式(如UUID)無法複製；
  • Row格式：優點：可以記錄資料庫的所有變更；缺點：如果單個SQL語句涉及的行均比較多，那麼會導致日誌量非常大；

Mixed格式的BinLog結合了Statement和Row格式的優點，對於普通的SQL語句使用Statement格式的BinLog記錄，對於一些特殊的SQL（如包含UUID的SQL），使用ROW格式的BinLog記錄。

對於資料庫隔離級別為讀已提交或讀未提交的場景，Mixed會使用會使用ROW格式的BinLog儲存記錄。

和Statement格式類似，我們可以通過如下配置設定MySQL的BinLog格式為MIXED格式：

[mysqld]
log-bin=mysql-bin
binlog-format="MIXED"

也可以在MySQL啟動時新增引數--binlog-format=MIXED設定BinLog的格式為MIXED.

接下來我們切換搭到新的BinLog檔案，並執行兩條SQL，一條可以用Statement格式的BinLog記錄，另外一條不可以：

flush logs;
insert into user_info (age, name) VALUES (1,'aaaa');
insert into user_info (age, name) VALUES (RAND(),'bbbb');

從下圖使用mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000014命令解析的日誌檔案可以看出，對於第一條SQL語句insert into user_info (age, name) VALUES (1,'aaaa');，BinLog使用Statement格式記錄，對於第二條SQL語句insert into user_info (age, name) VALUES (RAND(),'bbbb');，由於插入語句中包含隨機數，無法通過Statement複製，MySQL使用了Row格式的BinLog記錄了行資料的變更。

Mixed Demo

BinLog的作用

MySQL的BinLog主要有以下兩個作用：

  1. 資料恢復：資料庫資料丟失後，我們可以從某個時間節點的資料備份和該時間點之後的BinLog來恢復資料庫的資料；
  2. 主從複製：主從複製過程中，主資料庫將自身的BinLog傳送給從資料庫，從資料庫通過解析BinLog同步主資料庫的資料變更，從而達到主從資料一致；

資料恢復

MySQL資料庫可以恢復某個時間點的狀態，這個恢復過程就是通過BinLog實現的。BinLog會記錄資料庫所有的邏輯操作，並且是採用“追加寫”的形式。如果你的DBA承諾說半個月內可以恢復，那麼備份系統中一定會儲存最近半個月的所有BinLog，同時系統會定期做整庫備份。這裡的“定期”取決於系統的重要性，可以是一天一備，也可以是一週一備。

當需要恢復到指定的某一秒時，比如某天下午兩點發現中午十二點有一次誤刪表，需要找回資料，那你可以這麼做：

  1. 首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上的一個備份，從這個備份恢復到臨時庫；
  2. 然後，從備份的時間點開始，將備份的BinLog依次取出來，重放到中午誤刪表之前的那個時刻。

這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢復到線上庫去。

主從複製

在高併發的場景下，單節點的MySQL無法滿足併發量需求，這時就可以通過新增MySQL例項來提升效能。新增MySQL例項有多種方式，本節只介紹主從機制。

MySQL的主從複製是一個非同步的複製過程，資料將從一個MySQL資料庫（Master）複製到另一個MySQL資料庫（Slave），在Master和Slave之間實現整個主從複製的過程是由三個執行緒參與完成的。其中兩個執行緒（SQL執行緒和IO執行緒）在Slave端，另一個執行緒（I/O執行緒）在Master端。

要實現MySQL的主從複製，首先必須開啟Master端的binlog記錄功能，否則就無法實現。MySQL主從複製的步驟如下所示：

Mast slave Demo

根據上圖分析主從複製的流程，可以看出MYSQL主從複製包含以下步驟：

  1. 在Slave伺服器上執行start slave命令開啟主從複製開關，開始進行主從複製。
  2. Slave伺服器的IO執行緒會通過在master上已經授權的複製使用者許可權請求連線Master伺服器，並請求從執行binlog日誌檔案中的指定位置（日誌檔名和位置就是在配置主從複製服務時執行change master命令指定的）之後開始傳送binlog日誌內容。
  3. Master伺服器接收來自Slave伺服器的IO執行緒的請求後，其上負責複製的IO執行緒會根據Slave伺服器的IO執行緒請求的資訊分批讀取指定binlog日誌檔案指定位置之後的binlog日誌資訊，然後返回給Slave端的IO執行緒。返回的資訊中除了binlog日誌內容外，還有在Master伺服器端記錄的IO執行緒。返回的資訊中除了binlog中的下一個指定更新位置。
  4. 當Slave伺服器的IO執行緒獲取到Master伺服器上IO執行緒傳送的日誌內容、日誌檔案及位置點後，會將binlog日誌內容依次寫到Slave端自身的RelayLog（即中繼日誌）檔案（Mysql-relay-bin.xxx）的最末端，並將新的binlog檔名和位置記錄到master-info檔案中，以便下一次讀取master端新binlog日誌時能告訴Master伺服器從新binlog日誌的指定檔案及位置開始讀取新的binlog日誌內容
  5. Slave伺服器端的SQL執行緒會實時檢測本地Relay Log 中IO執行緒新增的日誌內容，然後及時把Relay LOG 檔案中的內容解析成sql語句，並在自身Slave伺服器上按解析SQL語句的位置順序執行應用這樣sql語句，並在relay-log.info中記錄當前應用中繼日誌的檔名和位置點

BinLog相關引數

  1. log_bin_basename：Since-MySQL 5.6.2，用於指定二進位制檔名，預設值為datadir + '/' + hostname + '-bin'。 該引數不需要設定，也不能在my.cnf中設定，否則會報錯；
  2. log_bin_index：Since-MySQL 5.6.4，二進位制日誌的索引檔名，可以在my.cnf中設定；
  3. log_bin_trust_function_creators：預設為OFF，這個引數開啟會限制儲存過程、Function、觸發器的建立；
  4. sql_log_bin：控制會話級別二進位制日誌功能的開啟或關閉，預設為ON，表示啟用二進位制日誌功能；
  5. expire_logs_days：BinLog保留的時長；
  6. binlog_cache_size：為每個客戶端分配binlog_cache_size大小的快取，預設值32768。BinLog快取使用的前提條件是伺服器端使用了支援事務的引擎以及開啟了BinLog功能，它是MySQL用來提高BinLog的效率而設計的一個用於短時間內臨時快取BinLog資料的記憶體區域。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多或多事務語句，寫入量比較大，可適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來快取了；
  7. max_binlog_cache_size： BinLog能夠使用的最大記憶體快取的大小。當執行多語句事務時，max_binlog_cache_size如果不夠大，系統可能會報出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的錯誤；
  8. max_binlog_stmt_cache_size：max_binlog_cache_size針對事務語句，max_binlog_stmt_cache_size針對非事務語句，當我們發現Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比較大時就需要考慮增大cache的大小；
  9. max_binlog_size：表示二進位制日誌的最大值，一般設定為512M或1GB，但不能超過1GB。該設定並不能嚴格控制二進位制日誌的大小，尤其是二進位制日誌比較靠近為不而又遇到一根比較大事務時， 為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進當前日誌，直到事務結束；
  10. binlog_checksum：主從校檢覆制時的資料校驗，NONE表示不生成checksum，CRC-32表示使用這個演算法做校檢
  11. binlog_format：指定二進位制日誌的型別，分別有STATEMENT、ROW、MIXED三種值，MySQL 5.7.6之前預設為STATEMENT模式，MySQL 5.7.7之後預設為ROW模式，這個引數主要影響主從複製。
  12. sync_binlog：這個引數對於Mysql系統來說是至關重要的，它不僅影響到二進位制日誌檔案對MySQL所帶來的效能損耗，而且還影響到MySQL中資料的完整性：

    sync_binlog=0，當事務提交後，Mysql僅僅是將binlog_cache中的資料寫入binlog檔案，但不執行fsync之類的磁碟同步指令通知檔案系統將快取重新整理到磁碟，而是讓Filesystem自行決定什麼時候來做同步。MySQL中預設的設定是sync_binlog=0，即不作任何強制性的磁碟重新整理指令，這個設定效能是最好的，但風險也是最大的。一旦系統崩潰（Crash），在檔案系統快取中的所有二進位制日誌資訊都會丟失。從而帶來資料不完整問題。sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，同時檔案系統將Binlog檔案快取重新整理到磁碟。可以適當的調整sync_binlog， 在犧牲一定的一致性下，獲取更高的併發和效能。

我是御狐神，歡迎大家關注我的微信公眾號:wzm2zsd

qrcode_for_gh_83670e17bbd7_344-2021-09-04-10-55-16

參考文件

MySQL官方文件
Binlog詳解
binlog淺析
mysql二進位制日誌格式化_Mysql 二進位制日誌及格式選擇
徹底解析Mixed日誌格式的binlog
(七) MySQL主從複製及讀寫分離實戰

本文最先發布至微信公眾號，版權所有，禁止轉載！

相關文章