MySQL同步(一) 基礎知識

摘自《MySQL手冊》
 

6 MySQL 同步

同步功能在MySQL 3.23.15就開始引進了，它可以把一個MySQL伺服器上的資料複製到另一個伺服器上去。本章描述了MySQL的各種複製特性。介紹了同步的概念，如何設定同步伺服器，以及可用伺服器的參照。還提供了一系列的常見問題及其答案，疑難解答。

"14.6 Replication Statements"中介紹了同步相關的SQL語句文法。

我們建議經常訪問"http://www.mysql.com"經常閱讀本章的最新內容。同步功能一直在改進，我們經常把這部分的手冊更新到當前的最新內容。

6.1 同步介紹

MySQL 3.23.15及更高的版本支援單向同步。一個伺服器作為master(主伺服器)，一個或者多個伺服器作為slave(從伺服器)。master伺服器把更新的內容寫到二進位日誌(binary log或binlog)中，並且維護了一個索引檔案來記錄日誌迴圈的情況。這些日誌中的更新部分會被發送到slave伺服器。一個slave串連到master之後，它通知master最後一次成功累加式更新的日誌位置。slave會找出所有從那個時刻開始的更新操作，然後阻塞並等待master發送新的更新操作。

如果想要做一個同步伺服器鏈的話，slave同時也可以作為master。

注意，啟用同步後，所有要同步的更新操作都必須在master上執行。否則，必須注意不要造成使用者在master上的更新和在slave上的更新引起衝突。

單向同步的好處是穩健，高速，系統易管理：

有了master/slave機制後，就更穩健了。當master上發生問題時，可以把slave作為備用切換過去。

可以在slave和master之間分擔一些查詢，這就能加速回應時間。SELECT 查詢就可以在slave上執行以減少master的負載。更新資料的語句則要放在mater上執行以保持master和slave的同步。當非更新操作佔多數時，負載平衡就很有效了，不過這隻是普通情況而言。

另一個好處是可以在slave上備份資料，無需幹擾master。備份資料時master照樣繼續運作。詳情請看"5.7.1 Database Backups"。

6.2 同步機制實現概述

MySQL同步機制基於master把所有對資料庫的更新、刪除 等)都記錄在二進位日誌裡。因此，想要啟用同步機制，在master就必須啟用二進位日誌。詳情請看"5.9.4 The Binary Log"。

每個slave接受來自master上在二進位日誌中記錄的更新操作，因此在slave上執行了這個操作的一個拷貝。

應該非常重要地意識到，二進位日誌只是從啟用二進位日誌開始的時刻才記錄更新操作的。所有的slave必須在啟用二進位日誌時把master上已經存在的資料拷貝過來。如果運行同步時slave上的資料和master上啟用二進位日誌時的資料不一致的話，那麼slave同步就會失敗。

把master上的資料拷貝過來的方法之一實在slave上執行 LOAD DATA FROM MASTER 語句。不過要注意，LOAD DATA FROM MASTER 是從MySQL 4.0.0之後才開始可以用的，而且只支援master上的 MyISAM 類型表。同樣地，這個操作需要一個全域的讀鎖，這樣的話傳送記錄到slave的時候在master上就不會有更新操作了。當實現了自由鎖表熱備份時(在MySQL 5.0中)，全域讀鎖就沒必要了。

由於有這些限制，因此我們建議只在master上相關資料比較小的時候才執行 LOAD DATA FROM MASTER 語句，或者在master上允許一個長時間的讀鎖。由於每個系統之間 LOAD DATA FROM MASTER 的速度各不一樣，一個比較好的衡量規則是每秒能拷貝1MB資料。這隻是的粗略的估計，不過master和slave都是奔騰700MHz的機器且用100MBit/s網路連接時就能達到這個速度了。

slave上已經完整拷貝master資料後，就可以串連到master上然後等待處理更新了。如果master當機或者slave串連斷開，slave會定期嘗試串連到master上直到能重連並且等待更新。重試的時間間隔由 --master-connect-retry 選項來控制，它的預設值是60秒。

每個slave都記錄了它關閉時的日誌位置。msater是不知道有多少個slave串連上來或者哪個slave從什麼時候開始更新。

6.3 同步實現細節

MySQL同步功能由3個線程(master上1個，slave上2個)來實現。執行 START SLAVE 語句後，slave就建立一個I/O線程。I/O線程串連到master上，並請求master發送二進位日誌中的語句。master建立一個線程來把日誌的內容發送到slave上。這個線程在master上執行 SHOW PROCESSLIST 語句後的結果中的 Binlog Dump 線程便是。slave上的I/O線程讀取master的 Binlog Dump 線程發送的語句，並且把它們拷貝到其資料目錄下的中繼日誌(relay logs)中。第三個是SQL線程，salve用它來讀取中繼日誌，然後執行它們來更新資料。

如上所述，每個mster/slave上都有3個線程。每個master上有多個線程，它為每個slave串連都建立一個線程，每個slave只有I/O和SQL線程。

在MySQL 4.0.2以前，同步只需2個線程(master和slave各一個)。slave上的I/O和SQL線程合并成一個了，它不使用中繼日誌。

slave上使用2個線程的優點是，把讀日誌和執行分開成2個獨立的任務。執行任務如果慢的話，讀日誌任務不會跟著慢下來。例如，如果slave停止了一段時間，那麼I/O線程可以在slave啟動後很快地從master上讀取全部日誌，儘管SQL線程可能落後I/O線程好幾的小時。如果slave在SQL線程沒全部執行完就停止了，但I/O線程卻已經把所有的更新日誌都讀取並且儲存在本地的中繼日誌中了，因此在slave再次啟動後就會繼續執行它們了。這就允許在master上清除二進位日誌，因為slave已經無需去master讀取更新日誌了。

執行 SHOW PROCESSLIST 語句就會告訴我們所關心的master和slave上發生的情況。

下例說明了 SHOW PROCESSLIST 結果中的3個線程是什麼樣的。這是在MySQL 4.0.15及更新上執行 SHOW PROCESSLIST 的結果，State 欄位的內容已經比舊版本顯示的更有意義了。

在master上，SHOW PROCESSLIST 的結果如下：

mysql> SHOW PROCESSLIST\G

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

Id: 2

User: root

Host: localhost:32931

db: NULL

Command: Binlog Dump

Time: 94

State: Has sent all binlog to slave; waiting for binlog to

be updated

Info: NULL

在這裡，線程2是為一個slave串連建立的。結果表明所有未完成的更新日誌已經都發送到slave了，master正等待新的更新日誌發生。

在slave上，SHOW PROCESSLIST 的結果如下：

mysql> SHOW PROCESSLIST\G

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

Id: 10

User: system user

Host:

db: NULL

Command: Connect

Time: 11

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 11

User: system user

Host:

db: NULL

Command: Connect

Time: 11

State: Has read all relay log; waiting for the slave I/O

thread to update it

Info: NULL

這表明線程10是I/O線程，它正串連到master上；線程11是SQL線程，它執行中繼日誌中的更新操作。現在，這2個線程都處於空閑狀態，正等待新的更新日誌。

注意，Time 欄位的值告訴我們slave上的日誌比master晚了多久。詳情請看"6.9 Replication FAQ"。

6.3.1 Master 同步線程狀態

以下列出了master的 Binlog Dump 線程 State 欄位中最常見的幾種狀態。如果在master上沒有 Binlog Dump 線程，那麼同步就沒有在運行。也就是說，沒有slave串連上來。

Sending binlog event to slave

事件是由二進位日誌構成，一個事件通常由更新語句加上其他資訊。線程讀取到一個事件並正發送到slave上。

Finished reading one binlog; switching to next binlog

讀取完了一個二進位日誌，正切換到下一個。

Has sent all binlog to slave; waiting for binlog to be updated

已經讀取完全部未完成更新日誌，並且全部都發送到slave了。它處於空閑狀態，正等待在master上執行新的更新操作以在二進位日誌中產生新的事件，然後讀取它們。

Waiting to finalize termination

當前線程停止了，這個時間很短。

6.3.2 Slave的I/O線程狀態

以下列出了slave的I/O線程 State 欄位中最常見的幾種狀態。從MySQL 4.1.1開始，這個狀態在執行 SHOW SLAVE STATUS 語句結果的 Slave_IO_State 欄位也會出現。這意味著可以只執行 SHOW SLAVE STATUS 語句就能瞭解到更多的資訊。

Connecting to master

該線程證嘗試串連到master上。

Checking master version

確定串連到master後出現的一個短暫的狀態。

Registering slave on master

確定串連到master後出現的一個短暫的狀態。

Requesting binlog dump

確定串連到master後出現的一個短暫的狀態。該線程向master發送一個請求，告訴它要請求的二進位檔案以及開始位置。

Waiting to reconnect after a failed binlog dump request

如果二進位日誌轉儲(binary log dump)請求失敗了(由於串連斷開)，該線程在休眠時進入這個狀態，並定期重連。重連的時間間隔由 --master-connect-retry 選項來指定。

Reconnecting after a failed binlog dump request

該線程正嘗試重連到master。

Waiting for master to send event

已經串連到master，正等待它發送二進位日誌。如果master閑置時，這個狀態可能會持續較長時間，如果它等待超過 slave_read_timeout 秒，就會發生逾時。這時，它就會考慮中斷連線，然後嘗試重連。

Queueing master event to the relay log

已經讀取到一個事件，正把它拷貝到中繼日誌中以備SQL線程處理。

Waiting to reconnect after a failed master event read

讀日誌時發生錯誤(由於串連斷開)。該線程在重連之前休眠 master-connect-retry 秒。

Reconnecting after a failed master event read

正嘗試重連到master。當串連確定後，狀態就變成 Waiting for master to send event。

Waiting for the slave SQL thread to free enough relay log space

relay_log_space_limit 的值非零，中繼日誌的大小總和超過這個值了。I/O線程等待SQL線程先處理中繼日誌然後刪除它們以釋放足夠的空間。

Waiting for slave mutex on exit

當前線程停止了，這個時間很短。

6.3.3 Slave的SQL線程狀態

以下列出了slave的SQL線程 State 欄位中最常見的幾種狀態：

Reading event from the relay log

從中繼日誌裡讀到一個事件以備執行。

Has read all relay log; waiting for the slave I/O thread to update it

已經處理完中繼日誌中的全部事件了，正等待I/O線程寫入更新的日誌。

Waiting for slave mutex on exit

當前線程停止了，這個時間很短。

SQL線程的 State 欄位有時候也可能是一個SQL語句。這意味著它從中繼日誌中讀取到一個事件了，從中提取出SQL語句，並執行它。

6.3.4 中繼日誌及狀態檔案

預設地，中繼日誌的名字格式為 `host_name-relay-bin.nnn`，host_name 是伺服器的主機名稱，nnn 是序號。中繼日誌是根據順序的序號來建立的，從 000001 (MySQL 4.0 及更舊是 001)開始。slave上用一個索引檔案來跟蹤當前正在使用的中繼日誌。預設的中繼日誌索引檔案名稱是 `host_name-relay-bin.index`。預設地，這個檔案位於slave的資料檔案目錄下。預設檔案名稱可以根據的系統選項 --relay-log 和 --relay-log-index 來替換。詳情請看"6.8 Replication Startup Options"。

中繼日誌和二進位日誌的格式一樣，因此也可以用 mysqlbinlog 來讀取。當SQL線程讀取完中繼日誌中的全部事件後就不再需要它了，會自動刪除它。中繼日誌沒有顯式的刪除機制，因為SQL線程會自動關注這個。不過，從MySQL 4.0.14開始，執行 FLUSH LOGS 的話就會輪轉(rotate)中繼日誌，會讓SQL線程刪除它們。

在下列條件中會建立一個新的中繼日誌：

slave啟動後，I/O線程第一次啟動(在MySQL 5.0中，每次I/O線程啟動後都會建立一個中繼日誌，而不只是第一次啟動時)。

重新整理日誌時；例如，執行 FLUSH LOGS 語句或運行 mysqladmin flush-logs 命令(從 MySQL 4.0.14開始才會建立新中繼日誌)。

當前的中繼日誌大小太大了；"太大了"是這麼判斷的：

max_relay_log_size, 如果 max_relay_log_size > 0 的話

max_binlog_size, 如果 max_relay_log_size = 0 或 MySQL 低於 4.0.14

slave會在資料檔案目錄下建立兩個額外的檔案。它們是狀態檔案，名字預設為 `master.info` and `relay-log.info`。它們的內容跟執行 SHOW SLAVE STATUS 語句的結果類似。詳情請看"14.6.2 SQL Statements for Controlling Slave Servers"。由於是磁碟上的檔案，它們在slave關閉後還會留著。下一次slave啟動時，就會讀取這兩個檔案來判斷從master讀取到二進位日誌的什麼位置了，處理中繼日誌到什麼位置了。

`master.info` 檔案由來I/O線程更新。在MySQL 4.1以前，檔案的內容和執行 SHOW SLAVE STATUS 語句結果中相對應的欄位值一樣，如下：

Line	Description
1	Master_Log_File
2	Read_Master_Log_Pos
3	Master_Host
4	Master_User
5	Password (not shown by SHOW SLAVE STATUS)
6	Master_Port
7	Connect_Retry

從MySQL 4.1開始，檔案內容還包括了SSL選項：

Line Description

Line	Description
1	Number of lines in the file
2	Master_Log_File
3	Read_Master_Log_Pos
4	Master_Host
5	Master_User
6	Password (not shown by SHOW SLAVE STATUS)
7	Master_Port
8	Connect_Retry
9	Master_SSL_Allowed
10	Master_SSL_CA_File
11	Master_SSL_CA_Path
12	Master_SSL_Cert
13	Master_SSL_Cipher
14	Master_SSL_Key

`relay-log.info` 檔案由SQL線程來更新。檔案的內容和執行 SHOW SLAVE STATUS 語句結果中相對應的欄位值一樣：

Line	Description
1	Relay_Log_File
2	Relay_Log_Pos
3	Relay_Master_Log_File
4	Exec_Master_Log_Pos

備份slave資料時，要把這兩個檔案也備份起來，和中繼日誌一道。想要恢複slave時就用得到它們了。如果丟失了中繼日誌，但是 `relay-log.info` 檔案還存在，那麼就可以判斷出SQL線程執行了多少master二進位日誌。然後執行 CHANGE MASTER TO 語句，帶上 MASTER_LOG_FILE 和 MASTER_LOG_POS 選項告訴slave要從master的二進位日誌哪個位置重新讀取。當然了，這要求master上相關的二進位日誌都還留著。

如果slav打算同步 LOAD DATA INFILE 語句，那麼也要備份對應目錄下的任何 `SQL_LOAD-*` 檔案。這可以在 LOAD DATA INFILE 被中斷後繼續保持同步。這個目錄由 --slave-load-tmpdir 選項來指定。預設地，如果沒有指定的話，它的值就是變數 tmpdir 的值。

引用：

http://database.ccidnet.com/art/1105/20060810/791491_1.html