MySQL 複寫完整指南｜設定步驟、故障排除、運維管理

1 1. MySQL 複寫是什麼？其概述與用途
2 2. MySQL 複寫的基本概念
3 3. MySQL 複寫設定步驟
4 4. 複寫的種類與應用
- 4.1 非同步複寫與準同步複寫的差異
  - 4.1.1 1. 非同步複寫
  - 4.1.2 2. 準同步複寫
- 4.2 利用 GTID 的複寫
  - 4.2.1 GTID 的優點
  - 4.2.2 GTID 複寫的設定
5 5. 複寫的維護與監控
- 5.1 複寫狀態確認方法
  - 5.1.1 主伺服器狀態確認
  - 5.1.2 從伺服器狀態確認
- 5.2 複寫故障排除
  - 5.2.1 1. 連線錯誤
  - 5.2.2 3. 延遲的解決
6 6. 常見問題與其處理方法
7 7. 總結
- 7.1 重點回顧

1. MySQL 複寫是什麼？其概述與用途

MySQL 複寫是一項將資料庫的副本即時同步到其他伺服器的功能。藉此可以提升資料庫的冗餘性與效能。以下將詳細說明 MySQL 複寫在什麼情境下使用，以及其運作機制。

MySQL 複寫的概述

MySQL 複寫透過 主伺服器 與 從伺服器 的架構，將資料庫內容在多台伺服器之間共享。具體而言，主伺服器將資料更新寫入二進位日誌，從伺服器讀取並套用這些更新，以達成資料同步。如此，即使主伺服器發生故障，也能切換至從伺服器，持續提供服務。

MySQL 複寫的用途

MySQL 複寫廣泛應用於以下用途。

確保高可用性：在發生故障時利用從伺服器，可將停機時間降至最低。
負載平衡：將只讀查詢分配給從伺服器，以分散主伺服器的負載。
資料保全與備份：複寫即時複製資料，亦可作為備份使用。

複寫的類型

MySQL 複寫依據資料同步方式分為以下類型。

非同步複寫：主伺服器在未等待從伺服器接收更新資訊的情況下繼續處理，可實現高速回應。但在故障時，部分資料可能無法傳送至從伺服器。
準同步複寫：在確認資料已在從伺服器上反映後才繼續處理，較非同步更具可靠性，但回應速度會稍慢。

接下來的章節將說明 MySQL 複寫的基本概念，包括二進位日誌與 GTID。

2. MySQL 複寫的基本概念

要了解 MySQL 複寫，必須掌握在複寫中扮演重要角色的二進位日誌與GTID（Global Transaction ID）的作用。這些要素是資料能正確複製的基礎。

主伺服器與從伺服器的角色

在 MySQL 複寫中，主伺服器與從伺服器各自承擔不同的角色。主伺服器會將資料的更新內容記錄到二進位日誌，並將其傳送給從伺服器。從伺服器則套用從主伺服器取得的日誌，更新資料。如此，從伺服器即可保持與主伺服器最新資料相同的內容。

二進位日誌與中繼日誌

MySQL 複寫的基礎使用以下兩種日誌。

二進位日誌（Binary Log）

二進位日誌是記錄主伺服器上資料更新（INSERT、UPDATE、DELETE 等）的日誌。藉此，伺服器能保持與主伺服器相同的資料狀態。

中繼日誌（Relay Log）

中繼日誌是從伺服器將從主伺服器取得的二進位日誌保存於自身系統的檔案。從伺服器的 SQL 執行緒會依序執行此中繼日誌，以反映資料變更。

GTID（Global Transaction ID）是什麼

GTID 是為每筆交易分配唯一 ID 的機制，有助於在多個從伺服器之間維持同步的一致性。使用 GTID 後，無需指定二進位日誌的位置，僅會自動將主伺服器尚未取得的交易套用到從伺服器，從而大幅簡化管理。

GT 的優點

唯一識別：每筆交易皆被賦予唯一的 GTID，因而能明確辨識哪些交易已套用。
復原容易：使用 GTID 後，即使主伺服器或從伺服器重新啟動，也只會重新套用尚未套用的交易。
運營管理的效率化：即使在存在多部從伺服器的大規模環境中，也能在保持交易一致性的同時，輕鬆進行管理。

要使用 GTID，必須設定 gtid_mode=ON 以及 enforce_gtid_consistency=ON。在主伺服器與從伺服器上進行此設定，即可啟用基於 GTID 的複寫。在下一節將說明 MySQL 複寫的具體設定步驟。

3. MySQL 複寫設定步驟

在此，我們將詳細說明設定 MySQL 複寫的步驟。依照以下步驟操作，即可完成主從的基本架構，實現資料的即時同步。

主伺服器設定

首先，編輯主伺服器的設定檔（通常為 my.cnf 或 my.ini），以啟用二進位日誌並設定伺服器 ID。

編輯設定檔

將以下設定加入 [mysqld] 區段，並將伺服器 ID 設為唯一值（例如：1）。

   [mysqld]
   server-id=1
   log-bin=mysql-bin

server-id 必須為每台伺服器指定不同的唯一編號，log-bin 表示啟用二進位日誌。

建立複寫使用者

在主伺服器上建立專用於複寫的使用者，並授予必要的權限。

   CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
   FLUSH PRIVILEGES;

此使用者是從從伺服器存取主伺服器資料所必需的。

確認主伺服器狀態

確認目前的二進位日誌檔案與位置（log 的位置）。此資訊在從伺服器設定時需要使用。

   SHOW MASTER STATUS;

此指令顯示的 File（日誌檔名）與 Position（位置）將用於從端的設定。

從伺服器設定

接著，編輯從伺服器的設定檔，設定伺服器 ID 與主伺服器資訊。

編輯設定檔

從伺服器也要將 server-id 設為唯一（例如：2），其編號須與主伺服器不同。

   [mysqld]
   server-id=2

為防止在從伺服器上寫入資料，通常會設定 read_only=ON。

將主伺服器資訊設定到從伺服器

在從伺服器上執行以下指令，指定主伺服器的主機名稱、使用者、二進位日誌檔名與位置。

   CHANGE MASTER TO
       MASTER_HOST='master_host',
       MASTER_USER='repl',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=123;

MASTER_LOG_FILE 與 MASTER_LOG_POS 請填入剛才在主伺服器確認的值。

啟動複寫

在從伺服器上執行以下指令，開始複寫。

   START SLAVE;

確認複寫狀態

確認主從之間的複寫是否正確設定。

確認主伺服器狀態

  SHOW MASTER STATUS;

確認從伺服器狀態

  SHOW SLAVE STATUSG;

只要 Slave_IO_Running 與 Slave_SQL_Running 顯示為 Yes，即表示複寫正常運作。

接下來的章節將說明 MySQL 複寫的進階設定方法。會討論非同步準同步複寫的差異，以及使用 GTID 的設定步驟。

4. 複寫的種類與應用

MySQL 複寫依據資料同步方式分為非同步複寫與準同步複寫兩種。透過了解各自的特點與適用情境的選擇基準，可提升系統的效能與可靠性。此外，這裡也說明利用GTID（Global Transaction Identifier）的複寫設定優點。

非同步複寫與準同步複寫的差異

1. 非同步複寫

非同步複寫是指主伺服器在交易完成的同時立即回應客戶端。也就是說，即使資料同步至從伺服器仍有延遲，主伺服器仍能處理新的請求。因此，它在回應效能上表現優異，適合以負載平衡為目的的系統。但需注意的是，發生故障時，未同步至從伺服器的資料可能會遺失。

2. 準同步複寫

準同步複寫會在主伺服器確認資料已傳送至從伺服器完成後，才回應客戶端。此方式可提升資料一致性，但因需等待資料在從端的寫入，交易的回應時間可能變長。準同步複寫適用於需要高資料一致性的系統，或是以資料可靠性為首要考量的環境。

利用 GTID 的複寫

GTID（Global Transaction Identifier）會為每筆交易賦予唯一的 ID，以在主從兩端維持交易的一致性。啟用 GTID 後，較之傳統以二進位日誌位置指定的複寫，管理上更為簡便。

GTID 的優點

資料一致性的提升：透過 GTID，可自動辨識從端尚未套用的交易，因而更容易維持資料一致性。
複寫管理的簡化：使用 GTID 後，可有效執行主從切換與復原作業。因不需指定二進位日誌位置，管理變得更簡單。

GTID 複寫的設定

若要利用 GTID，需在主從的設定檔中加入以下選項並啟用。 主伺服器的設定

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

從伺服器的設定

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

在啟用 GTID 的環境中，只需對從端使用 CHANGE MASTER TO 指令設定主伺服器資訊，即可自動執行 GTID 複寫。接下來的章節將說明 MySQL 複寫的維護方法與運營管理中的監控要點。

5. 複寫的維護與監控

為了適當運作 MySQL 複寫，定期的維護與監控是必不可少的。本節將說明用於確認複寫是否正常運作的指令，以及一般錯誤的處理方法。

複寫狀態確認方法

為了掌握複寫的狀態，使用以下指令來確認主伺服器與從伺服器之間的同步情況。

主伺服器狀態確認

在主伺服器上可透過 SHOW MASTER STATUS 指令確認複寫狀態。此指令會顯示目前的二進位日誌檔名與位置（position），以確認應傳送給從伺服器的最新更新內容。

SHOW MASTER STATUS;

此指令的輸出包含以下項目。

File：主伺服器目前輸出的二進位日誌檔名
Position：二進位日誌內目前的位置
Binlog_Do_DB與Binlog_Ignore_DB：複寫的目標資料庫

從伺服器狀態確認

從伺服器的複寫狀況可透過 SHOW SLAVE STATUS 指令確認。此指令的結果包含判斷從伺服器是否正常運作的資訊。

SHOW SLAVE STATUSG;

作為重要項目，列舉如下。

Slave_IO_Running 與 Slave_SQL_Running：若兩者皆為 Yes，表示從伺服器正常運作。
Seconds_Behind_Master：顯示從伺服器相對於主伺服器的延遲秒數。通常理想值為 0。

複寫故障排除

在複寫運作過程中常見的問題包括連線錯誤與資料不一致等。以下列出一般錯誤訊息及其處理方式。

1. 連線錯誤

若 Slave_IO_Running 為 No，表示從伺服器無法連接到主伺服器。請嘗試以下處理方法。

確認主伺服器的主機名稱或 IP 位址：確認主伺服器的位址是否正確。
確認防火牆設定：確保必要的埠號（通常為 3306）已開放。

4>2. 資料不一致若 Last_Error 中記載錯誤內容，可能表示主從之間發生資料不一致。發生資料不一致時，需要先暫停從伺服器再進行修正。

STOP SLAVE;
# 修正後重新啟動
START SLAVEcode>

3. 延遲的解決

從伺服器延遲的原因可能是硬體性能或網路問題。視需要加強從伺服器的配置，亦可改善此情況。在下一節，我們將更深入探討複寫問題的細節與解決方案。

6. 常見問題與其處理方法

在 MySQL 複寫過程中，運行時可能會發生各種問題。在此將詳細說明常見問題及其處理方法。透過及早發現問題並適當處理，可維持系統的穩定運作。

1. Slave_IO_Running 停止的情況

現象：在 SHOW SLAVE STATUS 指令的輸出中，若 Slave_IO_Running 為 No，表示從伺服器無法連接到主伺服器。 原因與對策：

網路問題：若網路連線有問題，從伺服器將無法存取主伺服器。請檢查防設定，確認是否能存取主伺服器。
主伺服器的主機名稱或 IP 位址設定錯誤：請確認在 CHANGE MASTER TO 中指定的主機名稱或 IP 位址是否正確。
使用者權限問題：若在主伺服器上設定的複寫使用者權限不足，也會導致連線失敗。請確認已使用 GRANT REPLICATION SLAVE 授予正確的權限。

2. 從伺服器的資料不一致

現象：當從伺服器與主伺服器的資料不一致時，從伺服器的資料可能會出現不一致的狀態。 原因與對策：

手動修正資料：若發生不一致，請先停止從伺服器，手動修正問題交易。修正後重新啟動從伺服器，即可恢復複寫正常。 STOP SLAVE; # 視需要修正AVE;
資料重新同步：若發生大規模不一致，可從主伺服器取得完整備份，並在從伺服器上重新同步以解決問題。

3. 複寫延遲

現象：在 SHOW SLAVE STATUS 的輸出中，若 Seconds_Behind_Master 不為 0，表示從伺服器相對於主伺服器有延遲。通常此值越小越理想。 原因與對策：

從伺服器硬體性能：若從伺服器的硬體規格較低，處理速度跟不上，可能會產生延遲。升級硬體是有效的解決方案。
查詢最佳化：若主伺服器傳送的查詢在從伺服器執行時耗時，會導致延遲。透過新增索引或最佳化查詢以縮短處理時間是有效的做法。

4. 複寫使用者的權限錯誤

現象：若 Last_Error 顯示與權限相關的錯誤訊息，可能是從伺服器缺乏連接主伺服器所需的權限。 原因與對策：

重新設定以授予權限：請確認在主伺服器上已建立具備適當權限的使用者，必要時重新設定。 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'從屬伺服器的IP位址'; FLUSH PRIVILEGES;

5. 二進位日誌的膨脹

現象：主伺服器的二進位日誌可能因膨脹而佔用伺服器磁碟空間。 原因與對策：

二進位日誌輪替：定期刪除或封存二進位日誌，可防止膨脹。設定 expire_logs_days 後，系統會自動刪除超過指定天數的日誌。 SET GLOBAL expire_logs_days = 7; # 刪除超過7天的日誌

透過了解 MySQL 複寫中常見的問題與解決方案，即可實現順暢的運維管理。接下來的章節將作為本文的總結，回顧複寫運維的要點。

7. 總結

MySQL 複寫是為了提升資料一致性與系統可靠性而重要的功能。本文從 MySQL 複寫的基本概念、設定步驟、運維管理中的監控與故障排除等進行了詳細說明。最後，將複寫運維管理的重點整理如下。

重點回顧

複寫的類型與選擇

非同步複寫具備回應速度快、適合負載平衡的優點，但若要求可靠性，則準同步複寫較為適合。請依系統需求選擇合適的方式。

GTID 的有效活用

利用 GTID 可免除指定二進位日誌位置，實現順暢的交易管理。特別是在有多個從伺服器的環境或需要快速故障復原的系統中非常有用。

定期狀態檢查

SHOW MASTER STATUS 與 SHOW SLAVE STATUS 指令，定期監控主伺服器與從伺服器的運作狀況非常重要。若偵測到異常，迅速處理即可將資料不一致或延遲的風險降至最低。

掌握一般故障排除技巧

從伺服器的連線錯誤、資料不一致、延遲等，MySQL 複寫常會出現特有的問題。了解各問題的基本解決方法，可讓運維時的故障處理更順暢。

二進位日誌的管理

二進位日誌若過度膨脹會佔用伺服器磁碟空間，建議使用 expire_logs_days 設定自動刪除，並定期進行維護。

MySQL 複寫並非設定一次就結束，日常的監控與適當的維護是必不可少的。定期檢查狀態，並視需要調整設定，即可構築與維持高可靠性的資料庫系統。希望本文能對您了解與實作 MySQL 複寫有所幫助，並祝未來的複寫運維順利。