MySQL主備複制原理、實作及異常處理

MySQL支援三種複制方式：基于行(Row)的複制、基于語句(Statement)的複制和混合類型(Mixed)的複制。

基于語句的複制早在3.23版本中就存在，而基于行的複制方式在5.1版本中才被加進來。這兩種方式都是通過在主庫上記錄二進制日志、在備庫重放日志的方式來實作異步的資料複制。

混合類型的複制：預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。

複制通常不會增加主庫的開銷，主要是啟用二進制日志帶來的開銷，但出于備份或及時從崩潰中恢複的目的，這點開銷也是必要的。除此之外，每個備庫也會對主庫增加一些負載（例如網絡I/O開銷），尤其當備庫請求從主庫讀取舊的二進制日志檔案時，可能會造成更高的I/O開銷。另外鎖競争也可能阻礙事務的送出。最後，如果是從一個高吞吐量的主庫上複制到多個備庫，喚醒多個複制線程發送事件的開銷将會累加。

mysql主備複制實作分成三個步驟：

master将改變記錄到二進制日志(binary log)中（這些記錄叫做二進制日志事件，binary log events，可以通過show binlog events進行檢視）；

slave将master的binary log events拷貝到它的中繼日志(relay log)；

slave重做中繼日志中的事件，将改變反映它自己的資料。

以上隻是概述，實際上每一步都很複雜：

第一步是在主庫上記錄二進制日志。在每次準備送出事務完成資料更新前，主庫将資料更新的事件記錄到二進制日志中。MySQL會按事務送出的順序而非每條語句的執行順序來記錄二進制日志。在記錄二進制日志後，主庫會告訴存儲引擎可以送出事務了。

下一步，備庫将主庫的二進制日志複制到其本地的中繼日志中。首先，備庫會啟動一個工作線程。稱為I/O線程，I/O線程跟主庫建立一個普通的用戶端連接配接，然後在主庫上啟動一個特殊的二進制轉儲（binlog dump）線程，這個二進制轉儲線程會讀取主庫上二進制日志中的事件。它不會對事件進行輪詢。如果該線程追趕上了主庫，它将進入睡眠狀态，直到主庫發送信号量通知其有新的事件産生時才會被喚醒，備庫I/O線程會将接收到的事件記錄到中繼日志中。

備庫的SQL線程執行最後一步，該線程從中繼日志中讀取事件并在備庫執行，進而實作備庫資料的更新。當SQL線程趕上I/O線程時，中繼日志通常已經在系統緩存中，是以中繼日志的開銷很低。SQL線程執行的事件也可以通過配置選項來決定是否寫入其自己的二進制日志中，它對我們稍後提到的場景非常有用。

複制賬戶事實上隻需要有主庫上的REPLICATION SLAVE權限，并不一定需要每一端伺服器都有REPLICATION CLIENT權限，那麼為什麼我們要把這兩種權限給主/備庫都賦予呢？這有兩個原因：

1. 用來監控和管理複制的賬号需要REPLICATION CLIENT權限，并且針對這兩種目的使用同一個賬号更加容易。

2. 如果在主庫上建立了賬号，然後從主庫将資料克隆到備庫上時，備庫也就設定好了——變成主庫所需要的配置。這樣後續有需要可以友善地交換主備庫的角色。

如果無腦式配置可以：

關停Master伺服器，将Master中的資料拷貝到B伺服器中，使得Master和slave中的資料同步，并且確定在全部設定操作結束前，禁止在Master和slave伺服器中進行寫操作，使得兩資料庫中的資料一定要相同！

備注：文中采用的案例中主備庫都有5個schema：

主庫的/etc/my.cnf配置(主機host:10.198.197.73)

備庫上也需要在/ect/my.cnf進行配置(備機host:10.198.197.60)

server_id 是必須的，而且唯一。slave沒有必要開啟二進制日志，但是在一些情況下，必須設定，例如，如果slave為其它slave的master，必須設定 bin_log。在這裡，我們開啟了二進制日志，而且顯示的命名(預設名稱為hostname，但是，如果hostname改變則會出現問題)。

relay_log配置中繼日志，log_slave_updates表示slave将複制事件寫進自己的二進制日志(後面會看到它的用處)。

有 些人開啟了slave的二進制日志，卻沒有設定log_slave_updates，然後檢視slave的資料是否改變，這是一種錯誤的配置。是以，盡量 使用read_only，它防止改變資料(除了特殊的線程)。但是，read_only并是很實用，特别是那些需要在slave上建立表的應用。

接 下來就是讓slave連接配接master，并開始重做master二進制日志中的事件。你不應該用配置檔案進行該操作，而應該使用CHANGE MASTER TO語句，該語句可以完全取代對配置檔案的修改，而且它可以為slave指定不同的master，而不需要停止伺服器。如下：

MASTER_LOG_POS的值為0，因為它是日志的開始位置。

你可以用SHOW SLAVE STATUS語句檢視slave的設定是否正确：

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No表明slave還沒有開始複制過程。日志的位置為4而不是0，這是因為0隻是日志檔案的開始位置，并不是日志位置。實際上，MySQL知道的第一個事件的位置是4。

為了開始複制，你可以運作：

運作show slave status檢視輸出結果：

在這裡主要是看:

slave的I/O和SQL線程都已經開始運作，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被擷取并執行了。如果你在master上進行修改，你可以在slave上看到各種日志檔案的位置的變化，同樣，你也可以看到資料庫中資料的變化。

（如果此時Slave_SQL_Running=No，可以參考下一節“異常情況處理”進行解決）

你可檢視master和slave上線程的狀态。在master上，你可以看到slave的I/O線程建立的連接配接(Binlog Dump)：

在master上輸入show processlist\G;

同樣，在備庫也可以看到兩個線程，一個是I/O線程，一個是SQL線程（Connect）：

在上一小節中在start slave之後進行show slave status就出現了想要的結果——“Slave_SQL_Running=Yes”.但是有些時候，卻不是這樣的：

可以看到Slave_SQL_Running=No，那麼該怎麼解決呢？

解決方案1

程式可能在slave上進行了寫操作，也可能是slave機器重新開機後事務復原造成的。

如果是事務復原造成的，可以：

最後通過show slave status進行檢視。

解決方案2

首先停掉slave服務：

到master上檢視主機狀态：

然後到slave伺服器上執行手動同步：

在master上的Schema Name: canal_test中有一個perosn的表，表結構如下：

表中有一條記錄：

（注意此時slave中的資料是一樣的）

往master上插入一條資料，之後檢視：

可以看到master中成功插入了一條資料，之後可以同樣在slave中輸入select * from person來檢視，如果結果master和slave相同，那麼恭喜你主備複制已經成功了。

《Optimization, Backups and Replication High Performance MySQL》Baron schwartz, Peter Zaitsev, Vadim Tkachenko.

<a href="http://www.open-open.com/lib/view/open1373874692544.html">MySQL主從複制原理以及架構</a>

<a href="http://wenku.baidu.com/link?url=nBWk9L5sZDCDgMhVKcTjVxugYV1zrUvDt1KjtAu3KY5ql0M6ux9-4TjvT9BHFnYhV1n8rnQk1n2sy_3Pp21Z8t1y7cj4xQ4XwOU5_KqYR7K">slave_sql_running_no解決方法</a>