MySQL全面瓦解27：主從複製（原理 + 實踐）

翁智華發表於2021-08-17
MySql

概念

主從複製，是指建立一個和主資料庫完全一樣的資料庫環境（稱為從資料庫），並將主庫的操作行為進行復制的過程：將主資料庫的DDL和DML的操作日誌同步到從資料庫上，

然後在從資料庫上對這些日誌進行重新執行，來保證從資料庫和主資料庫的資料的一致性。

為什麼要做主從複製

1、在複雜的業務操作中，經常會有操作導致鎖行甚至鎖表的情況，如果讀寫不解耦，會很影響執行中的業務，使用主從複製，讓主庫負責寫，從庫負責讀。

即使主庫出現了鎖表的情景，通過讀從庫也可以保證業務的正常執行。

2、保證資料的熱備份，主庫當機後能夠及時替換主庫，保障業務可用性。

3、架構的演進：業務量擴大，I/O訪問頻率增高，單機無法滿足，主從複製可以做多庫方案，降低磁碟I/O訪問的頻率，提高單機的I/O效能。

4、本質上也是分治理念，主從複製、讀寫分離即是壓力分拆的過程。

主從複製的原理

當在從庫上啟動複製時，首先建立I/O執行緒連線主庫，主庫隨後建立Binlog Dump執行緒讀取資料庫事件併傳送給I/O執行緒，I/O執行緒獲取到事件資料後更新到從庫的中繼日誌Relay Log中去，之後從庫上的SQL執行緒讀取中繼日誌Relay Log中更新的資料庫事件並應用，

如下圖所示：

  

細化一下有如下幾個步驟：

1、MySQL主庫在事務提交時把資料變更（insert、delet、update）作為事件日誌記錄在二進位制日誌表（binlog）裡面。

2、主庫上有一個工作執行緒 binlog dump thread，把binlog的內容傳送到從庫的中繼日誌relay log中。

3、從庫根據中繼日誌relay log重做資料變更操作，通過邏輯複製來達到主庫和從庫的資料一致性。

4、MySQL通過三個執行緒來完成主從庫間的資料複製，其中binlog dump執行緒跑在主庫上，I/O執行緒和SQL執行緒跑在從庫上。擁有多個從庫的主庫會為每一個連線到主庫的從庫建立一個binlog dump執行緒。 

搭建主從例項

我們這邊在個人PC機上進行MySQL主從複製的搭建測試，所以使用Docker會更方便。有如下優勢：

1、可以節省資源。

2、相對於Docker來說，虛擬機器搭建對機器配置有要求，且安裝MySQL步驟繁瑣。

3、一臺機器上可以執行多個Docker容器，所以我們可以部署多個MySQL服務。

4、Docker容器之間相互獨立，有獨立ip，互不衝突

5、Docker使用步驟簡便，啟動容器在秒級別。

Daocker安裝

我這邊是mac機，以此作為示範。可以用Homebrew 進行安裝，也可以手動下載安裝。

手動下載的話可以點選以下連結下載 Edge Docker for Mac。

如同Mac OS 其它軟體一樣，安裝也非常簡單，雙擊下載的 .dmg 檔案，然後將鯨魚圖示拖拽到 Application 資料夾即可。 

從應用中找到 Docker 圖示並點選執行。去Docker Hub上去註冊一個Docker ID，進行登入即可。

開啟終端，可以檢視Docker相關的版本及相關資訊。可以輸入如下資訊：

1 # 檢視docker版本
2 docker --version
3 # 檢視docker基本資訊
4 docker info

搭建主從伺服器

1、拉取docker的MySQL映象，這邊以5.7的版本為準：

1 docker pull mysql:5.7    

2、使用此映象啟動主庫容器：

1 docker run -p 3307:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 

從這邊可以看出，Master主庫容器對外對映的埠是3307，賬號預設root，密碼是123456，執行完之後，docker中執行了一個名為master的MySQL例項。

docker容器是相互獨立的，每個容器有其獨立的ip，所以不同容器使用相同的埠並不會衝突。這裡我們應該儘量使用mysql預設的3306埠，否則可能會出現無法通過ip連線docker容器內mysql的問題。

因為容器內的3306埠對映的對外埠是3307，所以外部主機可以通過 宿主機ip:3307 訪問到MySQL的服務，而實際是訪問容器內MySQL的3306埠，密碼就是我們前面設定好的123456。

登入完之後可以看到一個空庫，這個就是我們在docker裡面拉的MySQL映象。

3、同樣的道理，我們繼續建一個slave（從庫）的MySQL例項。注意名稱為slave，埠為3308

1 docker run -p 3308:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

4、主從都搭建完成之後，我們可以使用  docker ps 來檢視當前執行的容器資訊，如下：

注意下第一個引數為 container id，是我們後續進入服務進行配置的容器識別編號。 

配置主伺服器（Master）

首先，進入到 Master 伺服器。

1 wengzhihua@B000000147796DS ~ % docker exec -it 777fe9ce7f9d /bin/bash
2 root@777fe9ce7f9d:/#

這邊注意：777fe9ce7f9d 是 master 的 container id，然後檢視MySQL的狀態。

1 root@777fe9ce7f9d:/# service mysql status
2 [info] MySQL Community Server 5.7.35 is running.

然後到MySQL的目錄下去修改配置，切換到/etc/mysql目錄下，然後 vi my.cnf 對my.cnf進行編輯：

1 root@777fe9ce7f9d:/# cd /etc/mysql
2 root@777fe9ce7f9d:/etc/mysql# vi my.cnf
3 bash: vi: command not found 

此時會報出 bash: vi: command not found ，需要我們在docker容器內部自行安裝vim。我們使用  apt-get install vim  命令來安裝vim

1 root@777fe9ce7f9d:/etc/mysql# apt-get install vim
2 Reading package lists... Done
3 Building dependency tree       
4 Reading state information... Done
5 E: Unable to locate package vim 

這邊又提示 Unable to locate package vim。

執行 apt-get update ，然後再次執行 apt-get install vim 即可成功安裝vim。

然後我們就可以使用vim編輯my.cnf，在my.cnf中新增如下配置：

 1 [mysqld]
 2 ## 設定server_id，一般設定為IP，同一區域網內使用唯一值即可，注意要保證唯一，這邊我們暫且使用主庫的對映埠，方便識別
 3 server_id=3307  
 4 ## 複製過濾：也就是指定哪個資料庫不用同步（mysql庫一般不同步）
 5 binlog-ignore-db=mysql  
 6 ## 開啟二進位制日誌功能，可以隨便取，最好有含義（關鍵就是這裡了）
 7 log-bin=test-mysql-bin  
 8 ## 為每個session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取
 9 binlog_cache_size=1M  
10 ## 主從複製的格式（mixed,statement,row，預設格式是statement）
11 binlog_format=mixed  
12 ## 二進位制日誌自動刪除/過期的天數。預設值為0，表示不自動刪除。
13 expire_logs_days=7  
14 ## 跳過主從複製中遇到的所有錯誤或指定型別的錯誤，避免slave端複製中斷。
15 ## 如：1062錯誤是指一些主鍵重複，1032錯誤是因為主從資料庫資料不一致
16 slave_skip_errors=1062   

配置完成之後重啟服務：

1 service mysql restart 

這個命令會使得容器停止，重新啟動就可以了。
接下來建立資料同步使用者：

 1 root@777fe9ce7f9d:/etc/mysql# mysql -u root -p
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 2
 5 Server version: 5.7.35-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
16 Query OK, 0 rows affected (0.02 sec)
17 
18 mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; 
19 Query OK, 0 rows affected (0.00 sec)

這裡主要是要授予使用者 slave，REPLICATION SLAVE許可權和REPLICATION CLIENT許可權，用於同步資料。

配置從伺服器（Slave）

同主伺服器一樣的操作方式，先進入MySQL配置檔案

1 wengzhihua@B000000147796DS ~ % docker exec -it d4ba5e063deb /bin/bash
2 root@d4ba5e063deb:/# cd etc/mysql
3 root@d4ba5e063deb:/etc/mysql# vi my.cnf

然後配置從庫的資訊： 

 1 [mysqld]
 2 ## 設定server_id，一般設定為IP,注意要唯一，這邊我們使用從庫的對映埠，方便識別
 3 server_id=3308  
 4 ## 複製過濾：也就是指定哪個資料庫不用同步（mysql庫一般不同步）
 5 binlog-ignore-db=mysql  
 6 ## 開啟二進位制日誌功能，以備Slave作為其它Slave的Master時使用
 7 log-bin=test-mysql-slave1-bin  
 8 ## 為每個session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取
 9 binlog_cache_size=1M  
10 ## 主從複製的格式（mixed,statement,row，預設格式是statement）
11 binlog_format=mixed  
12 ## 二進位制日誌自動刪除/過期的天數。預設值為0，表示不自動刪除。
13 expire_logs_days=7  
14 ## 跳過主從複製中遇到的所有錯誤或指定型別的錯誤，避免slave端複製中斷。
15 ## 如：1062錯誤是指一些主鍵重複，1032錯誤是因為主從資料庫資料不一致
16 slave_skip_errors=1062  
17 ## relay_log配置中繼日誌
18 relay_log=edu-mysql-relay-bin  
19 ## log_slave_updates表示slave將複製事件寫進自己的二進位制日誌
20 log_slave_updates=1  
21 ## 防止改變資料(除了特殊的執行緒)
22 read_only=1

配置完成之後重啟服務  service mysql restart  ：

1 root@d4ba5e063deb:/etc/mysql# service mysql restart
2 [info] Stopping MySQL Community Server 5.7.35.
3 ...
4 [info] MySQL Community Server 5.7.35 is stopped.
5 [info] Re-starting MySQL Community Server 5.7.35. 

跟上面一樣，這個命令會使得容器停止，重新啟動就可以了。 

完成Master和Slave的連線

注意，需要保證 Master 和 Slave 除了不同步的資料庫，其他資料庫的資料要一致。
1、在 Master 進入 MySQL， 然後執行命令：

1 mysql> show master status;
2 +----------------------+----------+--------------+------------------+-------------------+
3 | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4 +----------------------+----------+--------------+------------------+-------------------+
5 | test-mysql-bin.000001 |      617 |              | mysql            |                   |
6 +----------------------+----------+--------------+------------------+-------------------+
7 1 row in set (0.00 sec)

記錄下 File 和 Position 欄位的值，後面會用到。

2、然後再查詢一下主從兩個容器所對應的IP，主庫對應 172.17.0.2，從庫對應172.17.0.3：

1 wengzhihua@B000000147796DS ~ % docker ps                             
2 CONTAINER ID   IMAGE       COMMAND                  CREATED       STATUS          PORTS                                                  NAMES
3 d4ba5e063deb   mysql:5.7   "docker-entrypoint.s…"   5 hours ago   Up 19 minutes   33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp   slave
4 777fe9ce7f9d   mysql:5.7   "docker-entrypoint.s…"   5 hours ago   Up 51 minutes   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   master
5 wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' 777fe9ce7f9d
6 172.17.0.2
7 wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' d4ba5e063deb
8 172.17.0.3

3、然後到 Slave 中進入 mysql，執行命令：

1 mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='test-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;  
2 Query OK, 0 rows affected, 2 warnings (0.02 sec)

大意就是在從庫使用什麼賬號（slave）進行同步，同步的主庫的IP（master_host）、密碼（master_password）、埠（master_port）、binlog日誌檔案（master_log_file）以及其實同步的位置（master_log_pos）等，

理解下這個命令的各個引數：

1 master_host: Master 的IP地址
2 master_user: 在 Master 中授權的用於資料同步的使用者，就我們之前在主庫容器裡建立的哪個slave使用者
3 master_password: 同步資料的使用者的密碼
4 master_port: Master 的資料庫的埠號，注意，這邊是3306，不是3307，3307是對映外部宿主主機的，寫成3307，會造成 Slave_IO_Running 一直是 Connecting 狀態，在這邊踩坑了 
5 master_log_file: 指定 Slave 從哪個日誌檔案開始複製資料，即上文中提到的 File 欄位的值
6 master_log_pos: 從哪個 Position 開始讀，即上文中提到的 Position 欄位的值
7 master_connect_retry: 當重新建立主從連線時，如果連線失敗，重試的時間間隔，單位是秒，預設是60秒。 

在 Slave 的 MySQL 終端執行檢視主從同步狀態

 1 mysql> show slave status \G;
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: 172.17.0.2
 5                   Master_User: slave
 6                   Master_Port: 3307
 7                 Connect_Retry: 30
 8               Master_Log_File: edu-mysql-bin.000001
 9           Read_Master_Log_Pos: 617
10                Relay_Log_File: edu-mysql-relay-bin.000001
11                 Relay_Log_Pos: 4
12         Relay_Master_Log_File: edu-mysql-bin.000001
13              Slave_IO_Running: No
14             Slave_SQL_Running: No
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 617
25               Relay_Log_Space: 154
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No 
SlaveIORunning 和 SlaveSQLRunning 是No，表明 Slave 還沒有開始複製過程。相反 SlaveIORunning 和 SlaveSQLRunning 是Yes表明已經開始工作了。
執行一下命令，開始啟動主從同步,   Slave_IO_Running: Connecting, Slave_SQL_Running: Yes 。
1 start slave; 
 1 mysql> show slave status \G;
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 172.17.0.2
 5                   Master_User: slave
 6                   Master_Port: 3306
 7                 Connect_Retry: 30
 8               Master_Log_File: edu-mysql-bin.000001
 9           Read_Master_Log_Pos: 2351
10                Relay_Log_File: edu-mysql-relay-bin.000006
11                 Relay_Log_Pos: 1259
12         Relay_Master_Log_File: edu-mysql-bin.000001
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 2351
25               Relay_Log_Space: 1640
26               Until_Condition: None 

驗證同步是否成功：

1、我們在主服務這邊建立一個test庫，庫下面建立了一個person表，並增加了一條資料。

 

2、轉到從庫這邊，馬上就查詢到資料了 

主從同步延遲解決方案

最近部門瘋狂招人，面試Java程式設計師的時候在資料庫部分問的最頻繁的問題就是這個，問題不難，不過很多候選人同學沒辦法比較完整的回答，跟今天的標題有點相關，我就蠻放上來。

按照我們這邊的要求，主從庫同步應該是近實時的，極端情況下也不應該超過8s，如果超過，我們認為是有問題的。

1、保證資料庫處在最有狀態下：優化系統配置（連結層或者儲存引擎層）：最大連線數、允許錯誤數、允許超時時間、pool_size、log_size，保證記憶體、CPU、儲存空間的擴容（硬體部分）。
2、業務量不多的情況下，不做讀寫分離。既然主從延遲是由於從庫同步寫庫不及時引起的，那我們也可以在有主從延遲的地方改變讀庫方式，由原來的讀從庫改為讀主庫。當然這也會增加程式碼的一些邏輯複雜性。（部分業務讀主庫）
3、假如你的業務時間允許，你可以在寫入主庫的時候，確保資料都同步到從庫了之後才返回這條資料寫入成功，當然如果有多個從庫，你也必須確保每個從庫都寫入成功。顯然，這個方案對效能和時間的消耗是極大的，不推薦。
4、可以引入redis或者其他nosql資料庫來儲存我們經常會產生主從延遲的業務資料。當我在寫入資料庫的同時，我再寫入一份到redis中。我們可以先去檢視redis中是否有這個資料，如果有我們就可以直接從redis中讀取這個資料。當資料真正同步到資料庫中的時候，再從redis中把資料刪除。
5、任何的伺服器都是有吞吐量的限制的，沒有任何一個方案可以無限制的承載使用者的大量流量。所以我們必須估算好我們的伺服器能夠承載的流量上限是多少。達到這個上限之後，就要採取快取，限流，降級的方式來應對我們的流量。這也是應對主從延遲的根本處理辦法。
6、如果系統流量確實龐大，單純的讀寫分離已經無法解決問題了，那麼就應該對資料庫進一步治理，垂直分割槽和水平分割槽是不錯的方式，下一章我們會詳細說說。

相關文章