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

BinLog

MySQL的BinLog用于記錄MySQL的所有資料變更和可能造成資料變更的事件，這些BinLog以二進制日志的形式順序存盤在磁盤中，用戶不能直接通過文本編輯器查看BinLog的內容，需要借助MySQL提供的mysqlbinlog工具才能查看檔案，

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

BinLog的開啟

MySQL中可以通過以下命令查看BinLog是否開啟，默認情況下MySQL5.7的BinLog處于關閉狀態：

show variables like '%log_bin%';

可以通過在MySQL組態檔[mysqld]中添加如下配置，然后重啟MySQL服務，達到開啟BinLog的目的：

[mysqld]
log-bin=mysql-bin

添加配置并重啟容器后，可以看到BinLog的狀態已經變為ON：

BinLog的切換

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

show master status 

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

flush logs;

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

BinLogs洗掉

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

show binary logs;

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的格式

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

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

MySQL中可以通過以下命令查看BinLog的格式：

show variables like 'binlog_format'

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格式了：

也可以在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陳述句以及對應的資料庫等資訊：

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記錄了每一行資料值的變更情況：

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記錄了行資料的變更，

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主從復制的步驟如下所示：

根據上圖分析主從復制的流程，可以看出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

參考檔案

MySQL官方檔案
Binlog詳解
binlog淺析
mysql二進制日志格式化_Mysql 二進制日志及格式選擇
徹底決議Mixed日志格式的binlog
(七) MySQL主從復制及讀寫分離實戰

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

標籤：Java

上一篇：【備考05組03號】第一屆藍橋杯本科組國賽題解

下一篇：微服務下前后端分離的統一認證授權服務，基于Spring Security OAuth2 + Spring Cloud Gateway實作單點登錄