Mysql主從複製原理及搭建

小熊我不要了發表於2019-08-22
MySql

Mysql主從複製原理

主從複製是指一臺伺服器充當主資料庫伺服器，另一臺或多臺伺服器充當從資料庫伺服器，主伺服器中的資料自動複製到從伺服器之中。對於多級複製，資料庫伺服器即可充當主機，也可充當從機。MySQL主從複製的基礎是主伺服器對資料庫修改記錄二進位制日誌，從伺服器通過主伺服器的二進位制日誌自動執行更新。

Mysq主從複製的型別

  • 基於語句的複製：

    主伺服器上面執行的語句在從伺服器上面再執行一遍，在MySQL-3.23版本以後支援。

    存在的問題：時間上可能不完全同步造成偏差，執行語句的使用者也可能是不同一個使用者。

  • 基於行的複製：

    把主伺服器上面改變後的內容直接複製過去，而不關心到底改變該內容是由哪條語句引發的，在MySQL-5.0版本以後引入。

    存在的問題：如果修改的行數過多，造成的開銷比較大。

MySQL預設使用基於語句的複製，當基於語句的複製會引發問題的時候就會使用基於行的複製，MySQL會自動進行選擇。

在MySQL主從複製架構中，讀操作可以在所有的伺服器上面進行，而寫操作只能在主伺服器上面進行。主從複製架構雖然給讀操作提供了擴充套件，可如果寫操作也比較多的話（多臺從伺服器還要從主伺服器上面同步資料），單主模型的複製中主伺服器勢必會成為效能瓶頸。

Mysql主從複製的工作原理

file

如上圖所示，主伺服器上面的任何修改都會儲存在二進位制日誌Binary log裡面，從伺服器上面啟動一個I/O thread（實際上就是一個主伺服器的客戶端程式），連線到主伺服器上面請求讀取二進位制日誌，然後把讀取到的二進位制日誌寫到本地的一個Realy log裡面。從伺服器上面開啟一個SQL thread定時檢查Realy log，如果發現有更改立即把更改的內容在本機上面執行一遍。

如果一主多從的話，這時主庫既要負責寫又要負責為幾個從庫提供二進位制日誌。此時可以稍做調整，將二進位制日誌只給某一從，這一從再開啟二進位制日誌並將自己的二進位制日誌再發給其它從。或者是乾脆這個從不記錄只負責將二進位制日誌轉發給其它從，這樣架構起來效能可能要好得多，而且資料之間的延時應該也稍微要好一些。

Mysql主從複製的過程

  1. Slave上面的IO程式連線上Master，並請求從指定日誌檔案的指定位置（或者從最開始的日誌）之後的日誌內容。
  2. Master接收到來自Slave的IO程式的請求後，負責複製的IO程式會根據請求資訊讀取日誌指定位置之後的日誌資訊，返回給Slave的IO程式。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置。
  3. Slave的IO程式接收到資訊後，將接收到的日誌內容依次新增到Slave端的relay-log檔案的最末端，並將讀取到的Master端的 bin-log的檔名和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master從何處開始讀取日誌。
  4. Slave的Sql程式檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，並在自身執行。

linux安裝mysql8並且實現主從同步

伺服器準備

準備伺服器Server1和Server2，如果在同一個伺服器的話則安裝mysql時需要改變其埠。

解除安裝mysql

在安裝之前必須先檢查主機上有沒有安裝過mysql，如果安裝過的話必須先解除安裝。

安裝mysql

下載軟體包：

wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

本地安裝：

yum localinstall mysql80-community-release-el7-1.noarch.rpm

安裝mysql：

yum install mysql-community-server

設為開機啟動：

systemctl enable mysqld

systemctl daemon-reload

啟動mysql：

systemctl start mysqld

以上步驟就安裝好mysql8了。

獲取mysql的臨時密碼：

grep 'temporary password' /var/log/mysqld.log

登入mysql：

mysql -uroot -p

會提示輸入密碼，輸入之前獲取的臨時密碼即可登入。

此時需要修改mysql的密碼，要不然之後的步驟也會強制提示你需要修改密碼：

ALTER USER 'root'@'localhost' IDENTIFIED BY '121b33dAj934J1^Sj9ag';

mysql8預設對密碼的強度有要求，需要設定複雜一點，要不然也會提示錯誤。

重新整理配置：

FLUSH PRIVILEGES;

主從配置

在主從配置之前需要確保兩臺mysql需要同步的庫狀態一致。

主

配置檔案預設在/etc/my.cnf下。

在配置檔案中新增配置：

[mysqld]
## 同一區域網內注意要唯一
server-id=100  
## 開啟二進位制日誌功能，可以隨便取（關鍵）
log-bin=mysql-bin
複製程式碼

修改配置後需要重啟才能生效：

service mysql restart

重啟之後進入mysql：

mysql -uroot -p

在master資料庫建立資料同步使用者，授予使用者 slave REPLICATION SLAVE許可權和REPLICATION CLIENT許可權，用於在主從庫之間同步資料。

CREATE USER 'slave'@'%' IDENTIFIED BY '@#$Rfg345634523rft4fa';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

語句中的%代表所有伺服器都可以使用這個使用者，如果想指定特定的ip，將%改成ip即可。

檢視主mysql的狀態：

show master status;

記錄下File和Position的值，並且不進行其他操作以免引起Position的變化。

從

在從my.cnf配置中新增：

mysqld]
## 設定server_id,注意要唯一
server-id=101  
## 開啟二進位制日誌功能，以備Slave作為其它Slave的Master時使用
log-bin=mysql-slave-bin   
## relay_log配置中繼日誌
relay_log=edu-mysql-relay-bin  
複製程式碼

修改配置後需要重啟才能生效：

service mysql restart

重啟之後進入mysql：

mysql -uroot -p

change master to master_host='172.17.0.2', master_user='slave', master_password='@#$Rfg345634523rft4fa', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 2830, master_connect_retry=30;

master_host ：Master的地址

master_port：Master的埠號

master_user：用於資料同步的使用者

master_password：用於同步的使用者的密碼

master_log_file：指定 Slave 從哪個日誌檔案開始複製資料，即上文中提到的 File 欄位的值

master_log_pos：從哪個 Position 開始讀，即上文中提到的 Position 欄位的值

master_connect_retry：如果連線失敗，重試的時間間隔，單位是秒，預設是60秒

在從mysql中檢視主從同步狀態：

show slave status \G;

此時的SlaveIORunning 和 SlaveSQLRunning 都是No，因為我們還沒有開啟主從複製過程。

開啟主從複製：

start slave;

再次檢視同步狀態：

show slave status \G;

SlaveIORunning 和 SlaveSQLRunning 都是Yes說明主從複製已經開啟。

若SlaveIORunning一直是Connecting，有下面4種原因：

1、網路不通，檢查ip埠

2、密碼不對，檢查用於同步的使用者名稱和密碼

3、pos不對，檢查Master的Position

4、mysql8特有的密碼規則問題引起：

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '@#$Rfg345634523rft4fa';

將密碼規則修改為：mysql_native_password

如果需要指定想要主從同步哪個資料庫，可以在master的my.cnf新增配置：

binlog-do-db：指定mysql的binlog日誌記錄哪個db

或者在slave的my.cnf新增配置：

replicate-do-db=需要複製的資料庫名，如果複製多個資料庫，重複設定這個選項即可 replicate-ignore-db=需要複製的資料庫名，如果複製多個資料庫，重複設定這個選項即可

如果想要同步所有庫和表，在從mysql執行：

STOP SLAVE SQL_THREAD; CHANGE REPLICATION FILTER REPLICATE_DO_DB = (); start SLAVE SQL_THREAD;

如果以上步驟出現問題，可以檢視日誌：

/etc/log/mysqld.log

至此完成了mysql8主從同步搭建工作。

相關文章