MySQL 中的日志比較重要的有 binlog(歸檔日志)、redo log(重做日志)以及 undo log，那么跟我們本文相關(guān)的主要是 binlog，另外兩個(gè)日志松哥將來(lái)有空了再和大家詳細(xì)介紹。

1. binlog

binlog 我們中文一般稱作歸檔日志，如果大家看過(guò)松哥之前發(fā)的 MySQL 主從搭建，應(yīng)該對(duì)這個(gè)日志有印象，當(dāng)我們搭建 MySQL 主從的時(shí)候就離不開(kāi) binlog(傳送門(mén)：MySQL8 主從復(fù)制踩坑指南)。

binlog 是 MySQL Server 層的日志，而不是存儲(chǔ)引擎自帶的日志，它記錄了所有的 DDL 和 DML(不包含數(shù)據(jù)查詢語(yǔ)句)語(yǔ)句，而且是以事件形式記錄，還包含語(yǔ)句所執(zhí)行的消耗的時(shí)間等，需要注意的是：

• binlog 是一種邏輯日志，他里邊所記錄的是一條 SQL 語(yǔ)句的原始邏輯，例如給某一個(gè)字段 +1，注意這個(gè)區(qū)別于 redo log 的物理日志(在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改)。
• binlog 文件寫(xiě)滿后，會(huì)自動(dòng)切換到下一個(gè)日志文件繼續(xù)寫(xiě)，而不會(huì)覆蓋以前的日志，這個(gè)也區(qū)別于 redo log，redo log 是循環(huán)寫(xiě)入的，即后面寫(xiě)入的可能會(huì)覆蓋前面寫(xiě)入的。
• 一般來(lái)說(shuō)，我們?cè)谂渲?binlog 的時(shí)候，可以指定 binlog 文件的有效期，這樣在到期后，日志文件會(huì)自動(dòng)刪除，這樣避免占用較多存儲(chǔ)空間。

根據(jù) MySQL 官方文檔的介紹，開(kāi)啟 binlog 之后，大概會(huì)有 1% 的性能損耗，不過(guò)這還是可以接受的，一般來(lái)說(shuō)，binlog 有兩個(gè)重要的使用場(chǎng)景：

• MySQL 主從復(fù)制時(shí)：在主機(jī)上開(kāi)啟 binlog，主機(jī)將 binlog 同步給從機(jī)，從機(jī)通過(guò) binlog 來(lái)同步數(shù)據(jù)，進(jìn)而實(shí)現(xiàn)主機(jī)和從機(jī)的數(shù)據(jù)同步。
• MySQL 數(shù)據(jù)恢復(fù)，通過(guò)使用 mysqlbinlog 工具再結(jié)合 binlog 文件，可以將數(shù)據(jù)恢復(fù)到過(guò)去的某一時(shí)刻。

2. 配置 binlog

為了演示方便，松哥這里在 Docker 中安裝了 MySQL，我們以此為例來(lái)開(kāi)始今天的演示。如果小伙伴們還不懂 docker 的使用，可以在公眾號(hào)后臺(tái)回復(fù) docker，有松哥寫(xiě)的教程。

首先我們?cè)?docker 中安裝好 MySQL，然后進(jìn)入到容器中，通過(guò)如下命令可以查看 binlog 是否開(kāi)啟：

這個(gè) OFF 就表示 binlog 是一個(gè)關(guān)閉狀態(tài)，沒(méi)有開(kāi)啟。

通過(guò)以下命令可以查看 binlog 日志的格式，如下：

可以看到，這個(gè) binlog 的格式為 ROW。

這里就涉及到一個(gè)問(wèn)題，binlog 的格式。

2.1 binlog 的格式

binlog 有三種格式：

• Statement(Statement-Based Replication,SBR)：每一條會(huì)修改數(shù)據(jù)的 SQL 都會(huì)記錄在 binlog 中。
• Row(Row-Based Replication,RBR)：不記錄 SQL 語(yǔ)句上下文信息，僅保存哪條記錄被修改。
• Mixed(Mixed-Based Replication,MBR)：Statement 和 Row 的混合體。

2.1.1 Statement

Statement 模式只記錄執(zhí)行的 SQL，不需要記錄每一行數(shù)據(jù)的變化，因此極大的減少了 binlog 的日志量，避免了大量的 IO 操作，提升了系統(tǒng)的性能。

但是，正是由于 Statement 模式只記錄 SQL，而如果一些 SQL 中包含了函數(shù)，那么可能會(huì)出現(xiàn)執(zhí)行結(jié)果不一致的情況。比如說(shuō) uuid() 函數(shù)，每次執(zhí)行的時(shí)候都會(huì)生成一個(gè)隨機(jī)字符串，在 master 中記錄了 uuid，當(dāng)同步到 slave 之后，再次執(zhí)行，就獲取到另外一個(gè)結(jié)果了。

所以使用 Statement 格式會(huì)出現(xiàn)一些數(shù)據(jù)一致性問(wèn)題。

2.2.2 Row

從 MySQL5.1.5 版本開(kāi)始，binlog 引入了 Row 格式，Row 格式不記錄 SQL 語(yǔ)句上下文相關(guān)信息，僅僅只需要記錄某一條記錄被修改成什么樣子了。

Row 格式的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，這樣就不會(huì)出現(xiàn) Statement 中存在的那種數(shù)據(jù)無(wú)法被正常復(fù)制的情況。

不過(guò) Row 格式也有一個(gè)很大的問(wèn)題，那就是日志量太大了，特別是批量 update、整表 delete、alter 表等操作，由于要記錄每一行數(shù)據(jù)的變化，此時(shí)會(huì)產(chǎn)生大量的日志，大量的日志也會(huì)帶來(lái) IO 性能問(wèn)題。

2.2.3 Mixed

從 MySQL5.1.8 版開(kāi)始，MySQL 又推出了 Mixed 格式，這種格式實(shí)際上就是 Statement 與 Row 的結(jié)合。

在 Mixed 模式下，系統(tǒng)會(huì)自動(dòng)判斷該用 Statement 還是 Row：一般的語(yǔ)句修改使用 Statement 格式保存 binlog;對(duì)于一些 Statement 無(wú)法準(zhǔn)確完成主從復(fù)制的操作，則采用 Row 格式保存 binlog。

Mixed 模式中，MySQL 會(huì)根據(jù)執(zhí)行的每一條具體的 SQL 語(yǔ)句來(lái)區(qū)別對(duì)待記錄的日志格式，也就是在 Statement 和 Row 之間選擇一種。

2.2 配置

接下來(lái)我們來(lái)看看 binlog 的配置。

2.2.1 開(kāi)啟 binlog

開(kāi)啟 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf，該文件在容器的 /etc/mysql/mysql.conf.d 目錄下。

針對(duì)該配置文件，我們做如下修改：

# 這個(gè)參數(shù)表示啟用 binlog 功能，并指定 binlog 的存儲(chǔ)目錄
log-bin=javaboy_logbin

# 設(shè)置一個(gè) binlog 文件的最大字節(jié)
# 設(shè)置最大 100MB
max_binlog_size=104857600

# 設(shè)置了 binlog 文件的有效期（單位：天）
expire_logs_days = 7

# binlog 日志只記錄指定庫(kù)的更新（配置主從復(fù)制的時(shí)候會(huì)用到）
#binlog-do-db=javaboy_db

# binlog 日志不記錄指定庫(kù)的更新（配置主從復(fù)制的時(shí)候會(huì)用到）
#binlog-ignore-db=javaboy_no_db

# 寫(xiě)緩存多少次，刷一次磁盤(pán)，默認(rèn) 0 表示這個(gè)操作由操作系統(tǒng)根據(jù)自身負(fù)載自行決定多久寫(xiě)一次磁盤(pán)
# 1 表示每一條事務(wù)提交都會(huì)立即寫(xiě)磁盤(pán)，n 則表示 n 個(gè)事務(wù)提交才會(huì)寫(xiě)磁盤(pán)
sync_binlog=0

# 為當(dāng)前服務(wù)取一個(gè)唯一的 id（MySQL5.7 之后需要配置）
server-id=1

各項(xiàng)配置的含義松哥已經(jīng)在注視中說(shuō)明了。截圖如下：

配置完成后，執(zhí)行如下命令重啟 mysql 容器(mysql1 是我這里容器的名字)：

docker restart mysql1

重啟之后，再次執(zhí)行 show variables like 'log_bin%'; 即可看到 binlog 已經(jīng)開(kāi)啟了。

這里除了 log_bin 變量外，還有兩個(gè)變量名也值得我們關(guān)注：

• log_bin_basename：這個(gè)是將來(lái)產(chǎn)生的 binlog 日志文件的名稱前綴，換句話說(shuō)，根據(jù)大家目前所看到的配置，將來(lái)產(chǎn)生的 binlog 日志文件名為 javaboy_logbin.xxx，這個(gè)文件中將會(huì)用來(lái)記錄所有的 DDL 和 DML 語(yǔ)句事件。
• log_bin_index：這個(gè)是 binlog 的索引文件，保存了所有 binlog 的目錄，因?yàn)?binlog 可能會(huì)有多個(gè)。我們可以來(lái)查看一下現(xiàn)在的javaboy_logbin.index 文件：

可以看到，目前只有一個(gè) logbin 文件。

2.2.2 修改 binlog_format

binlog_format 有幾種不同的改法：

修改當(dāng)前會(huì)話的 binlog_format，這個(gè)修改只針對(duì)當(dāng)前會(huì)話有效：

也可以修改全局的 binlog_format，這個(gè)修改，當(dāng) MySQL 重啟之后，會(huì)失效：

如果想一勞永逸搞定這事，可以修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，在配置文件中，添加 binlog_format 選項(xiàng)，如下：

這是一個(gè)永久性的修改。

3. 常見(jiàn) binlog 操作

接下來(lái)我們?cè)賮?lái)介紹幾個(gè)常見(jiàn)的 binlog 操作命令。

• 查看所有 binlog 日志

通過(guò)如下方式我們可以查看 binlog 日志列表：

show master logs;

可以看到，我這里目前只有一個(gè)日志文件，文件名為 javaboy_logbin.000001，F(xiàn)ile_size 表示這個(gè)文件占用的字節(jié)大小是 154。

• 查看 master 狀態(tài)

這個(gè)命令我們?cè)诖罱?MySQL 主從的時(shí)候經(jīng)常會(huì)用到，如下：

這個(gè)時(shí)候可以看到最新的 binlog 日志文件名稱以及最后一個(gè)操作事件的 Position 值(這個(gè)值有啥用，我們后面會(huì)給大家詳細(xì)介紹)。

• 刷新 binlog

正常來(lái)說(shuō)，一個(gè) binlog 寫(xiě)滿之后，會(huì)自動(dòng)切換到下一個(gè) binlog 開(kāi)始寫(xiě)，不過(guò)我們也可以執(zhí)行一個(gè) flush logs 命令來(lái)手動(dòng)刷新 binlog，手動(dòng)刷新 binlog 之后，就會(huì)產(chǎn)生一個(gè)新的 binlog 日志文件，接下來(lái)所有的 binlog 日志都將記錄到新的文件中。如下：

由上圖可以看到，我們刷新日志之后，再通過(guò) show master logs 去查看日志，發(fā)現(xiàn)日志文件已經(jīng)多了一個(gè)新產(chǎn)生的了，然后再通過(guò) show master status 去查看最新的日志文件信息，發(fā)現(xiàn)也已經(jīng)變?yōu)?javaboy_logbin.000002。

• 重置 binlog

reset master 可以重置 binlog 日志文件，讓日志重新從 000001 開(kāi)始記錄，不過(guò)如果當(dāng)前主機(jī)有一個(gè)或者多個(gè)從機(jī)在運(yùn)行，那么該命令就運(yùn)行不了(因?yàn)閺臋C(jī)是通過(guò) binlog 來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)同步的，主機(jī)把 binlog 清空了，從機(jī)會(huì)報(bào)找不到 binlog 的錯(cuò)誤)。

• 查看 binlog

由于 binlog 是二進(jìn)制日志文件，所以要是直接打開(kāi)，那肯定是看不了的：

沒(méi)有看到任何有用的信息。

為了查看 binlog，MySQL 為我們提供了兩個(gè)官方工具，我們一個(gè)一個(gè)來(lái)看，首先是 mysqlbinlog 命令，如下：

雖然看起來(lái)亂糟糟的，不過(guò)仔細(xì)看著其實(shí)都有跡可循。因?yàn)槲疫@里是一個(gè)新安裝的數(shù)據(jù)庫(kù)，里邊只是創(chuàng)建了一個(gè)名為 javaboy 的庫(kù)，然后創(chuàng)建了一個(gè)名為 user 的表加了兩條數(shù)據(jù)，其他什么事情都沒(méi)做，所以創(chuàng)建庫(kù)的腳本我們其實(shí)能夠從紛雜的文件中找到。

產(chǎn)生的日志文件中有一個(gè) end_log_pos 是日志文件的 pos 點(diǎn)，這個(gè)將來(lái)在數(shù)據(jù)恢復(fù)的時(shí)候有用。

不過(guò)這種查看方式不夠人性化，我們說(shuō) binlog 是按照事件來(lái)記錄日志的，所以如果我們能夠按照事件的方式查看日志，就會(huì)好很多，我們?cè)賮?lái)看看如下一個(gè)命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這個(gè)表示以事件的方式來(lái)查看 binlog，這里涉及到幾個(gè)參數(shù)：

• log_name：可以指定要查看的 binlog 日志文件名，如果不指定的話，表示查看最早的 binlog 文件。
• pos：從哪個(gè) pos 點(diǎn)開(kāi)始查看，凡是 binlog 記錄下來(lái)的操作都有一個(gè) pos 點(diǎn)，這個(gè)其實(shí)就是相當(dāng)于我們可以指定從哪個(gè)操作開(kāi)始查看日志，如果不指定的話，就是從該 binlog 的開(kāi)頭開(kāi)始查看。
• offset：這是是偏移量，不指定默認(rèn)就是 0。
• row_count：查看多少行記錄，不指定就是查看所有。

我們來(lái)看一個(gè)簡(jiǎn)單的例子：

show binlog events in 'javaboy_logbin.000001';

這下就清晰多了，我們可以看到之前的所有操作，例如：

• 在 Pos 219-322 之間創(chuàng)建了一個(gè)庫(kù)。
• 在 Pos 387-537 之間創(chuàng)建了一張表。
• 在 Pos 677-780 之間添加了一條記錄。
• ...

這其實(shí)就是 Row 格式的 binlog。

4. 小結(jié)

好啦，今天這篇文章主要是和小伙伴們分享了 MySQL 的 binlog 日志，主要是一些理論知識(shí)，下篇文章松哥將通過(guò)兩個(gè)具體的案例，來(lái)和大家演示不同的 binlog_format 存在的問(wèn)題。

本文轉(zhuǎn)載自微信公眾號(hào)「江南一點(diǎn)雨 」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系江南一點(diǎn)雨公眾號(hào)。