MySQL 複製技術介紹

1.1. MySQL 複製

複製是 MySQL 的一項功能，允許伺服器將更改從一個例項複製到另一個例項。 master 將所有資料和結構更改記錄到二進位制日誌中。 slave 從 master 請求該二進位制日誌並在本地應用其內容。

1) MySQL 複製

MySQL 中的複製功能用於將更改從一個伺服器（ master ）複製到一個或多個 slave 。 master 將更改寫入二進位制日誌， slave 請求 master 的二進位制檔案並應用其內容。日誌檔案的格式影響 slave 應用更改的方式。 MySQL 支援基於語句的、基於行的以及混合格式的日誌記錄。

2) slave 數量

一個 master 的 slave 數量沒有限制。但是，每個額外 slave 使用 master 上的較少資源，所以您應該仔細考慮生產設定中每個 slave 的好處。給定環境中 master 的最佳 slave 數量取決於許多因素：模式大小、寫入次數、 master 和 slave 的相對效能以及 CPU 和記憶體可用性等因素。一般準則是將每個 master 的 slave 數量限制為不超過 30 。

3) 網路故障

MySQL 中的複製功能在網路故障時繼續工作。每個 slave 跟蹤其已經處理了多少日誌並在網路連線恢復時自動繼續處理。此行為是自動的，不需要特殊配置。

1) 複製 master 和 slave

主 /slave 關係是一對多關係：

u 每個 slave 從一個 master 讀取日誌。

u 一個 master 可以將日誌傳送給許多 slave 。

u 一個 slave 可以用作另一個 slave 的 master 。

4) 中繼 slave

一個 slave 可以用作另一個 slave 的 master 。最頂層 master 的直接 slave 請求並應用該 master 處發生的更改，而該 slave 將更改向下中繼到其 slave ，以此類推，直到複製到達該鏈的末尾。這樣可以透過多個級別的複製來傳播更新，允許更復雜的拓撲。每個額外級別會向系統新增更多傳播延遲，從而較淺設定遇到的複製滯後要比較深設定少。每個 slave 僅能有一個 master ，一個 slave 不能從多個 master 進行復制。如果一個 slave 用作其他伺服器的 master ，該 slave 常常稱為中繼 slave 。

5) 使用 BLACKHOLE 儲存引擎進行復制

BLACKHOLE 儲存引擎無提示地放棄所有資料更改，而不發出警告。二進位制日誌繼續成功記錄這些更改。當中繼 slave 將所有更改複製到深一層的 slave ，但是自身不需要將資料儲存在特定表中時，將 BLACKHOLE 用於這些表。例如，如果您具有的中繼 slave 單獨用來對少量表執行經常長時間執行的業務智慧報表，您可以將其他所有複製的表配置為使用 BLACKHOLE ，從而伺服器不會儲存其不需要的資料，同時將所有更改複製到其 slave 。

2) 複雜拓撲

可以使用更復雜的拓撲：

u 雙向拓撲具有兩個 master ，每個 master 是另一個 master 的 slave 。

u 迴圈拓撲具有任意數量的伺服器。每個伺服器是一個 master 並且是另一個 master 的 slave 。對任何 master 的更改將複製到所有 master 。

u 並非每個 slave 都必須是 master 。

MySQL 複製不執行衝突解析。

什麼是衝突解析？在典型配置中，客戶機僅將更改寫入 master ，但是從任何伺服器讀取更改。在伺服器允許對相似資料進行併發更新的環境中，資料在多個伺服器上的最終狀態可能變得不一致。應用程式負責防止或管理衝突操作。 MySQL 複製不執行衝突解決解析。包括多個 master 的所有拓撲中都可能發生衝突。這包括諸如前面顯示的簡單分層結構（如果中繼 slave 接受客戶機的更改）。衝突在迴圈拓撲中特別常見。

例如，設想使用迴圈拓撲實現複製的電子商務公司，其中兩個伺服器分別處理“ Luxury Brands ”和“ Special Events ”團隊中的應用程式。假設應用程式不管理衝突操作，並且發生以下事件：

a) “ Luxury Brands ”團隊將奢侈品的價格漲了 20% 。

b) “ Special Events ”團隊因為將要來臨的特殊節日將價格高於 500 美元的所有產品降了 50 美元。

c) 一個成本 520 美元的產品屬於這兩個類別，前面兩個操作對其值進行了更新。每個伺服器上產品的最終價格取決於每個伺服器執行操作的順序。

如果兩個操作幾乎同時發生，將發生以下操作：

a) “ Luxury Brands ”團隊中的伺服器將產品的價格增加 20% ，從 520 美元漲到 624 美元

b) “ Special Events ”團隊中的伺服器將產品價格減少 50 美元，從 520 美元降到 470 美元

c) 每個伺服器將更改複製到另一個伺服器，導致“ Luxury Brands ”伺服器假設該產品的最終值為 574 美元，“ Special Events ”伺服器假設最終值為 564 美元。

d) 複製環境中的其他伺服器根據其應用操作的順序假設最終值。

類似地，如果“ Luxury Brands ”團隊新增一個新產品並且該產品在“ Special Events ”團隊進行其更改時尚未完全複製，或者如果兩個團隊新增在不同伺服器上具有相同主鍵的產品，則會出現衝突。雖然基於行的複製可以解決一些衝突，不過許多衝突僅能在應用程式級別被阻止。

注： MySQL Cluster 在內部使用複製，這在某些方面與 MySQL 伺服器中的複製不同，並且提供衝突檢測（和可選解析）。

3) 複製適用場景

複製適用場景：

l 水平向外擴充套件：實現複製的最常見原因是在一個或多個 slave 中分佈查詢工作負荷，從而提高整個應用程式中讀取操作的效能，並透過減少 master 的讀取工作負荷來提高其上的寫入操作效能。請訪問

http://dev.mysql.com/doc/refman/5.6/en/faqsreplication.html#qandaitem-B-13-1-8

檢視關於如何透過使用向外擴充套件複製來提高系統效能的有用示例。

l 業務智慧和分析：業務智慧報表和分析處理會使用大量資源，需要大量時間來執行。在複製的環境中，可以在 slave 上執行此類查詢，從而 master 可以繼續處理生產工作負荷，而不受長時間執行的 I/O 密集型報表的影響。

l 地理資料分佈：具有分散式地理位置的公司可以受益於複製，在每個區域具有伺服器，用於處理本地資料並在組織中複製該資料。這樣可以向客戶和員工提供地理相鄰性的效能和管理優勢，同時還使公司瞭解整個公司的資料。注：多源複製僅能透過迴圈拓撲間接進行。

4) 高可用性複製

複製適用於各種高可用性場景：

l 受控切換：在硬體或系統升級期間使用副本來代替生產伺服器。

l 伺服器冗餘：在系統故障時執行故障轉移到副本伺服器。

l 聯機模式更改：在具有多個伺服器的環境中執行滾動升級來避免整個系統故障。

l 軟體升級：在環境升級過程中在不同版本的 MySQL 之間進行復制。 slave 執行的版本必須比 master 新。在升級過程中發出的查詢必須受升級過程中使用的所有版本的支援。

1.2. 配置複製

配置複製需要為每個伺服器配置唯一 server-id ；複製拓撲中的每個伺服器必須具有唯一的 server-id ，一個無符號的 32 位整數，值從（預設）到 4,294,967,295 。 server-id 為 0 的伺服器（無論是 slave 還是 master ）拒絕使用其他伺服器進行復制。

1) master 配置：

a) 啟用二進位制日誌和啟用 TCP/IP 網路。

b) 建立具有 REPLICATION SLAVE 許可權的新使用者。

c) 備份主資料庫，並且如果需要則記錄日誌座標。

每個 master 必須分配有 IP 地址和 TCP 埠，因為複製無法使用 UNIX 套接字檔案。每個 master 還必須啟用二進位制日誌記錄，因為在複製過程中，每個 master 將其日誌內容傳送到每個 slave 。每個 slave 必須登入到 master 中才能從中進行復制。如果您正由於此目的而在 master 上建立新使用者，該新使用者必須具有 REPLICATION SLAVE 許可權：

GRANT REPLICATION SLAVE ON *.* TO <user>@<slave-hostname>;

如果您正使用已經包含已填充資料庫的 master 建立複製拓撲，必須首先為 slave 建立該資料庫的副本（例如，透過執行 master 的備份並將該備份恢復到 slave ）。如果您使用全域性事務識別符號 (Global Transaction Identifier, GTID) ，則不需要記錄日誌座標。

2) slave 配置

a) 從 master 恢復備份。

b) 在每個 slave 上發出 CHANGE MASTER TO 語句，包含： master 的網路位置、複製帳戶使用者名稱和口令、開始複製操作的日誌座標（如果需要）

c) 使用 START SLAVE 開始複製。

每個 slave 僅連線到一個 master 。要告知 slave 關於 master 的資訊，請使用 CHANGE MASTER TO... 語句。在 slave 上發出 CHANGE MASTER TO … 語句來配置複製 master 連線詳細資訊：

mysql> CHANGE MASTER TO

-> MASTER_HOST = 'host_name',

-> MASTER_PORT = port_num,

-> MASTER_USER = 'user_name',

-> MASTER_PASSWORD = 'password',

-> MASTER_LOG_FILE = 'master_log_name',

-> MASTER_LOG_POS = master_log_pos;

CHANGE MASTER TO 的後續呼叫保留每個未指定選項的值，更改 master 的主機或埠還會重置日誌座標；以下語句更改口令，但是保留所有其他設定：

mysql> CHANGE MASTER TO MASTER_PASSWORD='newpass';

l MASTER_HOST 和 MASTER_PORT 值指定 master 的主機名和 TCP 埠號；

l MASTER_USER 和 MASTER_PASSWORD 值指定具有 REPLICATION SLAVE 許可權的 master 上帳戶的帳戶詳細資訊。要提高安全性，還可以在啟用 SSL 的伺服器上使用 MASTER_SSL 和相關選項加密複製期間 slave 和 master 之間的網路通訊。

l MASTER_LOG_FILE 和 MASTER_LOG_POS 值包含 slave 開始進行復制的二進位制日誌位置的日誌座標。可以透過執行 SHOW MASTER STATUS 語句從 master 獲取檔案和位置：

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000014 | 51467 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

如果使用 mysqldump 執行 master 的資料庫備份作為 slave 的起點，可以使用 --master-data 選項在備份中包括日誌座標：

mysqldump -uroot -p --master-data -B world_innodb > backup.sql

如果您使用 GTID ，則指定 MASTER_AUTO_POSITION=1 ，而不是日誌座標。

3) 使用 GTID 進行復制

使用 CHANGE MASTER TO... 啟用 GTID 複製：

l 告知 slave 透過 GTID 標識事務：

CHANGE MASTER TO MASTER_AUTO_POSITION=1;

l 您不需要提供日誌座標，例如：

– MASTER_LOG_FILE

– MASTER_LOG_POS

l 不能在同一 CHANGE MASTER TO... 語句中提供 MASTER_AUTO_POSITION 和日誌座標。

啟用基於 GTID 的複製時，不需要指定 master 的日誌座標，因為 slave 將 @@global.gtid_executed 的值傳送給 master 。因此， master 知道 slave 已經執行了哪些事務，從而僅傳送 slave 尚未執行的那些事務。

1.3. 複製的故障轉移

1) 使用日誌座標進行故障轉移

要查詢每個 slave 的新 master 和正確的日誌座標，必須嚴密檢查二進位制日誌。

– 查詢應用於每個 slave 的最近事件。

– 選擇最新 slave 作為新的 master 。

– 確定新 master 上的日誌座標來匹配每個其他 slave 上最新應用的事件。

– 在每個 slave 上發出正確的 CHANGE MASTER TO …。

要在 master 變為不可用後進行故障轉移，請停止所有 slave 並透過在其餘 slave 上發出 CHANGE MASTER TO 語句（包含新 master 的日誌座標）選擇某個 slave 作為 master 。如果進行故障轉移時 slave 不是最新狀態，您可能會具有不一致的複製拓撲：

l 如果新 master 位於特定 slave 後面（即，如果該 slave 已經應用了該新 master 的日誌末尾的事件），則該 slave 會重複那些事件。

l 如果新 master 在特定 slave 的前面（即，如果該新 master 的二進位制日誌包含該 slave 尚未應用的事件），該 slave 將跳過那些事件。

要避免這種不一致，必須選擇最新 slave 作為新 master ，然後在新 master 上查詢與每個 slave 上的最近事件匹配的日誌座標。如果某些 slave 遠在其他 slave 之後，則對於一個 slave 與下一個 slave 而言，在 CHANGE MASTER TO … 語句中提供的日誌座標將有所不同，所以您不能在新 master 上僅發出 SHOW MASTER STATUS 。相反，必須檢查二進位制日誌來查詢正確的座標。

在迴圈拓撲中，查詢每個二進位制日誌中的事件源變得非常困難。

在多個 master 接受客戶機更新的迴圈拓撲中，查詢最新 slave 和確定正確日誌座標會非常困難，因為每個 slave 使用與其他 slave 不同的順序應用操作。要避免此困難，請使用全域性事務識別符號 (Global Transaction Identifier, GTID) 。 MySQL 實用程式還包括有助於使用 GTID 進行故障轉移的工具。

2) 全域性事務識別符號 (Global Transaction Identifier, GTID)

GTID 唯一地標識複製的網路中的每個事務。

l 每個 GTID 的形式為 <source-uuid>:<transaction-id> 。

0ed18583-47fd-11e2-92f3-0019b944b7f7:338

l GTID 集包含一系列 GTID ：

0ed18583-47fd-11e2-92f3-0019b944b7f7:1-338

l 使用以下選項啟用 GTID 模式：

– gtid-mode=ON ：與每個事務一起記錄唯一的 GTID

– enforce-gtid-consistency ：禁止無法以事務安全方式記錄的事件

– log-slave-updates ：將複製的事件記錄到 slave 的二進位制日誌

源 UUID （ universally unique identifier ，通用唯一識別符號）是每個事務的源伺服器的 UUID 。每個伺服器的 UUID 儲存在資料目錄中的 auto.cnf 檔案中。如果該檔案不存在， MySQL 會建立該檔案並生成新的 UUID ，將其放在該新檔案中。使用 server_uuid 變數查詢伺服器的 UUID ：

mysql> SELECT @@server_uuid\G

*************************** 1. row ***************************

@@server_uuid: 0ed18583-47fd-11e2-92f3-0019b944b7f7

1 row in set (0.00 sec)

客戶機在 master 上執行事務時， MySQL 將建立新 GTID 並記錄事務及其唯一 GTID 。 slave 從 master 讀取並應用該事務時，該事務保持其原始 GTID 。即，複製到 slave 的事務的伺服器 UUID 是 master 的 UUID ，而不是 slave 的。複製鏈中的每個後續 slave 都將記錄該事務及其原始 GTID 。因此，複製拓撲中的每個 slave 可以確定第一個執行事務的 master 。

每個伺服器記錄事務時，它還在 gtid_executed 變數內的 GTID 集中記錄該事務的 ID 。在全域性上下文中，此變數包含記錄到伺服器的二進位制日誌的所有 GTID 集（表示此伺服器和其他上游 master 的所有事務）。

mysql> SELECT @@global.gtid_executed\G

*************************** 1. row ***************************

@@global.gtid_executed: bacc034d-4785-11e2-8fe9-0019b944b7f7:1-34,

c237b5cd-4785-11e2-8fe9-0019b944b7f7:1-9,

c9cec614-4785-11e2-8fea-0019b944b7f7:1-839

1 row in set (0.00 sec)

gtid_purged 變數包含已經從二進位制日誌中清除的 GTID 集。在伺服器上執行 RESET MASTER 時， gtid_purged 和全域性 gtid_executed 都將重置為空字串。

3) 使用 GTID 進行故障轉移

l 使用 GTID 時，迴圈拓撲中的故障轉移

在發生故障的 master 的 slave 上，透過發出單個 CHANGE MASTER TO 語句繞過該 master 。每個伺服器忽略或應用從拓撲中的其他伺服器複製的事務，具體取決於是否看到了該事務的 GTID 。

l 非迴圈拓撲中的故障轉移

臨時將新 master 配置為最新 slave 的 slave ，直到該新 master 變為最新。

雖然 GTID 可以防止源自單個伺服器上的事件重複，但是它們不會防止源自不同伺服器上的衝突操作，如 “複雜拓撲”中所述。在故障轉移後將應用程式重新連線到伺服器時，您必須小心不要產生此類衝突。

1.4. 複製的過濾規則

過濾器是應用於 master 或 slave 的伺服器選項：

l master 寫入二進位制日誌時應用 binlog-* 過濾器。

l slave 讀取中繼日誌時應用 replicate-* 過濾器。

基於以下各項選擇要複製的事件：

Ø 資料庫：

– replicate-do-db, binlog-do-db

– replicate-ignore-db, binlog-ignore-db

Ø 表：

– replicate-do-table, replicate-wild-do-table

– replicate-ignore-table,replicate-wild-ignore-table

當環境中的不同伺服器用於不同目的時，使用過濾規則。例如，專用於顯示 Web 內容的伺服器不需要從 master 複製重新進貨資訊或工資記錄，而專用於生成關於銷售量的管理報表的伺服器不需要儲存 Web 內容或市場營銷副本。

過濾規則具有按順序應用的複雜優先順序規則：

Ø 資料庫過濾器先於表過濾器應用。

Ø 表萬用字元過濾器 *-wild-* 在不使用萬用字元的那些過濾器之後應用。

Ø *-do-* 過濾器先於各個 *-ignore-* 過濾器應用。

使用多個過濾器時要謹慎。由於應用過濾器的順序複雜，非常容易出錯。因為過濾器控制要複製的資料，所以很難從此類錯誤中恢復。因此，不要混用不同型別的過濾器。例如，如果使用 replicate-wild-* ，則不要使用任何非 wild replicate-* 過濾器。

有關複製規則及其執行順序的完整論述，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/replication-rules.html

1.5. MySQL 實用程式 -- 複製工具

許多實用程式對於複製特別有用：

a) mysqldbcopy ：將資料庫以及複製配置從源伺服器複製到目標伺服器； mysqldbcopy 實用程式接受選項 --rpl ，其在目標伺服器上執行 CHANGE MASTER TO 語句。它接受以下值：

Ø master ：目標伺服器變為源的 slave 。

Ø slave ：源已經是其他 master 的 slave 。目標伺服器從源複製該 master 資訊並變為同一 master 的 slave ；

b) mysqldbcompare ：比較兩個資料庫來查詢區別並建立指令碼來同步這兩個資料庫；

c) mysqlrpladmin ：管理複製拓撲；

– 在 master 故障後故障轉移到最佳 slave

– 切換以升級指定的 slave

– 啟動、重置或停止所有 slave

d) mysqlfailover ：持續監視 master ，並執行故障轉移到最佳可用 slave ；

mysqlfailover 實用程式透過 ping 操作定期檢查 master 狀態。期間間隔和 ping 操作都是可配置的。它使用 GTID 確保新的 master 在變為 master 時是最新的，透過以下操作實現此項：從候選列表中選擇新 master （如果列表中沒有可行的候選項，則從所有 slave 中選擇），將其配置為所有其他 slave 的 slave 來收集所有未完成事務，最後使其成為新 master 。 mysqlrpladmin 的 failover 命令在選擇新 master 時執行相似的臨時重新配置。如果 master 失敗，您還可以執行執行狀況監視而不執行自動重新配置。

e) mysqlrplcheck ：檢查 master 和 slave 之間進行復制的先決條件，包括：

– 二進位制日誌記錄

– 具有適當許可權的複製使用者

– server_id 衝突

– 可能導致複製衝突的各種設定

f) mysqlreplicate ：在兩個伺服器之間啟動複製，報告不匹配警告訊息；

g) mysqlrplshow ：顯示 master 與 slave 之間的複製拓撲或者遞迴顯示整個拓撲；

mysqlrplshow 實用程式顯示如下所示的複製拓撲：

# Replication Topology Graph

localhost:3311 (MASTER)

|

+--- localhost:3312 - (SLAVE + MASTER)

|

+--- localhost:3313 - (SLAVE + MASTER)

|

+--- localhost:3311 <--> (SLAVE)

在前面示例中，埠 3311 處的伺服器出現兩次：一次作為 master ，一次作為 slave 。 <--> 符號指示拓撲內的迴圈。

1.6. 非同步複製

什麼是非同步複製？ slave 請求二進位制日誌並應用其內容。 slave 通常滯後於 master 。 master 不關注 slave 何時應用日誌。 master 繼續執行而不等待 slave 。

MySQL 使用其預設配置進行復制過程中， master 從客戶機接受更改事件，提交那些事件並將其寫入二進位制日誌。在單獨的執行緒中， master 將二進位制日誌流處理到連線的 slave 。因為 master 提交更改而不等待任何 slave 的響應，所以這稱為非同步複製。

最重要的是，這意味著在 master 嚮應用程式報告成功時 slave 尚未應用事務。通常，這不是個問題。但是，如果在 master 提交事務之後而該事務複製到任何 slave 之前，該 master 出現故障並且資料丟失，則該事務將丟失，即使應用程式已經向使用者報告成功也是如此。

如果 master 在提交事務之前等待所有 slave 應用其更改，則複製稱為是同步的。雖然 MySQL 複製不是同步的，但 MySQL Cluster 在內部使用同步複製來確保整個群集中的資料一致性，並且 MySQL 客戶機請求是同步的，因為客戶機在向伺服器發出查詢後等待伺服器響應。

1.7. 半同步複製

半同步複製是指 master 在提交每個事務後執行阻止直到至少一個 slave 提交該事務，僅當 master 和至少一個 slave 提交事務時客戶機才收到“成功”，如果發生超時則切換到非同步複製。

要啟用半同步複製，請在 master 和至少一個 slave 上安裝外掛。使用以下外掛：

l rpl_semi_sync_master （在 master 上）

l rpl_semi_sync_slave （在 slave 上）

還必須啟用以下選項：

l rpl_semi_sync_master_enabled （在 master 上）

l rpl_semi_sync_slave_enabled （在 slave 上）

如果在 master 上啟用半同步複製，它的行為是非同步的，直到至少一個半同步 slave 連線。

在半同步複製過程中， master 在提交事務後執行阻止，直到至少一個半同步 slave 確認它也已經收到了該事務。這意味著在 master 嚮應用程式報告成功時至少一個 slave 已經收到了每個事務。如果 master 在提交事務後出現故障且資料丟失並且應用程式已經向使用者報告了成功，則該事務還存在於至少一個 slave 上。

半同步複製需要您在效能和資料完整性之間進行權衡。使用半同步複製時事務速度比使用非同步複製時慢，因為 master 在提交之前等待 slave 響應。

每個事務花費的額外時間至少是它為以下項花費的時間：

Ø TCP/IP 往返以將提交傳送到 slave

Ø slave 在其中繼日誌中記錄提交

Ø master 等待 slave 確認該提交

這意味著對於物理上位於同一位置、透過快速網路通訊的伺服器，半同步複製最有效。如果 master 在超時期間內沒有收到半同步 slave 的響應，該 master 仍提交該事務，但恢復為非同步模式。可以使用 rpl_semi_sync_master_timeout 變數配置超時，其包含以毫秒為單位的值。預設值是 10000 ，表示十秒。

1.8. 複製日誌

1) 檢視二進位制日誌記錄

二進位制日誌包含資料和模式更改及其時間戳，基於語句或基於行的日誌記錄，可以用於從備份的時間點恢復、從備份的完全恢復以及複製； binlog 在下列情況下輪轉： MySQL 重新啟動、其達到 max_binlog_size 設定的最大大小、執行 FLUSH LOGS 語句； binlog 可以各種方式進行檢查，查詢後設資料使用 SHOW BINARY LOGS 、 SHOW MASTER STATUS ，查詢內容使用 mysqlbinlog ；

2) 複製日誌

中繼日誌： MySQL 自動管理中繼日誌檔案集，在其已經重放了所有事件時刪除這些檔案並在當前檔案超過最大中繼日誌檔案大小時建立新檔案。中繼日誌使用與二進位制日誌相同的格式儲存；可以使用 mysqlbinlog 檢視那些日誌。 slave 維護索引檔案來跟蹤中繼日誌檔案。

預設情況下，中繼日誌檔名為 <host_name>-relay-bin.<nnnnnn> ，索引檔名為 <host_name>-relay-bin.index 。要使伺服器配置不受將來可能的主機名更改影響，請透過設定以下選項來進行這些更改： --relay-log 和 --relay-log-index 。

slave 狀態日誌： slave 儲存關於如何連線到 master 的資訊以及 master 的二進位制日誌和 slave 的中繼日誌的最近複製的日誌座標。有兩個此類日誌：

l master 資訊：此日誌包含關於 master 的資訊，包括主機名和埠、用於連線的憑證以及 master 二進位制日誌的最近下載的日誌座標等資訊。

l 中繼日誌資訊：此日誌包含中繼日誌的最近執行的座標以及 slave 的已複製事件落後於 master 的那些事件的秒數。

slave 狀態日誌儲存在檔案 master.info 和 relay-log.info 中（預設情況下），如果儲存在表中，則為 mysql 資料庫中的 slave_master_info 和 slave_relay_log_info 表；

3) 故障安全 (Crash-Safe) 複製

l 二進位制日誌記錄是故障安全的

MySQL 僅記錄完成事件或事務。使用 sync-binlog 提高安全性。預設情況下，值是，表示作業系統根據其內部規則向檔案寫入。將 sync-binlog 設定為 1 ，強制作業系統在每個事務之後寫入檔案，或者將其設定為任何較大數值以在該數量的事務之後寫入。

l 將 slave 狀態日誌儲存在 TABLE 中是故障安全的

slave 狀態日誌選項： master-info-repository 和 relay-loginfo-repository ，可能值為 FILE （預設值）和 TABLE 。 TABLE 是故障安全的。

預設情況下， slave 狀態日誌儲存在檔案中。使用 master-info-file 和 relay-log-info-file 選項設定檔名。預設情況下，檔名是 master.info 和 relay-log.info ，都儲存在資料目錄中。

如果將 slave 狀態日誌儲存在檔案中，在記錄事件與在狀態日誌中記錄該事件的日誌座標之間的某點會發生故障。伺服器在此類事件後重新啟動時，狀態檔案和二進位制日誌將不一致，恢復變得困難。

複製使用事務儲存引擎（例如 InnoDB ）的資料時，透過將 master-info-repository 和 relay-log-info-repository 的值從 FILE （預設值）更改為 TABLE ，來將狀態日誌儲存在事務表中以提高效能並確保故障安全複製。該表稱為 slave_master_info 和 slave_relay_log_info ，都儲存在 mysql 資料庫中，並且都使用 InnoDB 引擎確保事務完整性和故障安全行為。

有關 slave 狀態日誌的更多資訊，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/slave-logs-status.html

1.9. 複製執行緒

1) 複製執行緒介紹

slave 連線到 master 時

l master 建立 Binlog 轉儲執行緒：從二進位制日誌讀取事件並將其傳送到 slaveI/O 執行緒

l slave 至少建立兩個執行緒

— slaveI/O 執行緒：從 master 的 Binlog 轉儲執行緒讀取事件並將其寫入 slave 的中繼日誌

— slaveSQL 執行緒：在單執行緒 slave 上應用中繼日誌事件，在多執行緒 slave 上的工作執行緒之間分配中繼日誌事件

— slave 工作執行緒：在多執行緒 slave 上應用中繼日誌事件

MySQL 在 master 和 slave 上建立執行緒來執行復制工作。 slave 成功連線到 master 時， master 啟動稱為 Binlog 轉儲執行緒的複製 master 執行緒，如果 slave 配置為使用自動定位協議 (CHANGE MASTER TO MASTER_AUTO_POSITION) ，則該執行緒顯示為“ Binlog 轉儲 GTID ”。在 slave 已連線時，此執行緒會在二進位制日誌內的事件到達時將其傳送到 slave 。 master 為每個連線的 slave 建立一個 Binlog 轉儲執行緒。

預設情況下，每個 slave 啟動兩個執行緒，分別稱為 slaveI/O 執行緒和 slaveSQL 執行緒。

l slaveI/O 執行緒連線到 master ，將更新從 Binlog 轉儲執行緒讀取到本地中繼日誌中。

l slaveSQL 執行緒執行中繼日誌中的事件。

由於單個 SQL 程式處理中繼日誌而被稱為單執行緒的預設配置會導致 slave 滯後，其中 slave 落後於 master ：如果 master 具有多個客戶機連線則並行應用更改，但是序列執行其二進位制日誌中的所有事件。 slave 在單個執行緒中順序執行這些事件，在高流量環境中或者當 slave 的硬體不足以處理單個執行緒中的通訊流量時，這會成為瓶頸。

2) 多執行緒 slave

MySQL 支援多執行緒 slave 以避免單執行緒 slave 引起的一些滯後。如果在 slave 上將 slave_parallel_workers 變數設定為大於零的值，它會建立該數量的工作執行緒。在這種情況下， slaveSQL 執行緒不直接執行事件。相反，它按資料庫將事件分配給工作執行緒。這使多執行緒 slave 在要在多個資料庫中複製資料的環境中特別有用。

如果工作執行緒執行並行操作的順序與其在 master 上的執行順序不同，此選項可能導致資料庫之間不一致，所以您必須確保不同資料庫中的資料是獨立的。由單個執行緒複製的一個資料庫中的資料將保證是一致的。

如果將 slave_parallel_workers 變數設定為大於複製中所用資料庫數量的值，一些工作執行緒將保持空閒。類似地，如果僅在伺服器中複製一個資料庫，則在您的環境中使用多執行緒 slave 不會帶來什麼好處。

3) slave 執行緒管理

l 啟停 slave 執行緒：

START SLAVE;

STOP SLAVE;

l 單獨控制執行緒：

START SLAVE IO_THREAD;

STOP SLAVE SQL_THREAD;

l 啟動執行緒直到指定的條件：

START SLAVE UNTIL SQL_AFTER_MTS_GAPS;

START SLAVE IO_THREAD UNTIL SQL_AFTER_GTIDS = 0ed18583-47fd-11e2-92f3-0019b944b7f7:338;

透過 START SLAVE 和 STOP SLAVE 語句啟動和停止 slave 的 SQL 和 I/O 執行緒。如果沒有引數，這些語句控制這兩個執行緒。透過指定 IO_THREAD 或 SQL_THREAD 作為引數來單獨控制每個執行緒。例如，可以透過在 slave 上發出以下語句來臨時停止 slave 查詢 master ： STOP SLAVE IO_THREAD;

啟動 slave 執行緒時，可以選擇線上程到達 UNTIL 子句中指定的某點時將其停止。可以使用日誌座標、 GTID 集或特殊 SQL_AFTER_MTS_GAPS 值來指定此點，用於多執行緒 slave 。例如，以下語句在 slave 上啟動 SQL 執行緒，直到它執行中繼日誌中的指定 GTID ，在該點它將停止：

START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = 0ed18583-47fd-11e2-92f3-0019b944b7f7:338

注： slave 工作執行緒無法單獨啟動或停止，因為 SQL 執行緒控制其工作。啟動或停止 SQL 執行緒來控制工作執行緒。

有關使用 START SLAVE 的完整詳細資訊，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/start-slave.html

1.10. 監控複製

1) 查詢 slave 狀態

mysql> SHOW SLAVE STATUS\G

***************** 1. row *********************

Slave_IO_State: Queueing master event to the relay log

...

Master_Log_File: mysql-bin.005

Read_Master_Log_Pos: 79

Relay_Log_File: slave-relay-bin.005

Relay_Log_Pos: 548

Relay_Master_Log_File: mysql-bin.004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Exec_Master_Log_Pos: 3769

...

Seconds_Behind_Master: 8

Slave_*_Running ： Slave_IO_Running 和 Slave_SQL_Running 列標識 slave 的 I/O 執行緒和 SQL 執行緒當前正在執行、未執行還是正在執行但尚未連線到 master 。可能值分別為 Yes 、 No 或 Connecting 。

master 日誌座標： Master_Log_File 和 Read_Master_Log_Pos 列標識 master 二進位制日誌中 I/O 執行緒已經傳輸的最近事件的座標。這些列可與您在 master 上執行 SHOW MASTER STATUS 時顯示的座標進行比較。如果 Master_Log_File 和 Read_Master_Log_Pos 的值遠遠落後於 master 上的那些值，這表示 master 與 slave 之間事件的網路傳輸存在延遲。

中繼日誌座標： Relay_Log_File 和 Relay_Log_Pos 列標識 slave 中繼日誌中 SQL 執行緒已經執行的最近事件的座標。這些座標對應於 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列標識的 master 二進位制日誌中的座標。

如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的輸出遠遠落後於 Master_Log_File 和 Read_Master_Log_Pos 列（表示 I/O 執行緒的座標），這表示 SQL 執行緒（而不是 I/O 執行緒）中存在延遲。即，它表示複製日誌事件快於執行這些事件。

在多執行緒 slave 上， Exec_Master_Log_Pos 包含任何未提交事務之前最後一點的位置。這並不始終與中繼日誌中的最近日誌位置相同，因為多執行緒 slave 在不同資料庫上執行事務的順序可能與二進位制日誌中顯示的順序不同。

Seconds_Behind_Master ：此列提供中繼日誌中 SQL 執行緒執行的最近事件的時間戳（在 master 上）與 slave 的實際時間之間的秒數。當 master 並行處理大量事件而 slave 必須序列處理這些事件時，或者當高通訊流量期間 slave 的硬體不足以處理與 master 可以處理的相同量的事件時，通常會發生這種型別的延遲。如果 slave 未連線到 master ，此列為 NULL 。注：此列不顯示 I/O 執行緒中的延遲或者 master 的事件網路傳輸中的延遲。

2) 複製 slaveI/O 執行緒狀態

本節介紹的 slaveI/O 執行緒狀態是指 SHOW PROCESSLIST 輸出的 State 列和 SHOW SLAVE STATUS 顯示的 Slave_IO_State 列的值；常見的 I/O 執行緒狀態如下：

l Connecting to master ：執行緒正嘗試連線到 master 。

l Waiting for master to send event ：執行緒已經連線到 master 並且正在等待二進位制日誌事件到達。如果 master 處於空閒狀態，此狀態可能持續很長時間。如果等待持續 slave_read_timeout 秒，則發生超時。此時，執行緒考慮斷開連線並嘗試重新連線。

l Queueing master event to the relay log ：執行緒已經讀取事件並且正在將其複製到中繼日誌，從而 SQL 執行緒可以處理該事件。

l Waiting to reconnect after a failed binlog dump request ：如果二進位制日誌轉儲請求失敗（由於斷開連線），執行緒在其休眠時轉入此狀態，然後定期嘗試重新連線。可以使用 --master-connect-retry 選項指定重試之間的時間間隔。

l Reconnecting after a failed binlog dump request ：執行緒正嘗試重新連線到 master 。

l Waiting to reconnect after a failed master event read ：讀取時出錯（由於斷開連線）。執行緒在嘗試重新連線之前休眠 master-connect-retry 秒。

l Reconnecting after a failed master event read ：執行緒正嘗試重新連線到 master 。重新建立連線後，狀態變為 Waiting for master to send event 。

l Waiting for the slave SQL thread to free enough relay log space ：該狀態顯示正在使用非零 relay_log_space_limit 值，並且中繼日誌已經增加得足夠大，以至其合併大小超過了此值。 I/O 執行緒正在等待，直到 SQL 執行緒透過處理中繼日誌內容以便可以刪除一些中繼日誌檔案來釋放足夠空間。

3) 複製 slaveSQL 執行緒狀態

slaveSQL 執行緒和工作執行緒的 State 列中顯示的最常見狀態是：

l Waiting for the next event in relay log ：這是 Reading event from the relay log 之前的初始狀態。

l Reading event from the relay log ：執行緒已經從中繼日誌中讀取了事件，從而可以處理該事件。

l Making temp file ：執行緒正在執行 LOAD DATA INFILE 語句，並且正在建立包含資料的臨時檔案， slave 從該資料中讀取行。

l Slave has read all relay log; waiting for the slave I/O thread to update it ：執行緒已經處理了中繼日誌檔案中的所有事件，現在正在等待 I/O 執行緒將新事件寫入中繼日誌。

l Waiting until MASTER_DELAY seconds after master executed event ： SQL 執行緒已經讀取事件，但是正等待 slave 延遲結束。透過 CHANGE MASTER TO 的 MASTER_DELAY 選項設定此延遲。

l Waiting for an event from Coordinator ：在多執行緒 slave 上， 工作執行緒 正等待協調執行緒向工作佇列分配作業。

1.11. 排除 MySQL 複製故障

如何排除 MySQL 複製故障步驟？

a) 檢視錯誤日誌，錯誤日誌可以為您提供足夠資訊來確定和更正複製中的問題。

b) 在 master 上發出 SHOW MASTER STATUS 語句，如果位置值非零則啟用日誌記錄。

c) 確認 master 和 slave 都具有唯一的非零 server_id 值。 master 和 slave 必須具有不同的 server_id 。

d) 在 slave 上發出 SHOW SLAVE STATUS 命令。如果 slave 執行正常， Slave_IO_Running 和 Slave_SQL_Running 顯示 Yes 。 Last_IO_Error 和 Last_SQL_Error 顯示 IO 和 SQL 執行緒的最新錯誤訊息。 SHOW SLAVE STATUS 語句在與複製過程中遇到的最新錯誤相關的多個欄位中返回資訊。

l Last_IO_Error 、 Last_SQL_Error ：分別導致 I/O 執行緒或 SQL 執行緒停止的最新錯誤的錯誤訊息。在正常複製過程中，這些欄位是空的。如果發生錯誤並導致訊息顯示在以上任一欄位中，則錯誤值也顯示在錯誤日誌中。

l Last_IO_Errno 、 Last_SQL_Errno ：與分別導致 I/O 執行緒或 SQL 執行緒停止的最新錯誤關聯的錯誤編號。在正常複製過程中，這些欄位包含編號。

l Last_IO_Error_Timestamp 、 Last_SQL_Error_Timestamp ：分別導致 I/O 執行緒或 SQL 執行緒停止的最新錯誤的時間戳，格式為 YYMMDD HH:MM:SS 。在正常複製過程中，這些欄位是空的。

錯誤日誌包含關於複製開始時間的資訊以及關於複製過程中發生的所有錯誤的資訊。例如，下面的序列顯示成功開始，然後在複製 slave 上已經存在的使用者時失敗：

2012-12-16 11:13:21 8449 [Note] Slave I/O thread: connected to

master 'repl@127.0.0.1:3313',replication started in log 'FIRST'

at position 4

2012-12-16 11:13:21 8449 [Note] Slave SQL thread initialized,

starting replication in log 'mysql-bin.000002' at position 108,

relay log './slave-relay-bin.000003' position: 408

...

2012-12-16 16:42:53 8449 [ERROR] Slave SQL: Error 'Operation

CREATE USER failed for 'user'@'127.0.0.1'' on query. Default

database: 'world_innodb'. Query: 'CREATE USER ‘user'@'127.0.0.1'

IDENTIFIED BY PASSWORD

'*2447D497B9A6A15F2776055CB2D1E9F86758182F'', Error_code: 1396

2012-12-16 16:42:53 8449 [Warning] Slave: Operation CREATE USER

failed for 'user'@'127.0.0.1' Error_code: 1396

2012-12-16 16:42:53 8449 [ERROR] Error running query, slave SQL

thread aborted. Fix the problem, and restart the slave SQL

thread with "SLAVE START". We stopped at log 'mysql-bin.000002'

position 460

下面的序列顯示 I/O 執行緒從 master 的二進位制日誌讀取時失敗：

2012-12-16 16:48:13 8823 [Note] Slave I/O thread: connected to master

'repl@127.0.0.1:3313',replication started in log 'mysql-bin.000002' at position 1172

2012-12-16 16:48:15 8823 [ERROR] Read invalid event from master: 'Found invalid event

in binary log', master could be corrupt but a more likely cause of this is a bug

2012-12-16 16:48:15 8823 [ERROR] Slave I/O: Relay log write failure: could not queue

event from master, Error_code: 1595

2012-12-16 16:48:15 8823 [Note] Slave I/O thread exiting, read up to log 'mysqlbin.

000003', position 4

e) 使用 mysqlrplcheck 確保伺服器滿足複製的先決條件；

f) 在 master 和 slave 上發出 SHOW PROCESSLIST 命令，檢視 Binlog 轉儲、 I/O 和 SQL 執行緒的狀態。 slave 上的 SHOW PROCESSLIST I/O 執行緒指示正連線到 master ：

Ø 確認用於在 master 上進行復制的使用者的許可權；

Ø 確認主機名和埠對於 master 是正確的；

Ø 確認尚未在 master 或 slave 上禁用網路（使用 --skip-networking 選項）；

Ø 嘗試 ping master 來確認 slave 可以訪問 master ；

g) 對於突然停止工作的 slave ，檢查最近複製的語句。如果操作由於約束問題或其他錯誤而失敗， SQL 執行緒將停止，錯誤日誌包含導致 SQL 執行緒停止的事件；然後檢視已知複製限制（ http://dev.mysql.com/doc/refman/5.6/en/replication-features.html ）；確認 slave 資料尚未被直接修改（在複製之外）。

有關已知複製限制和可能影響複製的其他問題的更多資訊，請參閱《 MySQL 參考手冊》，網址為： http://dev.mysql.com/doc/refman/5.6/en/replication-features.html

MySQL入門--複製技術介紹

MySQL 複製技術介紹

相關文章