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

書接上回，閑話不表。

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

1. 準(zhǔn)備工作

以下配置基于 Docker。

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

這里，我們準(zhǔn)備兩臺(tái)機(jī)器：

• 主機(jī)：10.3.50.27:33061
• 從機(jī)：10.3.50.27:33062

1.1 主機(jī)配置

主機(jī)的配置就三個(gè)步驟，比較容易：

（1）授權(quán)給從機(jī)服務(wù)器

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

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

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

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 ，并設(shè)置 server-id ，每次修改配置文件后都要重啟 MySQL 服務(wù)才會(huì)生效。

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

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

[mysqld]
# 這個(gè)參數(shù)表示啟用 binlog 功能，并指定 binlog 的存儲(chǔ)目錄
log-bin=javaboy_logbin
# 設(shè)置 binlog_format 格式
binlog_format=STATEMENT
# 設(shè)置一個(gè) binlog 文件的最大字節(jié)
# 設(shè)置最大 100MB
max_binlog_size=104857600

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

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

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

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

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

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

如下圖：

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

配置完成后重啟 MySQL 服務(wù)端：

docker restart mysql33061

（3）查看主服務(wù)器當(dāng)前二進(jìn)制日志名和偏移量

這個(gè)操作的目的是為了在從數(shù)據(jù)庫啟動(dòng)后，從這個(gè)點(diǎn)開始進(jìn)行數(shù)據(jù)的恢復(fù)：

show master status;

再看一眼 binlog_format 設(shè)置成功沒：

可以看到，沒問題。

至此，主機(jī)配置完成。

1.2 從機(jī)配置

從機(jī)的配置也比較簡(jiǎn)單，我們一步一步來看：

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

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

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

配置完成后，記得重啟從機(jī)。

（2）使用命令來配置從機(jī)

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;

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

注意，由于 MySQL8 密碼插件的問題，這個(gè)問題同樣會(huì)給主從配置帶來問題，所以在 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）啟動(dòng) slave 進(jìn)程

start slave;

啟動(dòng)之后查看從機(jī)狀態(tài)：

show slave status\G;

（4）查看 slave 的狀態(tài)

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

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

至此，配置完成，主機(jī)創(chuàng)建庫，添加數(shù)據(jù)，從機(jī)會(huì)自動(dòng)同步。

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

具體的同步過程如下：

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

大致就是這樣一個(gè)流程。

2. 數(shù)據(jù)不一致問題

接下來我們創(chuàng)建一個(gè) javaboy_db 的數(shù)據(jù)庫，并在里邊創(chuàng)建一個(gè) 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;

接下來我們?cè)谥鳈C(jī)中向 user 表中插入一條記錄，如下：

按道理，這條記錄會(huì)同步到 33062 這臺(tái)從機(jī)上：

大家看到，數(shù)據(jù)確實(shí)同步了，但是 uuid 卻不一樣。

3. 原因分析

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

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

我們按照事件的方式來看一下 binlog，命令格式如下：

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

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

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

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

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

查看結(jié)果如下(部分)：

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

這句 SQL 將來同步到 slave 之后，slave 照著執(zhí)行一下，那必然出現(xiàn)執(zhí)行結(jié)果不一致的問題，因?yàn)?uuid() 函數(shù)每次執(zhí)行結(jié)果都不一樣。

現(xiàn)在小伙伴們看明白問題的原因了吧。

4. 問題解決

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

具體操作步驟如下。

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

修改完成后，重啟主機(jī)，主機(jī)重啟之后，會(huì)產(chǎn)生新的 binlog 文件，所以我們需要重新查看主機(jī)的最新狀態(tài)并重新配置從機(jī)，先來看主機(jī)，如下：

以此為依據(jù)，讓從機(jī)重新連接主機(jī)，在從機(jī)上再進(jìn)行如下操作：

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;

重新配置完從機(jī)之后，我們繼續(xù)向 user 表插入一條數(shù)據(jù)，插入完成后，我們?cè)偃タ磸臋C(jī)的數(shù)據(jù)，發(fā)現(xiàn)此時(shí)的數(shù)據(jù)已經(jīng)是一致的了。

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

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

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

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

不過這里看不出啥端倪來，我們借助 mysqlbinlog 工具來看看是否有新的發(fā)現(xiàn)。

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

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

-vv 表示顯示詳細(xì)信息，這樣就會(huì)打印出 binlog 中二進(jìn)制文件的內(nèi)容。

這里的內(nèi)容比較多，我們來看幾個(gè)比較關(guān)鍵的地方：

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

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

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

這就是日志中記錄的內(nèi)容，可以看到，每個(gè)字段上具體的值是啥，都寫下來了，這樣當(dāng)然就不會(huì)發(fā)生數(shù)據(jù)不一致的情況了。

5. 小結(jié)

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

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