MySQL 主從複製資料不一致的解決方法

2022-03-18 19:01:17

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 statusG;

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，如下：