InnoDB學習（三）之BinLog

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主從複製及讀寫分離實戰

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

Tags:

VirMach 便宜 VPS

QNews

QNews