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

1. 準備工作

以下配置基于 Docker。

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

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

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

1.1 主機配置

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

1. 授權(quán)給從機服務(wù)器

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

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

注意，在 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ù)才會生效

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

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

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

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

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

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

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

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

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

如下圖：

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

修改 binlog_format 的值為 STATEMENT，這一點很關(guān)鍵。

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

docker restart mysql33061

3. 查看主服務(wù)器當前二進制日志名和偏移量

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

show master status;

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

可以看到，沒問題。

至此，主機配置完成。

1.2 從機配置

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

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

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

注意：如果從機是從主機復(fù)制來的，即我們通過復(fù)制 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;

這里配置了主機地址、端口以及從機登錄主機的用戶名和密碼，注意最后兩個參數(shù)要和 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;

啟動之后查看從機狀態(tài)：

show slave status\G;

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

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

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

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

具體的同步過程如下：

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

大致就是這樣一個流程。

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

接下來我們創(chuàng)建一個 javaboy_db 的數(shù)據(jù)庫，并在里邊創(chuàng)建一個 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 這臺從機上：

大家看到，數(shù)據(jù)確實同步了，但是 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];

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

• log_name：可以指定要查看的 binlog 日志文件名，如果不指定的話，表示查看最早的 binlog 文件。
• pos：從哪個 pos 點開始查看，凡是 binlog 記錄下來的操作都有一個 pos 點，這個其實就是相當于我們可以指定從哪個操作開始查看日志，如果不指定的話，就是從該 binlog 的開頭開始查看。
• offset：這是是偏移量，不指定默認就是 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é)果不一致的問題，因為 uuid() 函數(shù)每次執(zhí)行結(jié)果都不一樣。

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

4. 問題解決

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

具體操作步驟如下。

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

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

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

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;

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

解決這個問題，我們最主要的更改就是修改了 binlog_format 為 ROW，當我們把 binlog_format 改為 ROW 之后，我們來看看此時 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 為我們提供了兩個官方工具，除了上面的 show binlog events，另一個就是 mysqlbinlog 命令，如下（注意在系統(tǒng)中執(zhí)行該命令，不是在 MySQL 終端執(zhí)行該命令）：

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

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

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

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

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

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

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

5. 小結(jié)

到此這篇關(guān)于MySQL 主從復(fù)制數(shù)據(jù)不一致的解決方法的文章就介紹到這了,更多相關(guān)MySQL 主從復(fù)制數(shù)據(jù)不一致內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論