這一次終於有人把MySQL主從複製講全面了！！！

奕鵬發表於2021-05-12

[TOC]

文章簡介

網路上關於 MySQL 主從複製的文章很多都是講解如何實現，以及部分實現原理，缺乏對 MySQL 主從複製的全面介紹。例如主從複製的模式(半同步模式和非同步同步模式)、同步的原理(binary log+position，GTID)、主從複製的常見問題都缺乏一個全面的總結。

本文針對這些部分內容做一個全面的分析與總結。本文主要的內容有 MySQL主從複製的原因、實現原理、實現步驟、半同步模式、非同步同步模式、GTID 同步、常見問題與解決方案等內容。

Snipaste_2021-05-11_15-30-41

模式優勢

在瞭解主從複製之前，我們先了解一下什麼是主從複製。說的簡單一點就是將一臺 MySQL 伺服器的資料庫檔案同步到其他的 MySQL 服務上，使得被同步的 MySQL 服務也能讀取到我們的資料。

為什麼會有主從複製呢？這裡我總結了兩點原因：

資料容災、備份。當我們的資料庫只使用一臺服務時，如果我們的資料庫遭到破壞，例如黑客的攻擊、人為操作的失誤等等情況。這時候我們就可以在一定程度上保障我們資料庫的恢復。

如果只為為了防止資料庫的丟失，我們可以針對資料進行定時備份，為何還要搞什麼主從複製，這樣不是更麻煩嘛。如果我們只是定時備份資料庫，可以試想一下，萬一在某個備份操作還未執行的階段，資料庫出現問題，中間的這一部分資料不就沒法恢復了嘛。

緩解 MySQL 主服務的壓力。當我們線上應用使用者量小的時候，所有的讀與寫操作都在一臺伺服器上，這時候還不會遇到什麼問題。當使用者量逐漸增加，訪問資料庫的請求也越來越多，這時候就給 MySQL 伺服器增加了負擔，容易導致服務崩潰等問題。因此，主從複製模式可以緩解單伺服器的壓力，將寫操作給主伺服器，讀操作給從伺服器，從伺服器可以部署多臺，分攤壓力。因為在一個應用中，讀的操作肯定是大於寫的操作。

Snipaste_2021-05-11_15-48-07

實現原理

下圖是 MySQL 主從複製的一個原理圖：

Snipaste_2021-05-11_15-50-17

master 伺服器會將 SQL 記錄通過多 dump 執行緒寫入到 binary log 中。
slave 伺服器開啟一個 io thread 執行緒向伺服器傳送請求，向 master 伺服器請求 binary log。master 伺服器在接收到請求之後，根據偏移量將新的 binary log 傳送給 slave 伺服器。
slave 伺服器收到新的 binary log 之後，寫入到自身的 relay log 中，這就是所謂的中繼日誌。
slave 伺服器，單獨開啟一個 sql thread 讀取 relay log 之後，寫入到自身資料中。

常見模式

常見的主從模式有如下幾種，具體的模式也得看實際的業務需要。根據實際的情況，選擇合適的一種架構模式。

一主一從模式。

Snipaste_2021-05-11_16-12-00

一主多從模式。

Snipaste_2021-05-11_16-12-19

級聯主從模式。

Snipaste_2021-05-11_16-12-29

一主多從模式。

Snipaste_2021-05-11_16-12-43

配置流程

在本文演示中，採用一主一從的架構模式。

角色	IP 地址	埠號	server-id
master	192.168.0.112	3304	1
slave	192.168.0.112	3305	2

要開啟主從複製，首先要遵循下面幾種條件。

master 和 server 都要有自身的 server-id，並且每一個 server-id 不能相同。
master 開啟 log_bin 選項。推薦將從伺服器的該選項也開啟。
master 開啟 binlog_format=row 選項。推薦從伺服器也開啟該選項，並且 log_slave_updates 也逐步開啟，後期如果 slave 升級為 master 也方便擴充套件。

master 操作

將下面的一段配置新增到 master 配置檔案中，重啟服務讓配置生效。

server_id               = 1
log_bin                 = mysql-bin
binlog_format           = ROW

接下來登入到 master 命令列操作介面，建立一個主從複製的賬號。這裡建立一個賬號為 slave_user，密碼為 123456 的賬號。

grant replication slave on *.* to 'slave_user'@'%' identified by '123456';
flush privileges;

檢視 master 的 binary log 檔案和 postion。記錄下來待會在 slave 上設定主節點資訊時需要使用。

mysql root@127.0.0.1:(none)> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000062 | 728      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set
Time: 0.011s

slave 操作

將下面的配置項新增到 slave 配置檔案中，重啟服務讓配置生效。

server_id               = 2
log_bin                 = mysql-bin
binlog_format           = ROW
log_slave_updates        = ON
read_only                = ON
super_read_only            = ON

接下來登入到 slave 命令列操作介面，設定主節點資訊。

change master to master_host='192.168.0.112',master_port=3304,master_user='slave_user',master_password='123456',master_log_file='mysql-bin.000062',master_log_pos=728;
start slave;
stop slave;

master_host：master 的 IP 地址。
master_port：master 的服務埠。
master_user：master 建立的主從複製使用者。
master_password：master 建立的主從複製使用者密碼。
master_log_file：master 的 binary log 檔案(上面在 master 上執行 show master status獲取到的 File 值)。
master_log_pos：master 的複製偏移量(上面在 master 上執行 show master status獲取到的 Position 值)。

配置主節點的資訊之後，還不能進行主從複製，還需要我們開啟主從複製。

開啟主從複製

start slave;

斷開主從複製

stop slave;

重置主從複製資訊

reset slave all;

效果演示

在配置之前，在 master 上實現有這樣一張表，表結構如下：

CREATE TABLE `mysql_test`.`master_slave_demo`  (
  `id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

在配置主從複製之前，我們在從伺服器上也同樣建立一張這樣的表，庫名和表名與 master 保持一致。

假設你都做好了這些工作，此時我們在 master 上 insert 一條資料，然後去 slave 檢視是否有新的資料。

mysql root@127.0.0.1:mysql_test> insert into `master_slave_demo` (`name`) value ('FFFFFF');
Query OK, 1 row affected
Time: 0.016s
mysql root@127.0.0.1:mysql_test> select * from `master_slave_demo`;
+----+--------+
| id | name   |
+----+--------+
| 1  | 張三   |
| 2  | 李四   |
| 3  | 王五   |
| 4  | 趙六   |
| 5  | AA     |
| 6  | BB     |
| 7  | CC     |
| 8  | DD     |
| 9  | EE     |
| 10 | FFFFFF |
+----+--------+
10 rows in set
Time: 0.011s

此時登入到 slave 檢視是否有新的資料。

mysql root@127.0.0.1:mysql_test> select * from master_slave_demo;
+----+--------+
| id | name   |
+----+--------+
| 1  | 張三   |
| 2  | 李四   |
| 3  | 王五   |
| 4  | 趙六   |
| 7  | CC     |
| 8  | DD     |
| 9  | EE     |
| 10 | FFFFFF |
+----+--------+
8 rows in set
Time: 0.013s

此時發現我們的資料已經自動同步過來了，到此我們的主從複製也配置完成了。

對於主從複製的資料庫表不推薦使用該方式，在實際的過程中會遇到各種問題。這裡只是為了文章演示採用的一種模式，後面針對 MySQL 的資料備份會單獨總結一下如何正確的去操作。

同步模式

上面總結了同步的原理、同步的配置流程和同步的實際效果，下面針對主從複製的同步模式進行深一步的探索。為什麼會有不同的同步模式呢？這肯定是因為某種模式存在缺陷，預設的同步模式使用的是非同步同步模式，在下面的示例中，我們就不在演示了，直接演示半同步模式。

同步模式分類

同步模式主要分為非同步同步和半同步模式(還有一種 GTID 模式，就單獨做講解，因為它不是基於這種 binary + Log 的簡單形式)，兩者實現的方式如下圖：

Snipaste_2021-05-11_19-19-03

非同步同步模式

非同步同步模式是 MySQL 預設的同步策略模式。客戶端在向服務端傳送請求後，master 處理完之後，直接返回客戶端結果，接著在將對應的 log 資訊傳送給 slave 節點。

上面的演示步驟就是屬於非同步同步模式，因此這裡不做再次演示。

半同步模式

半同步模式與非同步同步的模式最大的區別在於

master 處理完自身操作，將對應的 binary log 傳送給從伺服器，從伺服器通過 io thread 寫入到 relay log 中，然後將結果返回給 master，master 在收到 salve 的響應之後在返回給客戶端。
半同步模式也是基於非同步複製的基礎上進行的，無非是半同步模式需要安裝非同步外掛完成。

半同步模式具體操作流程：

Snipaste_2021-05-11_19-31-38

半同步實現流程

檢測是否支援動態安裝外掛模式。

mysql root@127.0.0.1:(none)> select @@have_dynamic_loading
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set
Time: 0.016s

在 master 上安裝 master 對應的外掛，也不一定只安裝 master 對應的外掛，slave 的外掛也可以安裝，後期服務的升級和降級也可以直接使用。

mysql root@127.0.0.1:(none)> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected
Time: 0.015s
mysql root@127.0.0.1:(none)> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set
Time: 0.015s

在 slave 安裝 slave 對應的外掛，同理也可以安裝 master 對應的外掛。

mysql root@127.0.0.1:(none)> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected
Time: 0.006s
mysql root@127.0.0.1:(none)> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set
Time: 0.012s

master 伺服器和 slave 伺服器都開啟主從複製外掛功能。

mysql root@127.0.0.1:(none)> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected
Time: 0.004s
mysql root@127.0.0.1:(none)> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected
Time: 0.002s
mysql root@127.0.0.1:(none)> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set
Time: 0.013s

salve 節點半同步複製模式。

stop slave io_thread;
start slave io_thread;

在該步驟中，省略了配置主從關係的一步，因為上面在演示主從複製時，已經建立了一個主從複製關係了並且半同步模式也是基於非同步同步模式進行的，所以你只需按照上面主從複製操作的流程進行即可。

在 master 上檢視 slave 資訊。

mysql root@127.0.0.1:(none)> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_status                | ON    |
+--------------------------------------------+-------+
15 rows in set
Time: 0.011s

這裡我們看到 Rpl_semi_sync_master_clients=1，則表示有一個 salve 節點連線上了。

檢測結果

通過上面的配置，半同步模式已經完成了。你可以直接在 master 上運算元據，slave 節點就能正常同步資料了。

半同步問題總結

slave 節點響應 master 延遲。

當 master 傳送給 slave 節點 binary log 之後，需要等待 slave 的響應。有時可能 slave 節點響應很慢，master 不能一直等待，這樣會導致客戶端請求超時情況，可以通過下面的引數進行設定。
該引數的單位是毫秒，預設是 10 秒，推薦設定大一點。因為超時之後，master 會自動切換為非同步複製。

rpl_semi_sync_master_timeout

半同步模式自動轉為非同步同步模式。

當面 1 中提到了，如果超時之後，半同步模式會自動切換為非同步複製模式。因此設定該引數即可。

master 接收 slave 節點數量，響應客戶端。

當 master 需要將 binary log 傳送給多個 slave 節點時，如果 slave 節點存在多個，master 都要等待 slave 一一響應之後才回復客戶端，這也是一個特別耗時的過程，可以通過下面的引數進行設定。
該引數的意義就是，只要 master 接收到 n 個 slave 的響應之後，就可以返回給客戶端了。預設是 1。

rpl_semi_sync_master_wait_for_slave_count

當半同步模式自動切換為非同步之後，如何切換為半同步模式。

這時候需要手動切換模式。就是關閉 io_thread，再開啟 io_thread。

start slave io_thread;

半同步一致性

半同步複製模式極大程度上提高了主從複製的一致性。同時在 MySQL5.7+的版本增加了另外一個引數，讓複製的一致性更加可靠。這個引數就是rpl_semi_sync_master_wait_point，需要在 master 上執行。

set global rpl_semi_sync_master_wait_point = 'x';

該引數有兩個值。一個值是 AFTER_SYNC，一個值是 AFTER_COMMIT。預設是 AFTER_SYNC。

AFTER_SYNC

master 在將事務寫入 binary log 之後，然後傳送給 slave。同時也會自動提交 master 的事務。等 slave 響應之後，master 接著響應給客戶端資訊。

AFTER_SYNC

master 在將事務寫入 binary log 之後，然後傳送給 slave。等待 slave 響應之後，才會提交 master 的事務，接著響應給客戶端資訊。

兩者對比

非同步同步模式，是直接返回給客戶端在處理 slave 的問題，如果 master 響應給客戶端成功資訊，在處理 slave 問題時，服務掛掉了，此時就會出現資料不一致。
半同步模式，需要等待 slave 節點做出響應，master 才會響應客戶端，如果 salve 響應較慢就會造成客戶端等待時間較長。
半同步模式，master 等待 slave 響應之後才會響應給客戶端，此方式極大程度的保證了資料的一致性，為主從複製的資料一致性提供了更可靠的保證。也推薦使用該方式進行主從複製操作。

GTID同步

什麼是GTID同步

GTID是一種全域性事務ID，它是在master上已經提交的事務，slave直接根據該ID進行復制操作。該操作替代了binary log + postion的方式。使得主從複製的配置操作更加簡單。

該模式需要MySQL>=5.6版本。

GTID組成部分

GTID = server-id + transaction-id組成。server-id不是MySQL配置檔案中id，而是每一個MySQL服務在啟動時，都會生成一個全域性隨機唯一的ID。transaction-id則是事務的ID，建立事務是會自動生成一個ID。

配置流程

master的配置檔案增加如下配置。

server_id               = 1
log_bin                 = ON
binlog_format           = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON

slave的配置檔案增加如下配置。

server_id               = 2
log_bin                 = mysql-bin
binlog_format           = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON
log_slave_updates        = ON

配置好之後，一定記得重啟master和salve服務。重啟好之後，登入master，使用show master status;檢視一下GTID。會看到如下的資訊。

mysql root@127.0.0.1:(none)> show master status;
+-----------+----------+--------------+------------------+------------------------------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-----------+----------+--------------+------------------+------------------------------------------+
| ON.000005 | 729      |              |                  | a9cf78c4-257f-11eb-94e0-0242ac120007:1-2 |
+-----------+----------+--------------+------------------+------------------------------------------+
1 row in set
Time: 0.011s

slave服務建立連線關係。下面的操作都是在slave節點進行。

# 重置所有的複製關係。
mysql root@127.0.0.1:(none)> reset slave all;
Query OK, 0 rows affected
Time: 0.056s


# 檢視主從複製狀態，發現沒有任何資訊了，則表示重置成功了。
mysql root@127.0.0.1:(none)> show slave status\G;
0 rows in set
Time: 0.005s


# 設定master資訊。
change master to master_host='192.168.0.112',master_port=3304,master_user='slave_user',master_password='123456',master_auto_position=1;
Query OK, 0 rows affected
Time: 0.048s


# 啟動複製。
start slave;
mysql root@127.0.0.1:(none)> start slave;
Query OK, 0 rows affected
Time: 0.007s


# 檢視複製狀態。
mysql root@127.0.0.1:(none)> stop slave io_thread;
***************************[ 1. row ]***************************
Slave_IO_State                | Waiting for master to send event
Master_Host                   | 192.168.0.112
Master_User                   | slave_user
Master_Port                   | 3304
Connect_Retry                 | 60
Master_Log_File               | ON.000005
Read_Master_Log_Pos           | 729
Relay_Log_File                | aa7863c59748-relay-bin.000002
Relay_Log_Pos                 | 928
Relay_Master_Log_File         | ON.000005
Slave_IO_Running              | Yes
Slave_SQL_Running             | Yes
Replicate_Do_DB               |
..........

需要測試結果，可以直接在master插入資料，看slave資料是否已經發生變化。

文章總結

通過上面的演示，你已經基本掌握了主從複製的原理、配置流程。針對主從複製還有很多細節，由於篇幅問題後期會持續更新。

專注於PHP、MySQL、Linux和前端開發，感興趣的感謝點個關注喲！！！文章整理在GitHub,主要包含的技術有PHP、Redis、MySQL、JavaScript、HTML&CSS、Linux、Java、Golang、Linux和工具資源等相關理論知識、面試題和實戰內容。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

喜歡的，可以關注公眾號"卡二條的技術圈"。

終於有人把MYSQL索引講清楚了
2020-09-15
MySql索引
瞧！終於有人把智慧製造與工業4.0講明白了
2022-04-28
終於有人把網路爬蟲講明白了
2019-04-10
爬蟲
終於有人把隱私計算講明白了
2022-05-07
麻了，一個操作把MySQL主從複製整崩了
2023-05-10
MySql
終於有人把工業資料採集講明白了
2022-05-10
終於有人把Web 3.0和元宇宙講明白了
2022-05-07
Web元宇宙
終於有人把能把資料採集給講明白了
2022-04-28
mysql的主從複製原理講解
2020-11-10
MySql
終於有人把BungeeCord群組服搭建教程方法講明白了
2022-04-28
終於有人把ERP和OA的區別講清楚了！
2024-11-21
MySQL主從複製與主主複製
2017-03-01
MySql
終於有人把雲端計算、大資料和 AI 講明白了
2018-11-21
大資料AI
終於有人把安全知識圖譜技術講明白了（上篇）
2022-08-31
MySQL全面瓦解27：主從複製（原理 + 實踐）
2021-08-17
MySql
MySQL的主從複製與MySQL的主主複製
2017-11-16
MySql
mysql5.7主從複製，主主複製
2018-08-21
MySql
MySQL主從複製、半同步複製和主主複製
2016-02-22
MySql
mysql複製--主從複製配置
2018-04-12
MySql
MySQL 主從複製
2008-04-01
MySql
【MySql】主從複製
2011-12-17
MySql
MySQL主從複製
2024-11-11
MySql
Mysql 基於GTID主從複製
2020-12-25
MySql
MySQL主從複製、半同步複製和主主複製概述
2016-03-14
MySql
MySQL主從複製的原理和注意事項都在這兒了！！
2020-11-15
MySql
MySQL主從複製_複製過濾
2017-12-26
MySql
終於有人把15個JavaScript的重要陣列方法給講出來了
2022-04-28
JavaScript陣列
終於有人把雲端計算、大資料和人工智慧講明白了
2018-05-19
大資料人工智慧
MySQL的主從複製、半同步複製、主主複製詳解
2016-03-11
MySql
配置mysql5.5主從複製、半同步複製、主主複製
2016-01-18
MySql
Redis主從複製看這篇就夠了
2018-09-05
Redis
用幾張圖實戰講解MySQL主從複製
2024-03-07
MySql
mysql 基於日誌的主從複製
2020-08-04
MySql
MySQL基於binlog主從複製配置
2015-09-11
MySql
MySQL主從複製原理
2019-04-01
MySql
MySQL的主從複製
2018-11-29
MySql
mysql--主從複製
2020-10-23
MySql
mysql主從複製搭建
2020-11-30
MySql

這一次終於有人把MySQL主從複製講全面了！！！

文章簡介

模式優勢

實現原理

常見模式

配置流程

master 操作

slave 操作

開啟主從複製

斷開主從複製

重置主從複製資訊

效果演示

同步模式

同步模式分類

非同步同步模式

半同步模式

半同步實現流程

半同步問題總結

半同步一致性

兩者對比

GTID同步

什麼是GTID同步

GTID組成部分

配置流程

文章總結

相關文章