日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

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

1. 準(zhǔn)備工作

以下配置基于 Docker。

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

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

 

•  

  主機(jī)：10.3.50.27:33061

   

•  

  從機(jī)：10.3.50.27:33062

   

1.1 主機(jī)配置

 

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

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這個地址登錄，登錄成功之后可以操作任意庫中的任意表。其中，如果不需要限制登錄地址，可以將 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

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

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

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

如下圖：

•  

   

  log-bin：同步的日志路徑及文件名，一定注意這個目錄要是 MySQL 有權(quán)限寫入的（我這里是偷懶了，直接放在了下面那個datadir下面）。

   

•  

  binlog-do-db：要同步的數(shù)據(jù)庫名，當(dāng)從機(jī)連上主機(jī)后，只有這里配置的數(shù)據(jù)庫才會被同步，其他的不會被同步。

   

•  

  server-id: MySQL 在主從環(huán)境下的唯一標(biāo)志符，給個任意數(shù)字，注意不能和從機(jī)重復(fù)。

   

•  

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

   

 

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

docker restart mysql33061

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

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

show master status;

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

可以看到，沒問題。

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

1.2 從機(jī)配置

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

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

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

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

配置完成后，記得重啟從機(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ī)的用戶名和密碼，注意最后兩個參數(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 進(jìn)程

start slave;

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

show slave statusG;

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

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

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

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

具體的同步過程如下：

 

1.  

   首先在從機(jī) 33062 上通過 change master 命令，設(shè)置主機(jī) 33061 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog（master_log_pos），這個位置包含文件名和日志偏移量。

    

2.  

   在從機(jī) 33061 上執(zhí)行start slave命令，這時候從機(jī)會啟動兩個線程，分別是io_thread和sql_thread。

    

3.  

   io_thread負(fù)責(zé)與主機(jī)建立連接。

    

4.  

   主機(jī) 33061 校驗(yàn)完用戶名、密碼后，開始按照從機(jī) 33062 傳過來的位置，從本地讀取 binlog，發(fā)給 33062。

    

5.  

   從機(jī) 33062 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。

    

6.  

   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;

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

按道理，這條記錄會同步到 33062 這臺從機(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];

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

 

•  

  log_name：可以指定要查看的 binlog 日志文件名，如果不指定的話，表示查看最早的 binlog 文件。

   

•  

  pos：從哪個 pos 點(diǎn)開始查看，凡是 binlog 記錄下來的操作都有一個 pos 點(diǎn)，這個其實(shí)就是相當(dāng)于我們可以指定從哪個操作開始查看日志，如果不指定的話，就是從該 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)?code>uuid()函數(shù)每次執(zhí)行結(jié)果都不一樣。

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

4. 問題解決

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

具體操作步驟如下。

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

修改完成后，重啟主機(jī)，主機(jī)重啟之后，會產(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ù)，插入完成后，我們再去看從機(jī)的數(shù)據(jù)，發(fā)現(xiàn)此時的數(shù)據(jù)已經(jīng)是一致的了。

解決這個問題，我們最主要的更改就是修改了 binlog_format 為 ROW，當(dāng)我們把 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 表示顯示詳細(xì)信息，這樣就會打印出 binlog 中二進(jìn)制文件的內(nèi)容。

   

 

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

 

1.  

   Table_map:javaboy_db.usermApped to number 108：這表示接下來要操作編號為 108 的表，每張表都有一個自己的編號。

    

2.  

   Write_rows: table id 108 flags: STMT_END_F：這個就是具體的添加操作了，向編號為 108 的表中添加一條記錄。

    

 

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

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

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

5. 小結(jié)

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

END