MySQL 設定主從複製實踐分享

2022-07-14 14:01:23

一、檢測通訊

檢視主庫（master）和從庫（slave）的ip地址，並檢測是否可以通訊

保證master和slave之間網路是互通的，用ping命令檢測

到這裡我們知道，master的ip為192.168.131.129，slave的ip為192.168.0.6，並且可以相互通訊。保證3306埠開放

檢視防火牆狀態systemctl status firewalld.service

臨時手動啟動防火牆systemctl start firewalld.service
臨時手動停止防火牆systemctl stop firewalld.service

持久開啟防火牆（重啟服務生效）systemctl enable firewalld.service
持久關閉防火牆（重啟服務生效）systemctl disable firewalld.service

檢視當前開放的埠列表firewall-cmd --list-ports

二、master設定

1. 開啟二進位制紀錄檔

設定log_bin和全域性唯一的server-id，和slave區分開，不能設定成一樣的（如果是my.cnf新新增設定，一定要重啟MySQL服務）

vim /etc/my.cnf開啟my.cnf檔案

2. 建立一個用於主從庫通訊用的賬號

即在master中建立一個賬號，用於slave登入master讀取binlog

雖然我們在Linux上檢視的ip地址是192.168.131.129，但我們建立賬戶登入時不寫這個ip，寫的是192.168.131.1。因為我這裡虛擬機器器用的是NAT模式（如果是橋接模式就可以直接用了），虛擬機器器（master）和物理機（slave）通訊的時候，虛擬機器器先把資料傳送到閘道器192.168.131.1（預設與VMnet8通訊），192.168.131.1再轉發到物理機，所以物理機接收到的是192.168.131.1的資料，故我們在master上為slave建立賬戶的時候，應該寫192.168.131.1

如果給slave設定的不是閘道器192.168.131.1地址，vim + /var/log/mysqld.log開啟錯誤紀錄檔會有如下資訊：

這說的就是從192.168.131.1的mslave許可權不夠，那是因為我們在master設定的是允許從其他地方登入，並不允許從192.168.131.1地址登入，導致許可權不夠。

由於master這邊收到的是來自192.168.131.1的請求，所以錯誤紀錄檔顯示的是192.168.131.1

建立使用者的命令：

//如果嫌麻煩可以用%代替192.168.131.1，，它就可以匹配任何ip
mysql> CREATE USER 'mslave'@'192.168.131.1' IDENTIFIED BY '1qaz@WSX';
//啟動主從，在主庫上給當前的mslave使用者開啟REPLICATION SLAVE主從複製的許可權,從庫就可以通過1qaz@WSX賬戶密碼
//從192.168.131.1 IP地址來請求存取這臺主庫上的任意庫裡面的任意表*.*,同步這個主庫的任意庫裡的任意表
mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'192.168.131.1' IDENTIFIED BY '1qaz@WSX';
mysql> FLUSH PRIVILEGES;

3. 獲取binlog的紀錄檔檔名和position

show master status

三、slave設定

1. 設定全域性唯一的server-id

設定全域性唯一的server-id

涉及修改組態檔，需要重啟MySQL服務

2. 使用master建立的賬戶讀取binlog同步資料

這一步設定主要是給IO執行緒讀取binlog使用：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.131.129',
MASTER_PORT=3306,
MASTER_USER='mslave',
MASTER_PASSWORD='1qaz@WSX',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=1262;

MASTER_HOST：指定master的ip
MASTER_LOG_FILE：binlog檔名
MASTER_LOG_POS：binlog的position

3. 開啟slave服務

通過show slave status命令檢視主從複製狀態，show processlist檢視master和salve相關執行緒的執行狀態

四、設定中可能出現的問題

1. 網路連線問題

通過show slave status命令檢視主從複製狀態

連線connection錯粗，先考慮網路是否互通，ping一下：

然後再檢查從庫裡面的設定資訊是否正確

如果都正確，檢查主庫所在機器的3306埠是否正常

telnet xxx.xxx.xxx.xxx 3306

如果發現3306埠不能連通，就需要懷疑主庫對埠有限制嗎，也就是防火牆限制，就需要在防火牆把3306埠開放出來。

如果這個錯誤還沒解決，就檢視一個主庫的錯誤紀錄檔/var/log/mysql/mysqld.log，檢視錯誤紀錄檔中提示的ip是否和自己允許slave登入的ip一致

這說的就是從192.168.131.1的mslave許可權不夠，自己玩的時候，如果虛擬機器器是NAT模式，則需要寫成VMnet8閘道器ip。如果都是物理機通訊，那直接寫正確的ip即可

可以在MySQL資料庫下的mysql庫的user表中更改允許登入的ip

然後重新賦予許可權：

mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'xxx.xxx.xxx.xxx' IDENTIFIED BY '1qaz@WSX';

2. binlog的position問題

在master中檢視show master status一下binlog紀錄檔檔名以及position，然後用命令重新設定slave，比如：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.131.129',MASTER_PORT=3306,MASTER_USER='mslave',MASTER_PASSWORD='1qaz@WSX',
MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=1262;

設定slave前需要stop slave，設定完成再start slave

3. SQL執行緒出錯

錯誤原因：首先設定主從複製的時候，slave的mytest庫中沒有user表，而master的mytest庫已經有user表了。設定好主從複製後直接drop table mytest.user，這就會寫到binlog裡面，然後在通過dump執行緒和IO執行緒將這個操作傳送到從庫的relay log，然後從庫的SQL執行緒從relay log裡把drop table mytest.user撈出來在從庫執行這個SQL，可從庫的mytest根本就沒有user表，這就是刪除一個不存在的表，於是出現錯誤了。

一般我們是不會做這樣的操作的，我們一般都是主庫設定以後，slave從資料開始增量進行同步，不會同步以後一開始就刪主庫裡的東西，如果真的出現這樣的問題了，隨時可以通過show slave status來檢視主從庫的狀態來解決錯誤，如果是上圖這個錯誤，
（1）可以在從庫stop slave，然後把位置重新設定一下，然後再start slave，相當於重新開始主從同步的位置。
（2）可以在從庫stop slave，然後set global sql_slave_skip_counter=1;（跳過一個錯誤），然後再start slave重啟從庫的執行緒，相當於把錯誤跳過了，異常操作。

可以通過show slave status檢視以下標識，IO執行緒出錯一般是網路問題，SQL執行緒出錯一般是SQL在slave庫執行出現了問題