MySQL實現設定主從複製專案實踐

2022-03-31 13:00:57

一、檢測通訊

檢視master（centos7）和slave（win10）的ip地址，並檢測是否可以相互通訊

到這裡我們知道，master的ip為192.168.131.129，slave的ip為192.168.0.6，並且可以相互通訊

檢視防火牆狀態

systemctl status firewalld.service

臨時手動啟動、停止防火牆

systemctl start firewalld.service
systemctl stop firewalld.service

持久開啟、關閉防火牆（重啟服務生效）

systemctl enable firewalld.service
systemctl disable firewalld.service

開放3306埠

二、master設定

1. 開啟二進位制紀錄檔

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

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地址，錯誤紀錄檔（可在my.cnf中指定）中會有如下資訊：

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

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

所以建立賬戶的命令應如下：

mysql> CREATE USER 'mslave'@'192.168.131.1' IDENTIFIED BY '1qaz@WSX';
mysql> GRANT REPLICATION SLAVE ON *.* to 'mslave'@'192.168.131.1' IDENTIFIED BY '1qaz@WSX';
mysql> FLUSH PRIVILEGES;

開啟主從複製的許可權，從庫可以通過這個賬戶和密碼，從這個IP來請求存取這個主庫上的任意庫，同步這個主庫的任意庫裡的任意表

3. 獲取binlog檔名和position

看當前二進位制紀錄檔的名字，主庫的更新是往哪個binlog寫的，以及當前寫紀錄檔的位置，從這個位置往後開始進行主從同步

show master status;

三、slave設定

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

找到my.ini

設定全域性唯一的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一下

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

如果都正確，還可以檢查一下master的3306埠是否可以連線

telnet xxx.xxx.xxx.xxx 3306

最重要的是，自己玩的時候，如果虛擬機器器是NAT模式，則需要寫成VMnet8閘道器ip。如果都是物理機通訊，那直接寫正確的ip即可

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

然後重新賦予許可權

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

出現錯誤後還可以檢視錯誤紀錄檔中提示的ip是否和自己允許slave登入的ip一致

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，來檢視主從同步的狀態，有什麼錯誤，就相應解決

要麼在stop slave，把position重新設定一下，start slave，即重新開啟主從同步，從最新的位置，這個drop操作不需要在從庫上同步

要麼就是stop，跳過該個錯誤，然後start

stop slave;
set global sql_slave_skip_counter=1;
start slave;

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

到此這篇關於MySQL實現設定主從複製專案實踐的文章就介紹到這了,更多相關MySQL 主從複製內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！