關(guān)于 MySQL 主從搭建，松哥之前寫過好多篇文章了，還錄過一個視頻。不過之前的都是一主一從的結(jié)構(gòu)，但是小伙伴們知道，我們在項目中，更常見一種結(jié)構(gòu)是雙 M 結(jié)構(gòu)，即兩個 MySQL 實例，每個 MySQL 實例互為主備，這樣在主節(jié)點突然斷電或者不可用的時候，slave 節(jié)點可以很快切換為 master，架構(gòu)圖如下：

在這種結(jié)構(gòu)中，兩個 MySQL 實例的地位是平等的，互為對方的主備，我們判斷誰是主機誰是從機的方式主要是看 readonly，誰是只讀的，那誰就是從機，所以這種情況下，主從切換也很方便，只要修改 readonly 屬性即可。

接下來我們就來搭建一個雙 M 的主從備份，看看和單純的 M-S 結(jié)構(gòu)的有啥區(qū)別。

1. 準(zhǔn)備工作

以下配置基于 Docker。

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

• M1：10.3.50.27:33061
• M2：10.3.50.27:33062

1.1 M1 配置

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

（1）授權(quán)給 M2 服務(wù)器

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

這里表示配置 M2 登錄用戶名為 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 格式，注意不要使用 STATEMENT
binlog_format=ROW
# 設(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

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

如下圖：

• log-bin：同步的日志路徑及文件名，一定注意這個目錄要是 MySQL 有權(quán)限寫入的（我這里是偷懶了，直接放在了下面那個datadir下面）。
• binlog-do-db：要同步的數(shù)據(jù)庫名，當(dāng)從機連上主機后，只有這里配置的數(shù)據(jù)庫才會被同步，其他的不會被同步。
• server-id: MySQL 在主從環(huán)境下的唯一標(biāo)志符，給個任意數(shù)字，注意不能和 M2 重復(fù)，因為將來 server-id 用于標(biāo)志 binlog 是由哪個庫產(chǎn)生的，所以主從數(shù)據(jù)庫的 server-id 千萬不能一樣，不然可能導(dǎo)致主從數(shù)據(jù)庫 binlog 的循環(huán)復(fù)制問題。
• 注意 binlog_format 的值為 ROW，具體原因在之前的文章中松哥已經(jīng)和大家聊過了，這里就不再贅述。

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

docker restart mysql33061

（3）查看 M1 當(dāng)前二進制日志名和偏移量

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

show master status;

至此，M1 配置完成。

1.2 M2 配置

M2 的配置和 M1 一模一樣，唯一不同的地方在于，M2 的 mysqld.cnf 這個文件中的 server-id=2，其他都一模一樣，我就不重復(fù)了。

配置完成后，相當(dāng)于 M2 現(xiàn)在也是一個主機，我們在 M2 上也可以執(zhí)行 show master status; 命令，結(jié)果如下：

1.3 主從配置

接下來配置 M1 和 M2 分別為對方的主機。

M1 配置

先來配置給 M1 配置吧，執(zhí)行如下命令設(shè)置主機：

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

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

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

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

（1） 啟動 slave 進程

start slave;

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

show slave status\G;

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

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

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

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

M2 配置

接下來再來配置 M2，M2 和 M1 的配置基本上是一致的，change master 中記得把地址和端口寫對：

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

配置完成后，現(xiàn)在 M1 和 M2 就互為主備了。

1.4 測試

測試分兩步：

• M1 中新建 javaboy_db 庫，庫中建 user 表，表中插入一條記錄，然后查看 M2 中是否將數(shù)據(jù)同步過來了。
• M2 中向 user 表中添加一條記錄，查看 M1 中是否有對應(yīng)的值。

經(jīng)過測試，我們發(fā)現(xiàn)沒問題，現(xiàn)在可以兩邊互相同步對方的數(shù)據(jù)了。

2. 誰主誰從

雖然是雙 M 結(jié)構(gòu)，但是在實際應(yīng)用中還是得分個主從，那么雙 M 該怎么分主從呢？

在生產(chǎn)環(huán)境中，我們一般會將備份節(jié)點設(shè)置為 read_only ，也就是只讀，防止有誤操作，當(dāng)然不用擔(dān)心設(shè)置為 read_only 后 binlog 的寫入也被阻止，super 用戶依然擁有寫入權(quán)限。

設(shè)置全庫只讀的辦法也很簡單，首先我們執(zhí)行如下 SQL 先看看對應(yīng)變量的值：

show variables like 'read_only';

可以看到，默認(rèn)情況下， read_only 是 OFF，即關(guān)閉狀態(tài)，我們先把它改為 ON，執(zhí)行如下 SQL：

set global read_only=1;

1 表示 ON，0 表示 OFF，執(zhí)行結(jié)果如下：

這個 read_only 對 super 用戶無效，所以設(shè)置完成后，接下來我們退出來這個會話，然后創(chuàng)建一個不包含 super 權(quán)限的用戶，用新用戶登錄，登錄成功之后，執(zhí)行一個插入 SQL，結(jié)果如下：

可以看到，這個錯誤信息中說，現(xiàn)在的 MySQL 是只讀的（只能查詢），不能執(zhí)行當(dāng)前 SQL。

如此設(shè)置之后，在 master 發(fā)生異常需要主從切換的時候再將 slave 臨時頂替上來。為了更好的做到主從同步，binlog 的類型建議使用 row 模式。