MySQL-18.主從複製

长名06發表於2024-07-09
MySql

C-18.主從複製

1.主從複製概述

1.1 如何提升資料庫併發能力

在實際工作中，我們常常將Redis作為快取與MySQL配合來使用，當有請求的時候，首先會從快取中進行查詢，如果存在就直接取出。如果不存在再方法資料庫，這樣就提升了讀取的效率，也減少了對後端資料庫的訪問壓力。Redis的快取架構是高併發架構中非常重要的一環。

此外，一般應用對資料庫而言都是“讀多寫少”，也就說對資料庫讀取資料的壓力比較大，有一個思路就是採用資料庫叢集的方案，做主從架構、進行讀寫分離，這樣同樣可以提升資料庫的併發處理能力。但並不是所有的應用都需要對資料庫進行主從架構的設定，畢竟設定架構本身是有成本的。

如果我們的目的在於提升資料庫高併發訪問的效率，那麼首先考慮的是如何最佳化SQL和索引，這種方式簡單有效；其次才是採用快取的策略，比如使用Redis將熱點資料儲存在記憶體資料庫中，提升讀取的效率最後才是對資料庫採用主從架構，進行讀寫分離。

按照上面的方式進行最佳化，使用和維護的成本是由低到高的。

1.2 主從複製的作用

主從同步設計不僅可以提高資料庫的吞吐量，還有以下3個方面的作用。

第1個作用：讀寫分離。我們可以透過主從複製的方式來同步資料，然後透過讀寫分離提高資料庫併發處理能力。

其中一個是Master主庫，負責寫入資料，我們稱之為:寫庫。

其它都是slave從庫，負責讀取資料，我們稱之為:讀庫。

當主庫進行更新的時候，會自動將資料複製到從庫中，而我們在客戶端讀取資料的時候，會從從庫中進行讀取。

面對“讀多寫少”的需求，採用讀寫分離的方式，可以實現更高的併發訪問。同時，我們還能對從伺服器進行負載均衡，讓不同的讀請求按照策略均勻地分發到不同的從伺服器上，讓讀取更加順暢。讀取順暢的另一個原因，就是減少了鎖表的影響，比如我們讓主庫負責寫，當主庫出現寫鎖的時候，不會影響到從庫進行SELECT的讀取。

第2個作用就是資料備份。我們透過主從複製將主庫上的資料複製到了從庫上，相當於是一種熱備份機制，也就是在主庫正常執行的情況下進行的備份，不會影響到服務。

第3個作用是具有高可用性。資料備份實際上是一種冗餘的機制，透過這種冗餘的方式可以換取資料庫的高可用性，也就是當伺服器出現故障或當機的情況下，可以切換到從伺服器上，保證服務的正常執行。

關於高可用性的程度，我們可以用一個指標衡量，即正常可用時間/全年時間。比如要達到全年99.999%的時間都可用，就意味著系統在一年中的不可用時間不得超過365*24*60+ (1-99.999%)=5.256分鐘（含系統崩潰的時間、日常維護操作導致的停機時間等)，其他時間都需要保持可用的狀態。

實際上，更高的高可用性，意味著需要付出更高的成本代價。在現實中我們需要結合業務需求和成本來進行選擇。

2.主從複製的原理

Slave會從Master讀取binlog來進行資料同步。

2.1 原理剖析

實際上主從同步的原理就是基於binlog 進行資料同步的。在主從複製過程中，會基於3個執行緒來操作，一個主庫執行緒，兩個從庫執行緒。


二進位制日誌轉儲執行緒（Binlog dump thread）是一個主庫執行緒。當從庫執行緒連線的時候，主庫可以將二進位制日誌傳送給從庫，當主庫讀取事件（Event）的時候，會在Binlog上加鎖，讀取完成之後，再將鎖釋放掉。

從庫I/O執行緒會連線到主庫，向主庫傳送請求更新Binlog。這時從庫的I/O執行緒就可以讀取到主庫的二進位制日誌轉儲執行緒，傳送的Binlog 更新部分，並且複製到本地的中繼日誌(Relay log)。

從庫SQL執行緒會讀取從庫中的中繼日誌，並且執行日誌中的事件，將從庫中的資料與主庫保持同步。

注意：

不是所有版本的MySQL都預設開啟伺服器的二進位制日誌。在進行主從同步的時候，我們需要先檢查伺服器是否已經開啟了二進位制日誌。

除非特殊指定，預設情況下從伺服器會執行所有主伺服器中儲存的事件。也可以透過配置，使從伺服器執行特定的事件。

複製三步驟

步驟1: Master將寫操作記錄到二進位制日誌（binlog)。這些記錄叫做二進位制日誌事件(binary log events);

步驟2: Slave將Master的binary log everkts複製到它的中繼日誌(relay log) ;

步驟3: Slave重做中繼日誌中的事件，將改變應用到自己的資料庫中。MySQL複製是非同步的且序列化的，而且重啟後從接入點開始複製。

複製的問題

複製的最大問題：延時。

2.2 複製的基本原則

  • 每個Slave只能有一個Master。
  • 每個Slave只能有一個唯一的伺服器ID。
  • 每個Master可以有多個Slave。

3.一主一從架構搭建

一臺主機用於處理所有寫請求，一臺從機負責所有讀請求，架構圖如下：

3.1 準備工作

1、準備2臺CentOS虛擬機器

2、每臺虛擬機器上需要安裝好MySQL(可以是8.0.x)

說明：前面我們講過如何克隆一臺CentOS。大家可以在一臺CentOS上安裝好MySQL，進而透過克隆的方式複製出1臺包含MySQL的虛擬機器。

注意：克隆的方式需要修改新克隆出來主機的：① MAC地址 ② hostname ③ IP 地址 ④ UUID 。

此外，克隆的方式生成的虛擬機器（包含MySQL Server），則克隆的虛擬機器MySQL Server的UUID相同，必須修改，否則在有些場景會報錯。比如： show slave status\G ，報如下的錯誤：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

修改MySQL Server 的UUID方式：

vim /var/lib/mysql/auto.cnf#修改uuid

systemctl restart mysqld#重新啟動MySQL服務

3.2 主機配置檔案

建議mysql版本一致且後臺以服務執行，主從所有配置項都配置在[mysqld]節點下，且都是小寫字母。

具體引數配置如下：

  • 必選
#[必須]主伺服器唯一ID
server-id=1
#[必須]啟動二進位制日誌，指明路徑，比如 '/var/lib/mysql/'
log-bin=binlog
  • 可選
#[可選] 0（預設）表示讀寫（主機），1表示只讀（從機）
read-only=0

#設定日誌檔案保留的時長，單位是秒
binlog_expire_logs_seconds=6000

#控制單個二進位制日誌大小。此引數的最大和預設值是1GB
max_binlog_size=200M

#[可選]設定不要複製的資料庫
binlog-ignore-db=test

#[可選]設定需要複製的資料庫,預設全部記錄。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要複製的主資料庫名字

#[可選]設定binlog格式
binlog_format=STATEMENT

重啟後臺mysql服務，使配置生效

注意：

先建立完主從複製，再建立資料庫。

MySQL主從複製起始時，從機不繼承主機資料。

binlog格式設定：

①STATEMENT模式（基於SQL語句的複製(statement-based replication, SBR)）

binlog_format=STATEMENT

每一條會修改資料的sql語句會記錄到binlog中。這是預設的binlog格式。

  • SBR 的優點：
    • 歷史悠久，技術成熟
    • 不需要記錄每一行的變化，減少了binlog日誌量，檔案較小
    • binlog中包含了所有資料庫更改資訊，可以據此來稽核資料庫的安全等情況
    • binlog可以用於實時的還原，而不僅僅用於複製
    • 主從版本可以不一樣，從伺服器版本可以比主伺服器版本高
  • SBR 的缺點：
    • 不是所有的UPDATE語句都能被複制，尤其是包含不確定操作的時候
  • 使用以下函式的語句也無法被複制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
    • INSERT ... SELECT 會產生比 RBR 更多的行級鎖
    • 複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的 UPDATE 時，需要比 RBR 請求更多的行級鎖
    • 對於有 AUTO_INCREMENT 欄位的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
    • 對於一些複雜的語句，在從伺服器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響
    • 執行復雜語句如果出錯的話，會消耗更多資源
    • 資料表必須幾乎和主伺服器保持一致才行，否則可能會導致複製出錯

②ROW模式（基於行的複製(row-based replication，RBR)）

binlog_format=ROW

5.1.5版本的MySQL才開始支援，不記錄每條sql語句的上下文資訊，僅記錄哪條資料被修改了，修改成什麼樣了。

  • RBR 的優點：

    • 任何情況都可以被複制，這對複製來說是最安全可靠的。（比如：不會出現某些特定情況下的儲存過程、function、trigger的呼叫和觸發無法被正確複製的問題）

    • 多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多

    • 複製以下幾種語句時的行鎖更少：INSERT ... SELECT、包含 AUTO_INCREMENT 欄位的 INSERT、沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

    • 執行 INSERT，UPDATE，DELETE 語句時鎖更少

    • 從伺服器上採用多執行緒來執行復製成為可能

  • RBR 的缺點：

    • binlog 大了很多

    • 複雜的回滾時 binlog 中會包含大量的資料

    • 主伺服器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生 binlog 的併發寫問題

    • 無法從 binlog 中看到都複製了些什麼語句

③MIXED模式（混合模式複製(mixed-based replication，MBR)）

binlog_format=MIXED

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。

在Mixed模式下，一般的語句修改使用statment格式儲存binlog。如一些函式，statement無法完成主從複製的操作，則採用row格式儲存binlog。

MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種。

3.3 從機配置檔案

要求主從所有配置項都配置在my.cnf的[mysqld]欄位下，且都是小寫字母。

  • 必選
#[必須]從伺服器唯一ID
server-id=2
  • 可選
#[可選]啟用中繼日誌
relay-log=mysql-relay

重啟MySQL服務，使配置生效

注意：主從機都關閉防火牆

service iptables stop #CentOS 6

systemctl stop firewalld.service #CentOS 7

3.4 主機：建立賬戶並授權

#在主機MySQL裡執行授權主從複製的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'從機器資料庫IP' IDENTIFIED BY 'abc123';
#5.5,5.7

注意：如果使用的是MySQL8，需要如下的方式建立賬戶，並授權slave：

CREATE USER 'slave1'@'%' IDENTIFIED BY 'ming6688';

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

#此語句必須執行。否則見下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY 'ming6688';

flush privileges;

查詢Master的狀態，並記錄下File和Position的值。

show master status;

  • 記錄下File和Position的值

注意：執行完此步驟後不要再操作主伺服器MySQL，防止主伺服器狀態值變化。

3.5 從機：配置需要複製的主機

步驟1：從機上覆制主機的命令

CHANGE MASTER TO
MASTER_HOST='主機的IP地址',
MASTER_USER='主機使用者名稱',
MASTER_PASSWORD='主機使用者名稱的密碼',
MASTER_LOG_FILE='mysql-bin.具體數字',
MASTER_LOG_POS=具體值;

舉例：

CHANGE MASTER TO
MASTER_HOST='192.168.200.132',
MASTER_USER='slave1',
MASTER_PASSWORD='ming6688',
MASTER_LOG_FILE='binlog.000067',
MASTER_LOG_POS=2058;


步驟2：

#啟動slave同步
START SLAVE;

如果報錯：

可以執行如下操作，刪除之前的relay_log資訊。然後重新執行 CHANGE MASTER TO ...語句即可。

mysql> reset slave; #刪除SLAVE資料庫的relaylog日誌檔案，並重新啟用新的relaylog檔案

接著，檢視同步狀態

SHOW SLAVE STATUS\G;

上面兩個引數都是Yes，則說明主從配置成功！

顯示如下的情況，就是不正確的。可能錯誤的原因有

1. 網路不通
2. 賬戶密碼錯誤
3. 防火牆
4. mysql配置檔案問題
5. 連線伺服器時語法
6. 主伺服器mysql許可權

3.6 測試

主機新建庫、新建表、insert記錄，從機複製：

CREATE DATABASE test_master_slave;

CREATE TABLE mytbl(id INT,NAME VARCHAR(16));

INSERT INTO mytbl VALUES(1, 'zhang3');

INSERT INTO mytbl VALUES(2,@@hostname);

3.7 停止主從同步

  • 停止主從同步命令
stop slave;
  • 如何重新配置主從

如果停止從伺服器複製功能，再使用需要重新配置主從。否則會報錯如下：


重新配置主從，需要在從機上執行

stop slave;

reset master;#刪除Master中所有的binglog檔案，並將日誌索引檔案清空，重新開始所有新的日誌檔案(慎用)

3.8 後續

搭建主從複製：雙主雙從

4.同步資料一致性問題

主從同步的要求：

  • 讀庫和寫庫的資料一致(最終一致)；
  • 寫資料必須寫到寫庫；
  • 讀資料必須到讀庫(不一定)；

4.1 理解主從延遲問題

進行主從同步的內容是二進位制日誌，它是一個檔案，在進行網路傳輸的過程中就一定會存在主從延遲（比如 500ms），這樣就可能造成使用者在從庫上讀取的資料不是最新的資料，也就是主從同步中的資料不一致性問題。

4.2 主從延遲問題原因

在網路正常的時候，日誌從主庫傳給從庫所需的時間是很短的，即T2-T1的值是非常小的。即，網路正常情況下，主備延遲的主要來源是備庫接收完binlog和執行完這個事務之間的時間差。

主備延遲最直接的表現是，從庫消費中繼日誌（relay log）的速度，比主庫生產binlog的速度要慢。

造成原因：

1、從庫的機器效能比主庫要差

2、從庫的壓力大

3、大事務的執行

舉例1：一次性用delete語句刪除太多資料

結論：後續再刪除資料的時候，要控制每個事務刪除的資料量，分成多次刪除。

舉例2：一次性用insert...select插入太多資料

舉例3：大表DDL

比如在主庫對一張500W的表新增一個欄位耗費了10分鐘，那麼從節點上也會耗費10分鐘。

4.3 如何減少主從延遲

若想要減少主從延遲的時間，可以採取下面的辦法：

  1. 降低多執行緒大事務併發的機率，最佳化業務邏輯
  2. 最佳化SQL，避免慢SQL，減少批次操作，建議寫指令碼以update-sleep這樣的形式完成。
  3. 提高從庫機器的配置，減少主庫寫binlog和從庫讀binlog的效率差。
  4. 儘量採用短的鏈路，也就是主庫和從庫伺服器的距離儘量要短，提升埠頻寬，減少binlog傳輸的網路延時。
  5. 實時性要求的業務讀強制走主庫，從庫只做災備，備份

4.4 如何解決一致性問題

如果操作的資料儲存在同一個資料庫中，那麼對資料進行更新的時候，可以對記錄加寫鎖，這樣在讀取的時候就不會發生資料不一致的情況。但這時從庫的作用就是備份，並沒有起到讀寫分離，分擔主庫讀壓力的作用。

讀寫分離情況下，解決主從同步中資料不一致的問題， 就是解決主從之間資料複製方式的問題，如果按照資料一致性從弱到強來進行劃分，有以下 3 種複製方式。

方法1：非同步複製

非同步模式就是客戶端提交COMMIT之後不需要等從庫返回任何結果，而是直接將結果返回給客戶端，這樣做的好處是不會影響主庫寫的效率，但可能會存在主庫當機，而Binlog還沒有同步到從庫的情況，也就是此時的主庫和從庫資料不一致。這時候從從庫中選擇一個作為新主，那麼新主則可能缺少原來主伺服器中已提交的事務。所以，這種複製模式下的資料一致性是最弱的。

方法2：半同步複製

MySQL5.5版本之後開始支援半同步複製的方式。原理是在客戶端提交COMMIT之後不直接將結果返回給客戶端，而是等待至少有一個從庫接收到了Binlog，並且寫入到中繼日誌中，再返回給客戶端。

這樣做的好處就是提高了資料的一致性，當然相比於非同步複製來說，至少多增加了一個網路連線的延遲，降低了主庫寫的效率。

在MySQL5.7版本中還增加了一個rpl_semi_sync_master_wait_for_slave_count引數，可以對應答的從庫數量進行設定，預設為1，也就是說只要有1個從庫進行了響應，就可以返回給客戶端。如果將這個引數調大，可以提升資料一致性的強度，但也會增加主庫等待從庫響應的時間。

方法3：組複製

非同步複製和半同步複製都無法最終保證資料的一致性問題，半同步複製是透過判斷從庫響應的個數來決定是否返回給客戶端，雖然資料一致性相比於非同步複製有提升，但仍然無法滿足對資料一致性要求高的場景，比如金融領域。MGR 很好地彌補了這兩種複製模式的不足。

組複製技術，簡稱 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一種新的資料複製技術，這種複製技術是基於 Paxos 協議的狀態機複製。

MGR 是如何工作的

首先我們將多個節點共同組成一個複製組，在執行讀寫（RW）事務的時候，需要透過一致性協議層（Consensus 層）的同意，也就是讀寫事務想要進行提交，必須要經過組裡“大多數人”（對應 Node 節點）的同意，大多數指的是同意的節點數量需要大於 （N/2+1），這樣才可以進行提交，而不是原發起方一個說了算。而針對只讀（RO）事務則不需要經過組內同意，直接 COMMIT 即可。

在一個複製組內有多個節點組成，它們各自維護了自己的資料副本，並且在一致性協議層實現了原子訊息和全域性有序訊息，從而保證組內資料的一致性。

MGR 將 MySQL 帶入了資料強一致性的時代，是一個劃時代的創新，其中一個重要的原因就是MGR 是基於 Paxos 協議的。Paxos 演算法是由 2013 年的圖靈獎獲得者 Leslie Lamport 於 1990 年提出的，有關這個演算法的決策機制可以搜一下。事實上，Paxos 演算法提出來之後就作為分散式一致性演算法被廣泛應用，比如Apache 的 ZooKeeper 也是基於 Paxos 實現的。

5.知識延伸

在主從架構的配置中，如果想要採取讀寫分離的策略，我們可以 自己編寫程式 ，也可以透過第三方的中介軟體來實現。

  • 自己編寫程式的好處就在於比較自主，我們可以自己判斷哪些查詢在從庫上來執行，針對實時性要求高的需求，我們還可以考慮哪些查詢可以在主庫上執行。同時，程式直接連線資料庫，減少了中介軟體層，相當於減少了效能損耗。
  • 採用中介軟體的方法有很明顯的優勢，功能強大，使用簡單。但因為在客戶端和資料庫之間增加了中介軟體層會有一些效能損耗，同時商業中介軟體也是有使用成本的。我們也可以考慮採取一些優秀的開源工具。

    ① Cobar屬於阿里B2B事業群，始於2008年，在阿里服役3年多，接管3000+個MySQL資料庫的schema,叢集日處理線上SQL請求50億次以上。由於Cobar發起人的離職，Cobar停止維護。

② Mycat是開源社群在阿里cobar基礎上進行二次開發，解決了cobar存在的問題，並且加入了許多新的功能在其中。青出於藍而勝於藍。

③ OneProxy基於MySQL官方的proxy思想利用c語言進行開發的，OneProxy是一款商業收費的中介軟體。捨棄了一些功能，專注在效能和穩定性上。

④ kingshard由小團隊用go語言開發，還需要發展，需要不斷完善。

⑤ Vitess是Youtube生產在使用，架構很複雜。不支援MySQL原生協議，使用`需要大量改造成本 。

⑥ Atlas是360團隊基於mysql proxy改寫，功能還需完善，高併發下不穩定。

⑦ MaxScale是mariadb（MySQL原作者維護的一個版本） 研發的中介軟體

⑧ MySQLRoute是MySQL官方Oracle公司釋出的中介軟體

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章