Mysql原理及主從架構_weixin 詳情 - mysql,架構,數據庫,服務器,MySQL,Html,CSS,前端開發 coolfengsy 博客

在互聯網應用的發展過程中，隨着業務量的不斷增長，單台 MySQL 數據庫服務器往往難以滿足高併發、大數據量的業務需求。此時，MySQL 主從架構應運而生，它不僅能夠有效分擔數據庫的讀寫壓力，還能提高數據的安全性和系統的可用性。本文將從 MySQL 主從架構的基本概念出發，深入探討其工作原理、搭建步驟、同步類型、常見問題及解決方案，同時分享一些優化技巧，幫助大家全面掌握 MySQL 主從架構的相關知識。

一、MySQL 主從架構基本概念

1.1 什麼是 MySQL 主從架構

MySQL 主從架構，也稱為 MySQL 主從複製，是指將一台 MySQL 服務器（稱為主服務器，Master）的數據變更同步到一台或多台其他 MySQL 服務器（稱為從服務器，Slave）的過程。在主從架構中，主服務器負責處理所有的寫操作（INSERT、UPDATE、DELETE）以及部分讀操作，而從服務器則主要負責處理讀操作，從而實現讀寫分離，提高數據庫的整體性能。

1.2 主從架構的作用

讀寫分離：這是主從架構最主要的作用。將寫操作集中在主服務器上，讀操作分散到從服務器上，能夠有效減輕主服務器的壓力，提高數據庫的併發處理能力。例如，在電商網站中，商品的下單、庫存更新等寫操作由主服務器處理，而商品的查詢、瀏覽等大量讀操作則由從服務器承擔。
數據備份：從服務器會實時或準實時地同步主服務器的數據，相當於為數據提供了一份實時備份。當主服務器發生故障導致數據丟失時，可以通過從服務器快速恢復數據，降低數據丟失的風險。
高可用性：當主服務器出現故障無法正常工作時，可以將其中一台從服務器切換為新的主服務器，繼續提供服務，從而減少系統的 downtime，提高系統的可用性。例如，在金融交易系統中，高可用性至關重要，主從架構能夠有效保障系統的持續穩定運行。
負載均衡：通過多個從服務器分擔讀操作，可以避免單一服務器因讀請求過多而導致性能瓶頸，提高整個數據庫系統的響應速度和吞吐量。

二、MySQL 主從複製原理

MySQL 主從複製主要基於二進制日誌（Binary Log）實現，具體工作流程如下：

2.1 主服務器（Master）操作

開啓二進制日誌：主服務器需要開啓二進制日誌功能，當主服務器上發生數據變更操作（如 INSERT、UPDATE、DELETE）時，會將這些操作記錄到二進制日誌文件中。二進制日誌文件會按照一定的規則進行滾動生成，例如按照文件大小或時間間隔。
記錄二進制日誌事件：每一個數據變更操作都會被記錄為一個二進制日誌事件（Binary Log Event），每個事件都包含了操作的類型、涉及的表、數據變更的內容以及事件發生的時間戳等信息。這些事件會按照發生的順序依次寫入二進制日誌文件。

2.2 從服務器（Slave）操作

從服務器通過兩個線程來完成與主服務器的數據同步，分別是 I/O 線程（I/O Thread）和 SQL 線程（SQL Thread）。

I/O 線程工作流程：

從服務器啓動 I/O 線程後，會向主服務器發送一個連接請求，請求同步主服務器的二進制日誌。
主服務器接收到從服務器的連接請求後，會創建一個二進制日誌轉儲線程（Binary Log Dump Thread），該線程會讀取主服務器上二進制日誌文件中的事件，並將這些事件發送給從服務器的 I/O 線程。
從服務器的 I/O 線程接收到主服務器發送的二進制日誌事件後，會將這些事件寫入到從服務器本地的中繼日誌（Relay Log）文件中。中繼日誌的格式與二進制日誌類似，它相當於一箇中間緩存，用於存儲從主服務器獲取的二進制日誌事件。

SQL 線程工作流程：

從服務器啓動 SQL 線程後，會不斷讀取中繼日誌文件中的二進制日誌事件。
對於每一個讀取到的二進制日誌事件，SQL 線程會解析該事件，並在從服務器上執行與主服務器相同的數據變更操作，從而將主服務器的數據變更同步到從服務器上。
當 SQL 線程執行完一箇中繼日誌文件中的所有事件後，會自動刪除該中繼日誌文件，以節省磁盤空間。

2.3 主從複製的整個流程總結

主服務器開啓二進制日誌，記錄數據變更操作到二進制日誌文件。
從服務器的 I/O 線程連接主服務器，主服務器創建二進制日誌轉儲線程，將二進制日誌事件發送給從服務器的 I/O 線程。
從服務器的 I/O 線程將接收到的二進制日誌事件寫入中繼日誌。
從服務器的 SQL 線程讀取中繼日誌中的二進制日誌事件，並在從服務器上執行相應的操作，實現數據同步。

整個主從複製過程是異步進行的，即主服務器在執行完數據變更操作並將其記錄到二進制日誌後，不會等待從服務器完成同步就會繼續處理下一個請求。這種異步方式雖然提高了主服務器的併發處理能力，但可能會導致主從服務器之間存在一定的數據延遲。

三、MySQL 主從架構搭建步驟（基於 MySQL 8.0）

下面將詳細介紹基於 MySQL 8.0 版本搭建主從架構的具體步驟，假設我們有兩台服務器，分別作為主服務器（Master）和從服務器（Slave），服務器信息如下：

主服務器（Master）：IP 地址為 192.168.1.100，操作系統為 CentOS 7
從服務器（Slave）：IP 地址為 192.168.1.101，操作系統為 CentOS 7

3.1 環境準備

安裝 MySQL 8.0：在主服務器和從服務器上分別安裝 MySQL 8.0。可以通過 MySQL 官方 yum 倉庫進行安裝，具體步驟如下：

下載 MySQL 官方 yum 倉庫配置文件：

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

安裝 yum 倉庫配置文件：

rpm -ivh mysql80-community-release-el7-3.noarch.rpm

安裝 MySQL 8.0：

yum install -y mysql-community-server

啓動 MySQL 服務並設置開機自啓：

systemctl start mysqld

systemctl enable mysqld

獲取初始密碼並修改：MySQL 8.0 安裝完成後，會在日誌文件中生成一個初始密碼，使用以下命令獲取初始密碼：

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

使用初始密碼登錄 MySQL，並修改初始密碼（MySQL 8.0 對密碼強度有要求，密碼需包含大小寫字母、數字和特殊字符）：

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123456';

關閉防火牆或開放 MySQL 端口：為了保證主從服務器之間能夠正常通信，需要關閉防火牆或開放 MySQL 默認端口 3306。

關閉防火牆：

systemctl stop firewalld

systemctl disable firewalld

或開放 3306 端口：

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

3.2 配置主服務器（Master）

修改 MySQL 配置文件：編輯 MySQL 配置文件/etc/my.cnf，添加以下配置：

[mysqld]

# 主服務器唯一ID（取值範圍1-2^32-1，不能與從服務器重複）

server-id = 1

# 開啓二進制日誌，指定二進制日誌文件的存儲路徑和前綴

log-bin = /var/lib/mysql/mysql-bin

# 指定二進制日誌的格式（可選值：STATEMENT、ROW、MIXED，推薦使用ROW格式，兼容性更好）

binlog-format = ROW

# 指定需要同步的數據庫（如果不指定，則同步所有數據庫）

binlog-do-db = test_db

# 指定不需要同步的數據庫（可選）

binlog-ignore-db = mysql

# 防止主從複製過程中，從服務器修改數據後與主服務器數據不一致

read-only = 0

# 二進制日誌過期時間（單位：天），過期後自動刪除，避免日誌文件佔用過多磁盤空間

expire_logs_days = 7

配置説明：

server-id：用於標識主從服務器，每個服務器的server-id必須唯一，主服務器的server-id不能與從服務器相同。
log-bin：開啓二進制日誌，並指定二進制日誌文件的存儲路徑和前綴，二進制日誌文件會以mysql-bin.xxxxxx的形式命名，其中xxxxxx是一個自增的序列號。
binlog-format：指定二進制日誌的格式，有三種格式可選：

STATEMENT：記錄 SQL 語句，日誌文件較小，但可能存在一些不兼容的情況，例如使用了NOW()、UUID()等函數時，在從服務器上執行可能會得到不同的結果。
ROW：記錄數據行的變更，日誌文件較大，但兼容性最好，能夠準確地同步數據變更，避免因 SQL 語句的差異導致的數據不一致問題。
MIXED：混合使用STATEMENT和ROW格式，MySQL 會根據 SQL 語句的類型自動選擇合適的日誌格式。

binlog-do-db：指定需要同步的數據庫，如果指定了該參數，主服務器只會將指定數據庫的變更記錄到二進制日誌中，從服務器也只會同步該數據庫的數據。如果不指定該參數，則會同步所有數據庫的數據。
binlog-ignore-db：指定不需要同步的數據庫，主服務器不會將指定數據庫的變更記錄到二進制日誌中，從服務器也不會同步該數據庫的數據。
read-only：設置主服務器是否為只讀模式，0表示可讀寫，1表示只讀。主服務器需要處理寫操作，所以設置為0。
expire_logs_days：設置二進制日誌的過期時間，過期後的二進制日誌文件會被自動刪除，避免日誌文件佔用過多的磁盤空間。

重啓 MySQL 服務：修改配置文件後，需要重啓 MySQL 服務使配置生效：

systemctl restart mysqld

創建用於主從複製的用户並授權：在主服務器上創建一個專門用於主從複製的用户，並授予該用户複製權限。

mysql -u root -p

# 創建用户repl，允許從192.168.1.101（從服務器IP）連接

CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'Repl@123456';

# 授予repl用户複製權限

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';

# 刷新權限，使授權生效

FLUSH PRIVILEGES;

這裏REPLICATION SLAVE權限是從服務器連接主服務器並獲取二進制日誌所必需的權限。

查看主服務器狀態：在主服務器上執行以下命令，查看主服務器的狀態，記錄File和Position的值，後續配置從服務器時需要使用：

SHOW MASTER STATUS;

執行結果如下（示例）：

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	156	test_db	mysql
其中，`File`表示當前主服務器正在寫入的二進制日誌文件名稱，`Position`表示當前二進制日誌文件中的位置偏移量，從服務器將從該位置開始同步二進制日誌。

3.3 配置從服務器（Slave）

修改 MySQL 配置文件：編輯從服務器的 MySQL 配置文件/etc/my.cnf，添加以下配置：

[mysqld]

# 從服務器唯一ID（取值範圍1-2^32-1，不能與主服務器重複）

server-id = 2

# 開啓中繼日誌，指定中繼日誌文件的存儲路徑和前綴（可選，如果不指定，默認會在數據目錄下生成）

relay-log = /var/lib/mysql/relay-bin

# 指定需要同步的數據庫（與主服務器的binlog-do-db對應）

replicate-do-db = test_db

# 指定不需要同步的數據庫（可選，與主服務器的binlog-ignore-db對應）

replicate-ignore-db = mysql

# 設置從服務器為只讀模式（除了超級用户外，其他用户無法執行寫操作）

read-only = 1

# 防止從服務器修改數據後，主服務器同步數據時出現衝突（MySQL 8.0新增參數）

super-read-only = 1

配置説明：

server-id：從服務器的唯一 ID，必須與主服務器的server-id不同。
relay-log：開啓中繼日誌，並指定中繼日誌文件的存儲路徑和前綴，中繼日誌文件會以relay-bin.xxxxxx的形式命名。如果不指定該參數，MySQL 會在數據目錄下默認生成中繼日誌文件。
replicate-do-db：指定需要同步的數據庫，與主服務器的binlog-do-db參數對應，確保從服務器只同步指定數據庫的數據。
replicate-ignore-db：指定不需要同步的數據庫，與主服務器的binlog-ignore-db參數對應。
read-only：設置從服務器為只讀模式，1表示只讀。此時，除了擁有SUPER權限的用户（如 root 用户）外，其他用户無法執行寫操作，這樣可以防止從服務器的數據被意外修改，保證主從數據的一致性。
super-read-only：MySQL 8.0 新增的參數，當該參數設置為1時，即使是擁有SUPER權限的用户也無法執行寫操作，進一步加強了從服務器的只讀保護。

重啓 MySQL 服務：修改配置文件後，重啓從服務器的 MySQL 服務使配置生效：

systemctl restart mysqld

配置從服務器連接主服務器：在從服務器上執行以下命令，配置從服務器連接主服務器的相關信息：

mysql -u root -p

# 停止從服務器的複製線程（如果之前已經開啓過）

STOP SLAVE;

# 配置主服務器信息，其中MASTER_LOG_FILE和MASTER_LOG_POS的值為之前在主服務器上查看的File和Position的值

CHANGE MASTER TO

MASTER_HOST='192.168.1.100',  # 主服務器IP地址

MASTER_USER='repl',           # 用於主從複製的用户名

MASTER_PASSWORD='Repl@123456',# 用於主從複製的用户密碼

MASTER_LOG_FILE='mysql-bin.000001',  # 主服務器當前的二進制日誌文件名稱

MASTER_LOG_POS=156;           # 主服務器當前的二進制日誌位置偏移量

# 啓動從服務器的複製線程

START SLAVE;

查看從服務器複製狀態：在從服務器上執行以下命令，查看從服務器的複製狀態，確認主從複製是否正常工作：

SHOW SLAVE STATUSG;

執行結果中，需要重點關注以下兩個參數的值：

Slave_IO_Running：表示從服務器的 I/O 線程是否正常運行，正常情況下應為Yes。
Slave_SQL_Running：表示從服務器的 SQL 線程是否正常運行，正常情況下應為Yes。
如果這兩個參數的值都為Yes，説明主從複製已經成功搭建並正常工作。如果其中任何一個參數的值為No，則需要根據Last_IO_Error或Last_SQL_Error字段的錯誤信息進行排查和解決。

四、MySQL 主從複製類型

根據數據同步的方式和時機，MySQL 主從複製主要可以分為以下幾種類型：

4.1 異步複製（Asynchronous Replication）

異步複製是 MySQL 主從複製的默認方式，也是最常用的複製方式。在異步複製中，主服務器在執行完數據變更操作並將其記錄到二進制日誌後，不會等待從服務器完成同步，就會立即向客户端返回執行結果。從服務器的 I/O 線程會異步地從主服務器獲取二進制日誌，並寫入中繼日誌，SQL 線程再異步地執行中繼日誌中的事件。

特點：

主服務器性能高：主服務器無需等待從服務器的同步反饋，能夠快速處理大量併發請求，不會因為從服務器的同步速度而受到影響。
存在數據不一致風險：由於主從同步是異步的，當主服務器發生故障時，可能存在部分已提交的事務還未同步到從服務器的情況，此時若將從服務器切換為主服務器，會導致數據丟失，造成主從數據不一致。
部署簡單：無需額外安裝插件或進行復雜配置，默認情況下 MySQL 就支持異步複製，上手難度較低。

適用場景：適用於對數據一致性要求不高，而對主服務器性能要求較高的業務場景，例如博客系統、新聞資訊網站等。這類業務中，即使出現少量數據丟失，對業務的影響也相對較小。

4.2 半同步複製（Semi-Synchronous Replication）

為了解決異步複製中可能出現的數據不一致問題，MySQL 引入了半同步複製機制。半同步複製是在異步複製的基礎上，增加了主服務器與從服務器之間的確認機制。在半同步複製中，主服務器在執行完數據變更操作並將其記錄到二進制日誌後，不會立即向客户端返回執行結果，而是會等待至少一台從服務器的 I/O 線程將該事務的二進制日誌事件寫入到中繼日誌，並向主服務器返回確認信息後，才會向客户端返回執行結果。

實現條件：半同步複製需要在主服務器和從服務器上分別安裝並啓用semisync_master和semisync_slave插件，MySQL 5.5 及以上版本支持半同步複製。

配置步驟（基於 MySQL 8.0）：

主服務器配置：

登錄 MySQL，安裝並啓用semisync_master插件：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;

設置主服務器等待從服務器確認的超時時間（單位：毫秒），若超過該時間仍未收到從服務器的確認信息，主服務器會自動切換為異步複製模式：

SET GLOBAL rpl_semi_sync_master_timeout = 1000;  # 1秒

（可選）設置需要確認的從服務器數量，默認情況下主服務器只需等待至少一台從服務器的確認：

SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;

從服務器配置：

登錄 MySQL，安裝並啓用semisync_slave插件：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重啓從服務器的 I/O 線程，使半同步複製配置生效：

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

特點：

數據一致性較高：主服務器會等待至少一台從服務器確認接收事務日誌後才返回結果，大大降低了主服務器故障時數據丟失的風險，提高了數據的一致性。
主服務器性能略有下降：由於主服務器需要等待從服務器的確認信息，會增加事務的響應時間，相比異步複製，主服務器的併發處理能力會略有下降。
存在超時切換機制：當從服務器出現故障或網絡延遲導致主服務器超時未收到確認信息時，主服務器會自動切換為異步複製，避免因等待確認而導致業務中斷。

適用場景：適用於對數據一致性有一定要求，同時能夠接受主服務器性能略有下降的業務場景，例如電商平台的訂單管理、用户賬户管理等。這類業務中，數據的準確性至關重要，不允許出現大量數據丟失的情況。

4.3 全同步複製（Fully Synchronous Replication）

全同步複製是一種比半同步複製更嚴格的數據同步機制。在全同步複製中，主服務器在執行完數據變更操作並將其記錄到二進制日誌後，會等待所有從服務器的 SQL 線程都執行完該事務的二進制日誌事件，並向主服務器返回確認信息後，才會向客户端返回執行結果。

特點：

數據一致性最高：所有從服務器都完成事務的執行後，主服務器才返回結果，能夠確保主從服務器的數據完全一致，不存在任何數據延遲或丟失的情況。
主服務器性能嚴重下降：主服務器需要等待所有從服務器完成事務執行，這會導致事務的響應時間大幅增加，主服務器的併發處理能力嚴重受限，甚至可能成為系統的性能瓶頸。
可用性較低：如果其中任何一台從服務器出現故障或執行事務緩慢，都會導致主服務器一直處於等待狀態，無法及時向客户端返回結果，影響業務的正常運行。

適用場景：適用於對數據一致性要求極高，而對性能和可用性要求較低的業務場景，例如金融行業的核心交易系統、證券交易系統等。這類業務中，數據的絕對一致性是首要目標，即使犧牲部分性能和可用性也必須保證數據不出現任何偏差。

五、MySQL 主從架構常見問題及解決方案

在 MySQL 主從架構的實際應用過程中，可能會遇到各種問題，如主從數據不一致、複製延遲、複製線程異常等。下面將介紹一些常見問題及相應的解決方案。

5.1 主從數據不一致

問題表現：在主服務器上執行的數據變更操作，在從服務器上未執行或執行結果與主服務器不一致，導致主從服務器的數據存在差異。

常見原因：

從服務器的 SQL 線程執行中繼日誌事件時發生錯誤，例如從服務器上不存在主服務器上執行操作涉及的表或字段，導致 SQL 線程中斷，後續的事務無法繼續執行。
人為在從服務器上執行了寫操作（如 INSERT、UPDATE、DELETE），修改了從服務器的數據，破壞了主從數據的一致性。
主服務器的二進制日誌格式設置不當，例如使用STATEMENT格式時，某些 SQL 語句（如包含NOW()、UUID()函數的語句）在從服務器上執行會得到不同的結果。
主從服務器的 MySQL 版本不一致，存在兼容性問題，導致某些事務在從服務器上無法正常執行。

解決方案：

檢查並修復 SQL 線程錯誤：

在從服務器上執行SHOW SLAVE STATUSG;命令，查看Last_SQL_Error字段的錯誤信息，根據錯誤信息排查問題原因。例如，如果錯誤信息提示 “Table ‘test_db.user’ doesn’t exist”，則需要在從服務器上創建test_db.user表，確保表結構與主服務器一致。
修復問題後，重啓從服務器的 SQL 線程，繼續執行後續的中繼日誌事件：

STOP SLAVE SQL_THREAD;

START SLAVE SQL_THREAD;

如果錯誤無法修復，或者希望快速恢復主從數據一致性，可以使用pt-table-checksum和pt-table-sync工具（Percona Toolkit 中的工具）來檢查和修復主從數據不一致的問題。

使用pt-table-checksum工具檢查主從數據一致性：

pt-table-checksum --host=192.168.1.100 --user=root --password=Root@123456 --databases=test_db

使用pt-table-sync工具修復主從數據不一致：

pt-table-sync --execute --host=192.168.1.100 --user=root --password=Root@123456 --databases=test_db h=192.168.1.101,u=root,p=Root@123456

禁止在從服務器上執行寫操作：

確保從服務器的read-only參數設置為1，super-read-only參數設置為1（MySQL 8.0 及以上版本），限制所有用户（包括 root 用户）在從服務器上執行寫操作。
嚴格控制從服務器的訪問權限，避免人為誤操作執行寫命令。

選擇合適的二進制日誌格式：推薦使用ROW格式的二進制日誌，ROW格式記錄的是數據行的變更，能夠確保在從服務器上執行得到與主服務器一致的結果，避免因 SQL 語句的差異導致的數據不一致問題。
保證主從服務器 MySQL 版本一致：在搭建主從架構時，儘量保證主服務器和從服務器的 MySQL 版本相同，或從服務器版本高於主服務器版本（且兼容主服務器版本），避免因版本差異導致的兼容性問題。

5.2 主從複製延遲

問題表現：主服務器上執行的數據變更操作，需要較長時間才能同步到從服務器上，導致從服務器的數據滯後於主服務器，即主從複製延遲。

常見原因：

主服務器的寫操作壓力過大，產生大量的二進制日誌事件，從服務器的 I/O 線程和 SQL 線程無法及時處理這些事件，導致複製延遲。
從服務器的硬件配置較低（如 CPU 性能不足、內存不足、磁盤 I/O 速度慢），無法快速執行中繼日誌中的事件，導致 SQL 線程執行緩慢。
網絡帶寬不足或網絡延遲較高，導致主服務器的二進制日誌事件無法快速傳輸到從服務器，影響 I/O 線程的同步速度。
從服務器上存在長時間運行的查詢語句，佔用了大量的 CPU 和內存資源，導致 SQL 線程無法及時執行中繼日誌中的事件。
主服務器開啓了二進制日誌的延遲寫入功能（如sync_binlog=0），導致二進制日誌無法及時寫入磁盤，影響從服務器的同步速度。

解決方案：

優化主服務器的寫操作：

對主服務器上的寫操作進行優化，例如合併批量寫入操作（如將多條 INSERT 語句合併為一條），減少事務的數量。
合理設計數據庫表結構和索引，提高寫操作的執行效率，減少二進制日誌事件的產生量。
對於非核心業務的寫操作，可以考慮分庫分表，將寫壓力分散到多個主服務器上。

提升從服務器的硬件配置：

為從服務器配備高性能的 CPU、足夠的內存和高速的磁盤（如 SSD），提高從服務器的處理能力和 I/O 速度。
合理配置從服務器的 MySQL 參數，例如增加innodb_buffer_pool_size（InnoDB 緩衝池大小），減少磁盤 I/O 操作；調整max_connections（最大連接數），避免連接數不足導致的性能問題。

優化網絡環境：

確保主從服務器之間的網絡帶寬充足，避免因網絡帶寬不足導致的日誌傳輸緩慢。
將主從服務器部署在同一局域網內，減少網絡延遲，提高日誌傳輸速度。如果主從服務器不在同一局域網，可以考慮使用專線或 VPN 等方式優化網絡連接。

優化從服務器的查詢語句：

避免在從服務器上執行長時間運行的查詢語句，對於複雜的查詢，可以考慮在從服務器上創建合適的索引，提高查詢效率。
限制從服務器的查詢併發量，避免大量查詢語句佔用過多的資源，影響 SQL 線程的執行速度。可以使用max_user_connections參數限制單個用户的最大連接數，或使用數據庫中間件（如 MyCat、Sharding-JDBC）對查詢請求進行限流和調度。

調整主服務器的二進制日誌參數：

將主服務器的sync_binlog參數設置為1，確保主服務器每次提交事務後，都將二進制日誌同步寫入磁盤，避免因二進制日誌延遲寫入導致的複製延遲。但需要注意，sync_binlog=1會增加主服務器的磁盤 I/O 壓力，可能會影響主服務器的性能，需要根據實際情況權衡。
合理設置主服務器的binlog_cache_size參數（二進制日誌緩存大小），對於大事務，可以適當增大該參數，減少二進制日誌的磁盤 I/O 操作。

5.3 複製線程異常（Slave_IO_Running 或 Slave_SQL_Running 為 No）

問題表現：在從服務器上執行SHOW SLAVE STATUSG;命令時，發現Slave_IO_Running或Slave_SQL_Running參數的值為No，表示從服務器的 I/O 線程或 SQL 線程未正常運行，主從複製無法正常進行。

常見原因及解決方案：

5.3.1 Slave_IO_Running 為 No

主從服務器網絡不通：

檢查主從服務器之間的網絡連接是否正常，可以使用ping命令測試主服務器的 IP 地址是否可達：

ping 192.168.1.100  # 在從服務器上執行，測試是否能ping通主服務器

檢查主服務器的防火牆是否開放了 MySQL 的 3306 端口，或是否禁止了從服務器的 IP 地址訪問。如果防火牆未開放端口或禁止訪問，需要重新配置防火牆規則：

# 在主服務器上開放3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

# 或允許從服務器的IP地址訪問

firewall-cmd --zone=public --add-source=192.168.1.101 --permanent

firewall-cmd --reload

主服務器的複製用户權限不足或用户名 / 密碼錯誤：

檢查在主服務器上創建的複製用户（如 repl）是否具有REPLICATION SLAVE權限，以及用户名和密碼是否正確。可以在主服務器上執行以下命令查看用户權限：

SHOW GRANTS FOR 'repl'@'192.168.1.101';

如果權限不足，需要重新授予權限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';

FLUSH PRIVILEGES;

如果用户名或密碼錯誤，需要在從服務器上重新配置主服務器信息：

STOP SLAVE;

CHANGE MASTER TO MASTER_PASSWORD='CorrectPassword';  # 修改為正確的密碼

START SLAVE;

主服務器的二進制日誌文件名稱或位置偏移量錯誤：

檢查從服務器配置的主服務器二進制日誌文件名稱（MASTER_LOG_FILE）和位置偏移量（MASTER_LOG_POS）是否與主服務器當前的狀態一致。可以在主服務器上執行SHOW MASTER STATUS;命令獲取最新的File和Position值。
如果配置的日誌文件名稱或位置偏移量錯誤，需要在從服務器上重新配置：

STOP SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=368;  # 修改為最新的File和Position值

START SLAVE;

5.3.2 Slave_SQL_Running 為 No

從服務器上存在與主服務器不一致的數據：

例如，主服務器上執行了UPDATE test_db.user SET name='張三' WHERE id=1;操作，而從服務器上不存在id=1的用户記錄，導致 SQL 線程執行該事務時失敗。
解決方案：根據Last_SQL_Error字段的錯誤信息，手動在從服務器上修復數據不一致的問題，例如插入缺失的記錄、更新錯誤的數據等。修復完成後，重啓 SQL 線程：

STOP SLAVE SQL_THREAD;

START SLAVE SQL_THREAD;

從服務器上的表結構與主服務器不一致：

例如，主服務器上的test_db.user表有id、name、age三個字段，而從服務器上的test_db.user表只有id、name兩個字段，當主服務器執行INSERT INTO test_db.user (id, name, age) VALUES (2, '李四', 25);操作時，從服務器的 SQL 線程執行該語句會失敗。
解決方案：確保從服務器上的表結構與主服務器完全一致。可以使用mysqldump工具導出主服務器的表結構，然後在從服務器上導入：

# 在主服務器上導出test_db.user表的結構

mysqldump -u root -p --no-data test_db user > user_table_structure.sql

# 將導出的表結構文件複製到從服務器

scp user_table_structure.sql root@192.168.1.101:/tmp/

# 在從服務器上導入表結構

mysql -u root -p test_db < /tmp/user_table_structure.sql

導入完成後，重啓 SQL 線程。

從服務器的 SQL_MODE 與主服務器不一致：

MySQL 的SQL_MODE參數用於控制 SQL 語句的執行模式，如果主從服務器的SQL_MODE不一致，可能導致某些 SQL 語句在從服務器上無法正常執行。例如，主服務器的SQL_MODE未開啓STRICT_TRANS_TABLES（嚴格模式），允許插入不符合字段類型的數據（如向INT類型字段插入字符串），而從服務器開啓了該模式，執行相同的INSERT語句時會報錯，導致 SQL 線程中斷。
解決方案：將從服務器的SQL_MODE配置為與主服務器一致。首先在主服務器上查看當前的SQL_MODE：

SHOW VARIABLES LIKE 'sql_mode';

假設主服務器的SQL_MODE為ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION，則在從服務器的/etc/my.cnf文件中添加以下配置：

[mysqld]

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重啓從服務器的 MySQL 服務，使配置生效：

systemctl restart mysqld

最後重啓 SQL 線程，恢復主從複製：

STOP SLAVE SQL_THREAD;

START SLAVE SQL_THREAD;

六、MySQL 主從架構優化技巧

為了進一步提升 MySQL 主從架構的性能、穩定性和可用性，需要從多個維度進行優化。以下是一些關鍵的優化方向和具體實踐。

6.1 數據庫參數優化

6.1.1 主服務器參數優化

二進制日誌相關參數

sync_binlog：默認值為 0，表示 MySQL 不主動同步二進制日誌到磁盤，依賴操作系統的緩存刷新。若追求數據安全性，可設置為 1（每次事務提交都同步日誌到磁盤），但會增加磁盤 I/O 開銷；若追求性能，可設置為 100-1000（每提交 N 個事務同步一次日誌），平衡安全性與性能。

sync_binlog = 100

binlog_cache_size：用於緩存未提交事務的二進制日誌，默認值為 32KB。對於大事務場景（如批量數據導入），可適當增大該參數（如 64KB-4MB），減少磁盤 I/O 次數：

binlog_cache_size = 64K

max_binlog_size：單個二進制日誌文件的最大大小，默認值為 1GB。若設置過小，會導致日誌文件頻繁滾動，增加磁盤開銷；若設置過大，會增加日誌恢復時間。建議根據業務量設置為 512MB-2GB：

max_binlog_size = 1G

事務與併發參數

innodb_flush_log_at_trx_commit：控制 InnoDB 事務日誌的刷新策略，默認值為 1（每次事務提交都同步日誌到磁盤，安全性最高）。若業務可接受少量數據丟失，可設置為 2（事務提交時將日誌寫入操作系統緩存，每秒刷新到磁盤），提升性能：

innodb_flush_log_at_trx_commit = 2

max_connections：主服務器的最大併發連接數，默認值為 151。需根據業務併發量調整，建議預留 20%-30% 的冗餘，避免連接數不足導致的業務中斷：

max_connections = 1000

6.1.2 從服務器參數優化

複製線程參數

slave_parallel_workers：MySQL 5.6 及以上版本支持的從服務器並行複製參數，默認值為 0（單線程複製）。對於寫壓力大的場景，可設置為 4-16（根據 CPU 核心數調整），讓多個 SQL 線程並行執行中繼日誌事件，減少複製延遲：

slave_parallel_workers = 8

slave_parallel_type：並行複製的類型，默認值為DATABASE（按數據庫維度並行），適用於多數據庫場景；若單數據庫下有大量併發事務，可設置為LOGICAL_CLOCK（按事務邏輯時鐘並行），提升並行效率：

slave_parallel_type = LOGICAL_CLOCK

查詢性能參數

innodb_buffer_pool_size：InnoDB 緩衝池大小，默認值為 128MB。從服務器主要承擔讀操作，建議將該參數設置為物理內存的 50%-70%，減少磁盤 I/O：

innodb_buffer_pool_size = 8G  # 若服務器內存為16GB

query_cache_type：查詢緩存開關，默認值為 0（關閉）。由於查詢緩存在高併發場景下會產生鎖競爭，且失效頻率高，建議保持關閉；若存在大量重複只讀查詢（如靜態數據查詢），可開啓並設置query_cache_size：

query_cache_type = 0

query_cache_size = 0

6.2 架構層面優化

多級從服務器架構

當單台從服務器無法承載讀壓力時，可搭建 “主服務器→一級從服務器→二級從服務器” 的多級架構。一級從服務器既同步主服務器數據，又作為二級從服務器的主服務器，將讀壓力分散到更多從服務器節點。
注意事項：多級架構會增加複製延遲，需確保一級從服務器性能充足，且二級從服務器的server-id與主服務器、一級從服務器不重複。

讀寫分離中間件引入

手動維護讀寫分離（如應用層判斷 SQL 類型，路由到主 / 從服務器）會增加開發成本，且難以應對從服務器故障切換。建議引入中間件（如 MyCat、ProxySQL、Sharding-JDBC），實現以下功能：

自動路由：將寫操作路由到主服務器，讀操作負載均衡到多台從服務器；
故障檢測：實時監控主從服務器狀態，當從服務器故障時自動剔除，恢復後重新加入；
延遲過濾：過濾掉延遲超過閾值的從服務器，避免業務讀取到過期數據。

數據分片與主從結合

當單庫數據量超過 100GB 或單表數據量超過 10GB 時，即使使用主從架構，查詢性能也會下降。此時可結合分庫分表（如按用户 ID 哈希分片、按時間範圍分片），將數據分散到多個主服務器節點，每個主服務器再配置從服務器，形成 “分片 + 主從” 的混合架構，同時解決數據量過大和讀寫壓力的問題。

6.3 運維監控優化

關鍵指標監控

需實時監控主從架構的核心指標，及時發現異常，常用指標包括：

複製延遲：通過SHOW SLAVE STATUSG;中的Seconds_Behind_Master字段查看，正常應小於 10 秒；
複製線程狀態：Slave_IO_Running和Slave_SQL_Running需始終為Yes；
二進制日誌與中繼日誌：監控日誌文件大小、生成頻率，避免磁盤空間耗盡；
主從服務器資源：CPU 使用率（建議低於 80%）、內存使用率（建議低於 90%）、磁盤 I/O（避免持續 100% 佔用）。

監控工具選型

推薦使用專業監控工具實現自動化監控與告警：

Prometheus + Grafana：通過mysqld_exporter採集 MySQL 指標，在 Grafana 中配置可視化面板，設置延遲、線程異常等指標的告警閾值（如延遲超過 30 秒觸發告警）；
Zabbix：自帶 MySQL 監控模板，支持監控複製狀態、連接數、慢查詢等指標，可通過郵件、短信發送告警；
Percona Monitoring and Management (PMM)：專為 MySQL、MongoDB 設計的監控工具，內置主從複製監控模塊，支持一鍵部署。

七、MySQL 主從切換方案

當主服務器發生硬件故障、軟件崩潰或性能瓶頸時，需將從服務器切換為新的主服務器，保證業務連續性。以下是兩種常見的切換方案，分別適用於手動運維和自動化運維場景。

7.1 手動切換方案（適用於小型架構）

7.1.1 切換前準備

確認主服務器故障狀態：通過ping、telnet ``192.168.1.100`` 3306等命令檢查主服務器是否完全不可用；
選擇目標從服務器：優先選擇Seconds_Behind_Master最小（數據最新）、硬件配置最高的從服務器作為新主服務器；
停止目標從服務器的複製：避免在切換過程中繼續同步舊主服務器數據（若舊主服務器恢復）：

STOP SLAVE;

7.1.2 切換步驟

將目標從服務器設置為可寫：修改read-only和super-read-only參數（MySQL 8.0）：

SET GLOBAL read_only = 0;

SET GLOBAL super_read_only = 0;

應用層切換：修改應用配置中的數據庫連接地址，將寫操作路由到新主服務器；
其他從服務器重新指向新主服務器：對剩餘的從服務器，執行以下命令切換主服務器：

STOP SLAVE;

CHANGE MASTER TO

MASTER_HOST='192.168.1.101',  # 新主服務器IP（原從服務器）

MASTER_USER='repl',

MASTER_PASSWORD='Repl@123456',

MASTER_LOG_FILE='relay-bin.000005',  # 新主服務器的中繼日誌文件（原從服務器的中繼日誌，可通過`SHOW SLAVE STATUSG;`的`Relay_Master_Log_File`獲取）

MASTER_LOG_POS=1024;  # 新主服務器的中繼日誌位置（通過`Relay_Log_Pos`獲取）

START SLAVE;

舊主服務器恢復後處理：若舊主服務器可修復，修復後需將其配置為新主服務器的從服務器，避免數據不一致。

7.1.3 注意事項

手動切換需人工介入，切換時間較長（通常 5-30 分鐘），適用於對可用性要求不高的業務；
切換前需確保目標從服務器的數據與舊主服務器一致，避免業務讀取到缺失數據。

7.2 自動化切換方案（適用於高可用場景）

對於金融、電商等對可用性要求極高（RTO < 1 分鐘）的業務，需通過工具實現主從自動切換，常用方案為MGR（MySQL Group Replication，MySQL 組複製） 或Keepalived + 腳本。

7.2.1 MySQL Group Replication（MGR）方案

MGR 是 MySQL 5.7.17 及以上版本官方提供的高可用方案，支持多主模式和單主模式，自動實現故障檢測與切換，核心特點如下：

架構組成：由 3-9 個節點組成複製組，每個節點保存完整數據副本；
故障檢測：組內節點通過心跳機制監控彼此狀態，若主節點故障，自動選舉新主節點；
數據一致性：基於 Paxos 協議實現事務的原子性提交，確保所有節點數據一致。

配置關鍵步驟：

所有節點開啓 GTID（全局事務 ID）：

[mysqld]

gtid_mode = ON

enforce_gtid_consistency = ON

安裝並啓用 MGR 插件：

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

SET GLOBAL group_replication_bootstrap_group = ON;  # 僅在初始化節點執行

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group = OFF;

其他節點加入複製組：

START GROUP_REPLICATION;

優勢：完全自動化，無需人工干預；支持多主模式，可分擔寫壓力；數據一致性強，基於 Paxos 協議避免數據丟失。

7.2.2 Keepalived + 腳本方案

通過 Keepalived 實現虛擬 IP（VIP）漂移，結合自定義腳本監控主從狀態，當主服務器故障時自動切換 VIP 到從服務器，步驟如下：

主從服務器安裝 Keepalived，配置相同的 VIP（如 192.168.1.200）；
編寫監控腳本（如check_mysql.sh），定期檢查主服務器 MySQL 服務狀態（如執行mysqladmin ping）；
配置 Keepalived，若監控腳本檢測到主服務器故障，自動將 VIP 漂移到從服務器，並執行從服務器切換為可寫的命令；
主服務器恢復後，腳本自動將其配置為新主服務器的從服務器，VIP 漂移回主服務器（或保持從服務器為主，視業務需求而定）。

優勢：部署簡單，基於開源工具，成本低；切換時間短（通常 10-30 秒）。

八、MySQL 主從架構的擴展與演進

隨着業務的發展，傳統的一主多從架構可能無法滿足需求，需向更靈活、更具擴展性的架構演進，常見方向如下：

8.1 從 “一主多從” 到 “多主多從”

當單主服務器的寫壓力達到瓶頸（如每秒寫事務超過 1 萬），可採用多主多從架構：

將業務按模塊或數據分片拆分到多個主服務器（如用户模塊主服務器、訂單模塊主服務器）；
每個主服務器配置多個從服務器，承擔對應模塊的讀壓力；
通過中間件（如 Sharding-JDBC）實現數據分片與讀寫路由，透明化底層架構。

8.2 結合雲原生與容器化

在雲環境中，可將 MySQL 主從架構部署在 Docker 容器中，通過 Kubernetes（K8s）實現自動化運維：

K8s 的 StatefulSet 控制器確保主從節點的穩定部署與網絡標識；
通過 K8s 的 ConfigMap 管理 MySQL 配置文件，Secret 管理數據庫密碼；
結合 K8s 的探針（Liveness Probe、Readiness Probe）監控主從狀態，故障時自動重啓或重建節點；
利用雲存儲（如 AWS EBS、阿里云云盤）存儲 MySQL 數據，確保數據持久化。

8.3 引入時序數據庫與緩存

對於日誌分析、監控數據等時序場景，可將 MySQL 主從架構與時序數據庫（如 InfluxDB、Prometheus）結合：

MySQL 主服務器存儲核心業務數據（如用户信息、訂單記錄）；
從服務器同步核心數據後，通過 ETL 工具將時序數據（如用户訪問日誌）同步到時序數據庫；
引入 Redis 緩存常用數據（如商品信息、用户會話），減少從服務器的讀壓力，提升業務響應速度。

九、總結

MySQL 主從架構是解決數據庫性能瓶頸、提高數據安全性的核心方案，從原理到實踐需掌握以下關鍵點：

核心原理：基於二進制日誌的 “主服務器記錄→從服務器 I/O 線程同步→SQL 線程執行” 流程，異步複製為默認模式，半同步 / 全同步複製需額外配置；
搭建關鍵：確保主從server-id唯一，主服務器創建複製用户並授權，從服務器正確配置主服務器信息，驗證Slave_IO_Running與Slave_SQL_Running為Yes；
問題處理：主從數據不一致需通過工具修復，複製延遲需從硬件、網絡、參數多維度優化，線程異常需根據錯誤日誌排查原因；
優化與演進：通過參數調優、架構擴展（多級從、讀寫分離）、自動化運維（MGR、K8s）提升架構性能與可用性，最終向多主多從、雲原生方向演進。

在實際應用中，需根據業務的併發量、數據一致性要求、可用性需求選擇合適的複製模式與架構方案，同時建立完善的監控與運維體系，確保主從架構穩定運行，支撐業務持續發展。

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

博客 / 詳情