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 語句結果中相對應的字段值一樣，如下：

從mysql 4.1開始，文件內容還包括了ssl選項：

line description

`relay-log.info` 文件由sql線程來更新。文件的內容和執行 show slave status 語句結果中相對應的字段值一樣：

備份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 的值。