今天的文章來晚了，主要是我一覺起來變黃碼了，關鍵是我還不知道，早上 8.20 到了公司樓下(最近不在深圳)，保安要看健康碼，當我自信滿滿的打開粵省事卻傻眼了，折騰一早上，鬧了個烏龍，綠碼總算回來了，真是生活處處有驚喜......

書接上回，閑話不表。

今天來說說 MySQL 主從復制數據不一致的問題，通過幾個具體的案例，來向小伙伴們展示 binlog 不同 format 之間的區別。

1. 準備工作

以下配置基于 Docker。

我這里有一張簡單的圖向大伙展示 MySQL 主從的工作方式：

這里，我們準備兩臺機器：

• 主機：10.3.50.27:33061
• 從機：10.3.50.27:33062

1.1 主機配置

主機的配置就三個步驟，比較容易：

（1）授權給從機服務器

GRANT REPLICATION SLAVE ON *.* to 'rep1'@'10.3.50.27' identified by '123';
FLUSH PRIVILEGES;

這里表示配置從機登錄用戶名為 rep1，密碼為 123，并且必須從10.3.50.27 這個地址登錄，登錄成功之后可以操作任意庫中的任意表。其中，如果不需要限制登錄地址，可以將 IP 地址更換為一個 %。

注意，在 MySQL8 里邊，這塊有一些變化。MySQL8 中用戶創建和授權需要分開，不能像上面那樣一步到位，具體方式如下：

CREATE USER `rep1`@`10.3.50.27` IDENTIFIED WITH caching_sha2_password BY 'javaboy.COM';

GRANT Replication Slave ON *.* TO `rep1`@`10.3.50.27`;

（2）修改主庫配置文件

開啟 binlog ，并設置 server-id ，每次修改配置文件后都要重啟 MySQL 服務才會生效。

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

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

[mysqld]
# 這個參數表示啟用 binlog 功能，并指定 binlog 的存儲目錄
log-bin=javaboy_logbin
# 設置 binlog_format 格式
binlog_format=STATEMENT
# 設置一個 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

各項配置的含義松哥已經在注視中說明了。截圖如下：

如下圖：

• log-bin：同步的日志路徑及文件名，一定注意這個目錄要是 MySQL 有權限寫入的(我這里是偷懶了，直接放在了下面那個datadir下面)。
• binlog-do-db：要同步的數據庫名，當從機連上主機后，只有這里配置的數據庫才會被同步，其他的不會被同步。
• server-id: MySQL 在主從環境下的唯一標志符，給個任意數字，注意不能和從機重復。
• 修改 binlog_format 的值為 STATEMENT，這一點很關鍵。

配置完成后重啟 MySQL 服務端：

docker restart mysql33061

（3）查看主服務器當前二進制日志名和偏移量

這個操作的目的是為了在從數據庫啟動后，從這個點開始進行數據的恢復：

show master status;

再看一眼 binlog_format 設置成功沒：

可以看到，沒問題。

至此，主機配置完成。

1.2 從機配置

從機的配置也比較簡單，我們一步一步來看：

（1） 在/etc/my.cnf 添加配置

注意從機這里只需要配置一下 server-id 即可。

注意：如果從機是從主機復制來的，即我們通過復制 CentOS 虛擬機獲取了 MySQL 實例 ，此時兩個 MySQL 的 uuid 一樣(正常安裝是不會相同的)，這時需要手動修改，修改位置在 /var/lib/mysql/auto.cnf ，注意隨便修改這里幾個字符即可，但也不可太過于隨意，例如修改了 uuid 的長度。

配置完成后，記得重啟從機。

（2）使用命令來配置從機

change master to master_host='10.3.50.27',master_port=33061,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000001',master_log_pos=154;

這里配置了主機地址、端口以及從機登錄主機的用戶名和密碼，注意最后兩個參數要和 master 中的保持一致。

注意，由于 MySQL8 密碼插件的問題，這個問題同樣會給主從配置帶來問題，所以在 MySQL8 配置主從上，上面這行命令需要添加 get_master_public_key=1，完整命令如下：

change master to master_host='10.3.50.27',master_port=33061,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000001',master_log_pos=154,get_master_public_key=1;

（3）啟動 slave 進程

start slave;

啟動之后查看從機狀態：

show slave status\G;

（4）查看 slave 的狀態

主要是下面兩項值都要為為 YES，則表示配置正確：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

至此，配置完成，主機創建庫，添加數據，從機會自動同步。

如果這兩個有一個不為 YES ，表示主從環境搭建失敗，此時可以閱讀日志，查看出錯的原因，再具體問題具體解決。

具體的同步過程如下：

• 首先在從機 33062 上通過 change master 命令，設置主機 33061 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog(master_log_pos)，這個位置包含文件名和日志偏移量。
• 在從機 33061 上執行 start slave 命令，這時候從機會啟動兩個線程，分別是 io_thread 和 sql_thread。
• io_thread 負責與主機建立連接。
• 主機 33061 校驗完用戶名、密碼后，開始按照從機 33062 傳過來的位置，從本地讀取 binlog，發給 33062。
• 從機 33062 拿到 binlog 后，寫到本地文件，稱為中轉日志(relay log)。
• sql_thread 線程讀取中轉日志，解析出日志里的命令，并執行。

大致就是這樣一個流程。

2. 數據不一致問題

接下來我們創建一個 javaboy_db 的數據庫，并在里邊創建一個 user 表，user 表的定義如下：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(128) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下來我們在主機中向 user 表中插入一條記錄，如下：

按道理，這條記錄會同步到 33062 這臺從機上：

大家看到，數據確實同步了，但是 uuid 卻不一樣。

3. 原因分析

我們知道，MySQL 主從同步最主要的依據就是 binlog，master 將自己的 binlog 發給 slave，slave 重放之后獲取和 master 一致的數據。

那我們就來看看 master 生成的 binlog 是啥樣子。

我們按照事件的方式來看一下 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：查看多少行記錄，不指定就是查看所有。

查看命令如下(我這里就從 pos 為 154 的位置開始)：

show binlog events IN 'javaboy_logbin.000001' FROM 154;

查看結果如下(部分)：

從圖中可以看到，記錄在 binlog 原文中的日志是：use javaboy_db; insert into user(uuid,name) values(uuid(),'javaboy')。

這句 SQL 將來同步到 slave 之后，slave 照著執行一下，那必然出現執行結果不一致的問題，因為 uuid() 函數每次執行結果都不一樣。

現在小伙伴們看明白問題的原因了吧。

4. 問題解決

問題倒也好解決，上篇文章我們說過，我們可以將 binlog_format 設置為 ROW 來解決這個問題。

具體操作步驟如下。

在主機中，修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，將 binlog_format 改為 ROW，如下：

修改完成后，重啟主機，主機重啟之后，會產生新的 binlog 文件，所以我們需要重新查看主機的最新狀態并重新配置從機，先來看主機，如下：

以此為依據，讓從機重新連接主機，在從機上再進行如下操作：

stop slave;

change master to master_host='10.3.50.27',master_port=33061,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000002',master_log_pos=794;

start slave;

重新配置完從機之后，我們繼續向 user 表插入一條數據，插入完成后，我們再去看從機的數據，發現此時的數據已經是一致的了。

解決這個問題，我們最主要的更改就是修改了 binlog_format 為 ROW，當我們把 binlog_format 改為 ROW 之后，我們來看看此時 binlog 中都記錄了啥。

show binlog events IN 'javaboy_logbin.000002' FROM 794;

大家看到，在 BEGIN 和 COMMIT 之間，就是我們的數據修改操作。

• Table_map：這一行是說明了接下來要操作 javaboy_db.user 表。
• Write_rows：這一行是說明了要寫一行新的數據了。

不過這里看不出啥端倪來，我們借助 mysqlbinlog 工具來看看是否有新的發現。

為了查看 binlog，MySQL 為我們提供了兩個官方工具，除了上面的 show binlog events，另一個就是 mysqlbinlog 命令，如下(注意在系統中執行該命令，不是在 MySQL 終端執行該命令)：

mysqlbinlog -vv /var/lib/mysql/javaboy_logbin.000002 --start-position=794;

-vv 表示顯示詳細信息，這樣就會打印出 binlog 中二進制文件的內容。

這里的內容比較多，我們來看幾個比較關鍵的地方：

• Table_map: javaboy_db.user mapped to number 108：這表示接下來要操作編號為 108 的表，每張表都有一個自己的編號。
• Write_rows: table id 108 flags: STMT_END_F：這個就是具體的添加操作了，向編號為 108 的表中添加一條記錄。

接下來那兩行，大致上瞅一眼，像是 Base64 轉碼后的內容，大家感興趣的可以自行解碼看看，解碼后有一些是亂碼的，但是有一些字符串如 uuid 則沒有亂碼，我們也能大致猜出來這里存儲的內容。

接下來我們看下面記錄的 SQL，如下：

這就是日志中記錄的內容，可以看到，每個字段上具體的值是啥，都寫下來了，這樣當然就不會發生數據不一致的情況了。

5. 小結

好啦，今天通過一個簡單的案例，跟小伙伴們分享了 binlog 兩種不同的日志格式，另外還有一中 MIXED 格式現在很少用了，感興趣的小伙伴可以結合上篇文章的內容，在本文案例的基礎上繼續測試 MIXED 模式，這里我就不贅述啦！

本文轉載自微信公眾號「江南一點雨」，可以通過以下二維碼關注。轉載本文請聯系江南一點雨公眾號。