MySQL 的 binlog 的三種格式這么好玩！
MySQL 中的日志比較重要的有 binlog(歸檔日志)、redo log(重做日志)以及 undo log，那么跟我們本文相關的主要是 binlog，另外兩個日志松哥將來有空了再和大家詳細介紹。
1. binlog
binlog 我們中文一般稱作歸檔日志，如果大家看過松哥之前發的 MySQL 主從搭建，應該對這個日志有印象，當我們搭建 MySQL 主從的時候就離不開 binlog(傳送門：MySQL8 主從復制踩坑指南)。
binlog 是 MySQL Server 層的日志，而不是存儲引擎自帶的日志，它記錄了所有的 DDL 和 DML(不包含數據查詢語句)語句，而且是以事件形式記錄，還包含語句所執行的消耗的時間等，需要注意的是：
- binlog 是一種邏輯日志，他里邊所記錄的是一條 SQL 語句的原始邏輯，例如給某一個字段 +1，注意這個區別于 redo log 的物理日志(在某個數據頁上做了什么修改)。
- binlog 文件寫滿后，會自動切換到下一個日志文件繼續寫，而不會覆蓋以前的日志，這個也區別于 redo log，redo log 是循環寫入的，即后面寫入的可能會覆蓋前面寫入的。
- 一般來說，我們在配置 binlog 的時候，可以指定 binlog 文件的有效期，這樣在到期后，日志文件會自動刪除，這樣避免占用較多存儲空間。
根據 MySQL 官方文檔的介紹，開啟 binlog 之后，大概會有 1% 的性能損耗，不過這還是可以接受的，一般來說，binlog 有兩個重要的使用場景：
- MySQL 主從復制時：在主機上開啟 binlog，主機將 binlog 同步給從機，從機通過 binlog 來同步數據，進而實現主機和從機的數據同步。
- MySQL 數據恢復，通過使用 mysqlbinlog 工具再結合 binlog 文件，可以將數據恢復到過去的某一時刻。
2. 配置 binlog
為了演示方便，松哥這里在 Docker 中安裝了 MySQL，我們以此為例來開始今天的演示。如果小伙伴們還不懂 docker 的使用，可以在公眾號后臺回復 docker，有松哥寫的教程。
首先我們在 docker 中安裝好 MySQL，然后進入到容器中，通過如下命令可以查看 binlog 是否開啟：
這個 OFF 就表示 binlog 是一個關閉狀態，沒有開啟。
通過以下命令可以查看 binlog 日志的格式，如下：
可以看到，這個 binlog 的格式為 ROW。
這里就涉及到一個問題，binlog 的格式。
2.1 binlog 的格式
binlog 有三種格式：
- Statement(Statement-Based Replication,SBR)：每一條會修改數據的 SQL 都會記錄在 binlog 中。
- Row(Row-Based Replication,RBR)：不記錄 SQL 語句上下文信息，僅保存哪條記錄被修改。
- Mixed(Mixed-Based Replication,MBR)：Statement 和 Row 的混合體。
2.1.1 Statement
Statement 模式只記錄執行的 SQL，不需要記錄每一行數據的變化，因此極大的減少了 binlog 的日志量，避免了大量的 IO 操作，提升了系統的性能。
但是，正是由于 Statement 模式只記錄 SQL，而如果一些 SQL 中包含了函數，那么可能會出現執行結果不一致的情況。比如說 uuid() 函數，每次執行的時候都會生成一個隨機字符串，在 master 中記錄了 uuid，當同步到 slave 之后，再次執行，就獲取到另外一個結果了。
所以使用 Statement 格式會出現一些數據一致性問題。
2.2.2 Row
從 MySQL5.1.5 版本開始，binlog 引入了 Row 格式，Row 格式不記錄 SQL 語句上下文相關信息，僅僅只需要記錄某一條記錄被修改成什么樣子了。
Row 格式的日志內容會非常清楚的記錄下每一行數據修改的細節，這樣就不會出現 Statement 中存在的那種數據無法被正常復制的情況。
不過 Row 格式也有一個很大的問題，那就是日志量太大了，特別是批量 update、整表 delete、alter 表等操作，由于要記錄每一行數據的變化，此時會產生大量的日志，大量的日志也會帶來 IO 性能問題。
2.2.3 Mixed
從 MySQL5.1.8 版開始，MySQL 又推出了 Mixed 格式，這種格式實際上就是 Statement 與 Row 的結合。
在 Mixed 模式下，系統會自動判斷該用 Statement 還是 Row：一般的語句修改使用 Statement 格式保存 binlog;對于一些 Statement 無法準確完成主從復制的操作，則采用 Row 格式保存 binlog。
Mixed 模式中，MySQL 會根據執行的每一條具體的 SQL 語句來區別對待記錄的日志格式，也就是在 Statement 和 Row 之間選擇一種。
2.2 配置
接下來我們來看看 binlog 的配置。
2.2.1 開啟 binlog
開啟 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf，該文件在容器的 /etc/mysql/mysql.conf.d 目錄下。
針對該配置文件，我們做如下修改：
# 這個參數表示啟用 binlog 功能，并指定 binlog 的存儲目錄
log-bin=javaboy_logbin
# 設置一個 binlog 文件的最大字節
# 設置最大 100MB
max_binlog_size=104857600
# 設置了 binlog 文件的有效期（單位：天）
expire_logs_days = 7
# binlog 日志只記錄指定庫的更新（配置主從復制的時候會用到）
#binlog-do-db=javaboy_db
# binlog 日志不記錄指定庫的更新（配置主從復制的時候會用到）
#binlog-ignore-db=javaboy_no_db
# 寫緩存多少次，刷一次磁盤，默認 0 表示這個操作由操作系統根據自身負載自行決定多久寫一次磁盤
# 1 表示每一條事務提交都會立即寫磁盤，n 則表示 n 個事務提交才會寫磁盤
sync_binlog=0
# 為當前服務取一個唯一的 id（MySQL5.7 之后需要配置）
server-id=1
各項配置的含義松哥已經在注視中說明了。截圖如下：
配置完成后，執行如下命令重啟 mysql 容器(mysql1 是我這里容器的名字)：
docker restart mysql1
重啟之后，再次執行 show variables like 'log_bin%'; 即可看到 binlog 已經開啟了。
這里除了 log_bin 變量外，還有兩個變量名也值得我們關注：
- log_bin_basename：這個是將來產生的 binlog 日志文件的名稱前綴，換句話說，根據大家目前所看到的配置，將來產生的 binlog 日志文件名為 javaboy_logbin.xxx，這個文件中將會用來記錄所有的 DDL 和 DML 語句事件。
- log_bin_index：這個是 binlog 的索引文件，保存了所有 binlog 的目錄，因為 binlog 可能會有多個。我們可以來查看一下現在的javaboy_logbin.index 文件：
可以看到，目前只有一個 logbin 文件。
2.2.2 修改 binlog_format
binlog_format 有幾種不同的改法：
修改當前會話的 binlog_format，這個修改只針對當前會話有效：
也可以修改全局的 binlog_format，這個修改，當 MySQL 重啟之后，會失效：
如果想一勞永逸搞定這事，可以修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，在配置文件中，添加 binlog_format 選項，如下：
這是一個永久性的修改。
3. 常見 binlog 操作
接下來我們再來介紹幾個常見的 binlog 操作命令。
- 查看所有 binlog 日志
通過如下方式我們可以查看 binlog 日志列表：
show master logs;
可以看到，我這里目前只有一個日志文件，文件名為 javaboy_logbin.000001，File_size 表示這個文件占用的字節大小是 154。
- 查看 master 狀態
這個命令我們在搭建 MySQL 主從的時候經常會用到，如下：
這個時候可以看到最新的 binlog 日志文件名稱以及最后一個操作事件的 Position 值(這個值有啥用，我們后面會給大家詳細介紹)。
- 刷新 binlog
正常來說，一個 binlog 寫滿之后，會自動切換到下一個 binlog 開始寫，不過我們也可以執行一個 flush logs 命令來手動刷新 binlog，手動刷新 binlog 之后，就會產生一個新的 binlog 日志文件，接下來所有的 binlog 日志都將記錄到新的文件中。如下：
由上圖可以看到，我們刷新日志之后，再通過 show master logs 去查看日志，發現日志文件已經多了一個新產生的了，然后再通過 show master status 去查看最新的日志文件信息，發現也已經變為 javaboy_logbin.000002。
- 重置 binlog
reset master 可以重置 binlog 日志文件，讓日志重新從 000001 開始記錄，不過如果當前主機有一個或者多個從機在運行，那么該命令就運行不了(因為從機是通過 binlog 來實現數據庫同步的，主機把 binlog 清空了，從機會報找不到 binlog 的錯誤)。
- 查看 binlog
由于 binlog 是二進制日志文件，所以要是直接打開，那肯定是看不了的：
沒有看到任何有用的信息。
為了查看 binlog，MySQL 為我們提供了兩個官方工具，我們一個一個來看，首先是 mysqlbinlog 命令，如下：
雖然看起來亂糟糟的，不過仔細看著其實都有跡可循。因為我這里是一個新安裝的數據庫，里邊只是創建了一個名為 javaboy 的庫，然后創建了一個名為 user 的表加了兩條數據，其他什么事情都沒做，所以創建庫的腳本我們其實能夠從紛雜的文件中找到。
產生的日志文件中有一個 end_log_pos 是日志文件的 pos 點，這個將來在數據恢復的時候有用。
不過這種查看方式不夠人性化，我們說 binlog 是按照事件來記錄日志的，所以如果我們能夠按照事件的方式查看日志，就會好很多，我們再來看看如下一個命令：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
這個表示以事件的方式來查看 binlog，這里涉及到幾個參數：
- log_name：可以指定要查看的 binlog 日志文件名，如果不指定的話，表示查看最早的 binlog 文件。
- pos：從哪個 pos 點開始查看，凡是 binlog 記錄下來的操作都有一個 pos 點，這個其實就是相當于我們可以指定從哪個操作開始查看日志，如果不指定的話，就是從該 binlog 的開頭開始查看。
- offset：這是是偏移量，不指定默認就是 0。
- row_count：查看多少行記錄，不指定就是查看所有。
我們來看一個簡單的例子：
show binlog events in 'javaboy_logbin.000001';
這下就清晰多了，我們可以看到之前的所有操作，例如：
- 在 Pos 219-322 之間創建了一個庫。
- 在 Pos 387-537 之間創建了一張表。
- 在 Pos 677-780 之間添加了一條記錄。
- ...
這其實就是 Row 格式的 binlog。
4. 小結
好啦，今天這篇文章主要是和小伙伴們分享了 MySQL 的 binlog 日志，主要是一些理論知識，下篇文章松哥將通過兩個具體的案例，來和大家演示不同的 binlog_format 存在的問題。
本文轉載自微信公眾號「江南一點雨 」，可以通過以下二維碼關注。轉載本文請聯系江南一點雨公眾號。
