一、主從復制搭建方法參考

MySQL5.6 數據庫主從（Master/Slave）同步安裝與配置詳解

二、Mysql 主從復制的常用拓撲結構

2.1、一主一從

是最基礎的復制結構，用來分擔之前單臺數據庫服務器的壓力，可以進行讀寫分離。

2.2、一主多從

一臺 Slave 承受不住讀請求壓力時，可以添加多臺，進行負載均衡，分散讀壓力。

還可以對多臺 Slave 進行分工，服務于不同的系統，例如一部分 Slave 負責網站前臺的讀請求，另一部分 Slave 負責后臺統計系統的請求。

因為不同系統的查詢需求不同，對 Slave 分工后，可以創建不同的索引，使其更好的服務于目標系統。

2.3、雙主復制

Master 存在下線的可能，例如故障或者維護，需要把 Slave 切換為 Master。

在原來的 Master 恢復可用后，由于其數據已經不是最新的了，不能再做主，需要做為 Slave 添加進來。

那么就需要對其重新搭建復制環境，需要耗費一定的工作量。

雙主結構就是用來解決這個問題的，互相將對方作為自己的 Master，自己作為對方的 Slave 來進行復制，但對外來講，還是一個主和一個從。

當 主Master 下線時，備Master 切換為 主Master，當原來的 主Master 上線后，因為他記錄了自己當前復制到對方的什么位置了，就會自動從之前的位置開始重新復制，不需要人為地干預，大大提升了效率。

2.4、級聯復制

當直接從屬于 Master 的 Slave 過多時，連到 Master 的 Slave IO 線程就比較多，對 Master 的壓力是很大的。

級聯結構就是通過減少直接從屬于 Master 的 Slave 數量，減輕 Master 的壓力，分散復制請求，從而提高整體的復制效率。

2.5、雙主級聯

級聯復制結構解決了 Slave 過多導致的瓶頸問題，但還是有單主結構中切換主時的維護問題。

那么為了解決這個問題，就可以加入上面的雙主結構。

在必要時，可以再對 Slaves 進行分級。

Mysql 的復制結構有很多種方式，復制的最大問題是數據延時，選擇復制結構時需要根據自己的具體情況，并評估好目標結構的延時對系統的影響。

三、Mysql 主從復制過程及原理

3.1、Binary Log 簡單介紹

因為Binlog dump 線程操作的文件是bin-log 日志文件，并且實現主從復制在主服務器上主要依靠bin-log日志文件，所以我們簡單介紹一下bin-log日志文件。

3.2、原理

MySQL的Replication（英文為復制）是一個多MySQL數據庫做主從同步的方案，特點是異步復制，廣泛用在各種對MySQL有更高性能、更高可靠性要求的場合。與之對應的是另一個同步技術是MySQL Cluster，但因為MySQL Cluster配置比較復雜，所以使用者較少。

MySQL Replication 就是從服務器拉取主服務器上的 二進制日志文件，然后再將日志文件解析成相應的SQL語句在從服務器上重新執行一遍主服務器的操作，通過這種方式來保證數據的一致性。

MySQL的Replication是一個異步復制的過程（mysql5.1.7以上版本分為異步復制和半同步兩種模式），它是從一個Mysql instance（instance英文為實例）(我們稱之為Master)復制到另一個Mysql instance(我們稱之slave)。

3.3、三個線程

在master與slave之間實現整個復制過程主要由三個線程來完成：

1、Slave SQL thread線程，在slave端

2、Slave I/O thread線程，在slave端

3、Binlog dump thread線程(也可稱為IO線程)，在master端

注意：如果一臺主服務器配兩臺從服務器那主服務器上就會有兩個Binlog dump 線程，而每個從服務器上各自有兩個線程。

要實現MySQL的Replication，首先必須打開master端的binlog (mysql-bin.xxxxxx)日志功能，否則無法實現mysql的主從復制。因為mysql的整個主從復制過程實際上就是：slave端從master端獲取binlog日志，然后再在自己身上完全順序的執行該日志中所記錄的各種SQL操作。有關具體如何開啟mysql的binlog日志功能，請大家自己在網上搜。

3.4、主從復制流程

MySQL主從復制的基本交互過程，如下：

1、slave端的IO線程連接上master端，并請求從指定binlog日志文件的指定pos節點位置(或者從最開始的日志)開始復制之后的日志內容。

2、master端在接收到來自slave端的IO線程請求后，通知負責復制進程的IO線程，根據slave端IO線程的請求信息，讀取指定binlog日志指定pos節點位置之后的日志信息，然后返回給slave端的IO線程。該返回信息中除了binlog日志所包含的信息之外，還包括本次返回的信息在master端的binlog文件名以及在該binlog日志中的pos節點位置。

3、slave端的IO線程在接收到master端IO返回的信息后，將接收到的binlog日志內容依次寫入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并將讀取到的master端的binlog文件名和pos節點位置記錄到master-info（該文件存slave端）文件中，以便在下一次讀取的時候能夠清楚的告訴master“我需要從哪個binlog文件的哪個pos節點位置開始，請把此節點以后的日志內容發給我”。

4、slave端的SQL線程在檢測到relaylog文件中新增內容后，會馬上解析該log文件中的內容。然后還原成在master端真實執行的那些SQL語句，并在自身按順豐依次執行這些SQL語句。這樣，實際上就是在master端和slave端執行了同樣的SQL語句，所以master端和slave端的數據完全一樣的。

以上mysql主從復制交互過程比較拗口，理解起來也比較麻煩，我簡化了該交互過程。如下：

1、master在執行sql之后，記錄二進制log文件（bin-log）。

2、slave連接master，并從master獲取binlog，存于本地relay-log中，然后從上次記住的位置起執行SQL語句，一旦遇到錯誤則停止同步。

從以上mysql的Replication原理可以看出：

主從間的數據庫不是實時同步，就算網絡連接正常，也存在瞬間主從數據不一致的情況。

如果主從的網絡斷開，則從庫會在網絡恢復正常后，批量進行同步。

如果對從庫進行修改數據，那么如果此時從庫正在在執行主庫的bin-log時，則會出現錯誤而停止同步，這個是很危險的操作。所以一般情況下，我們要非常小心的修改從庫上的數據。

一個衍生的配置是雙主、互為主從配置，只要雙方的修改不沖突，則可以工作良好。

如果需要多主庫的話，可以用環形配置，這樣任意一個節點的修改都可以同步到所有節點。

3.5、整體過程就是：

MySQL 復制基于主服務器在二進制日志中跟蹤所有對數據庫的更改(更新、刪除等等)。每個從服務器從主服務器接收主服務器已經記錄到其二進制日志的保存的更新，以便從服務器可以對其數據拷貝執行相同的更新。

將主服務器的數據拷貝到從服務器的一個途徑是使用LOAD DATA FROM MASTER語句。請注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存儲引擎的主服務器上工作。并且，該語句將獲得全局讀鎖定。

MySQL 使用3個線程來執行復制功能,其中1個在主服務器上，另兩個在從服務器上。當發出START SLAVE時，從服務器創建一個I/O線程，以連接主服務器并讓它發送記錄在其二進制日志中的語句。

主服務器創建一個線程，即I/O線程，將二進制日志中的內容發送到從服務器。該線程可以識別為主服務器上SHOW PROCESSLIST的輸出中的Binlog Dump線程。

從服務器I/O線程讀取主服務器Binlog Dump線程發送的內容并將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日志。

第3個線程是SQL線程，是從服務器創建用于讀取中繼日志并執行日志中包含的更新。

有多個從服務器的主服務器創建為每個當前連接的從服務器創建一個線程；每個從服務器有自己的I/O和SQL線程。

四、MySQL支持的復制類型及其優缺點

bin-log 日志文件有兩種格式，一種是Statement-Based，另一種是Row-Based。

（１）：基于語句的復制（Statement-Based）： 在主服務器上執行的SQL語句，在從服務器上執行同樣的語句。MySQL默認采用基于語句的復制，效率比較高。 一旦發現沒法精確復制時，會自動選著基于行的復制。

（２）：基于行的復制（Row-Based）：把改變的內容復制過去，而不是把命令在從服務器上執行一遍. 從mysql5.0開始支持

（３）：混合類型的復制: 默認采用基于語句的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制。

4.1、Statement-Based優點和缺點分析

優點

1. bin-log日志包含了描述數據庫操作的事件，但是這些事件包含的情況只是對數據庫進行改變的操作，例如 insert、update、create、delete等操作。相反對于select、desc等類似的操作并不會去記錄，并且它記錄的是語句，所以相對于Row-Based來說這樣會占用更少的存儲空間。
2. 因為bin-log日志文件記錄了所有的改變數據庫的語句，所以此文件可以作為以后的數據庫的審核依據

缺點

1. 不安全，并不是所有的改變數據的語句都會被記錄復制。任何的非確定性的行為都是很難被記錄復制的。
2. 例如：對于delete 或者update語句，如果使用了limit但是并沒有 order by ，這就屬于非確定性的語句，就不會被記錄
3. 對于沒有索引條件的update語句，必須鎖定更多的數據，降低了數據庫的性能。
4. insert……select 語句同樣也需要鎖定大量的數據，對數據庫的性能有所損耗。
5. 獲取更詳細的信息可以參考官方文檔——Statement-Based的優點和缺點。

4.2、Row-Based優點和缺點分析

優點

1. 所有的改變都會被復制，這是最安全的復制方式
2. 對于 update、insert……select等語句鎖定更少的行
3. 此種方式和大多數的數據庫系統一樣，所以了解其他的系統的人員可以很容易的轉到mysql

缺點

1. 使用不方便，我們不能通過bin-log日志文件查看什么語句執行了，也無從知道在從服務器上接收到什么語句，我們只能看到什么數據改變了
2. 因為記錄的是數據，所以說bin-log日志文件占用的存儲空間要比Statement-based大。
3. 對于數據量大的操作其花費的時間有更長

獲取更詳細的信息可以參考官方文檔——Row-Based的優點和缺點

bin-log日志文件默認的格式為Statement-Based，如果想改變其格式在開啟服務的時候使用—binlog-format選項，其具體命令如下

mysqld_safe –user=msyql –binlog-format=格式 &

四、主服務器流程分析

4.1、主服務器線程 Binlog dump thread

Binlog dump 線程是當有從服務器連接的時候由主服務器創建，其大致工作過程經歷如下幾個階段：

首先bin-log日志文件加鎖，然后讀取更新的操作，讀取完畢以后將鎖釋放掉，最后將讀取的記錄發送給從服務器。

我們可以使用如下的命令來查看該線程的信息

mysql> SHOW PROCESSLIST\G 

以我的系統為例，因為我這系統中是一臺主服務器和兩臺從服務器，所以會列出兩條Binlog dump線程的信息 

*************************** 1. row ***************************  
Id: 2  
User: repuser  
Host: 192.168.144.131:41544  
db: NULL  
Command: Binlog Dump  
Time: 54  
State: Master has sent all binlog to slave; waiting for binlog to be updated  
Info: NULL  
*************************** 2. row ***************************  
Id: 3  
User: repuser  
Host: 192.168.144.132:40888 
db: NULL  
Command: Binlog Dump  
Time: 31  
State: Master has sent all binlog to slave; waiting for binlog to be updated  
Info: NULL  

上述字段中的state字段會有以下幾種狀態：

1. Sending binlog event to slave

表示Binlog dump 線程已經讀取完binlog日志中更新的event，現在正在發送給從服務器

2. Finished reading one binlog; switching to next binlog

表示Binlog dump 線程已經讀取完一個binlog日志，現在正在打開下一個binlog日志讀取來發送給從服務器

3. Master has sent all binlog to slave; waiting for binlog to be updated

這就是上面我們看到的state的值，表示Binlog dump 線程已經讀取完所有的binlog日志文件，并且將其發送給了從服務器。現在處于空閑狀態，正在等待讀取有新的操作的binlog日志文件

4. Waiting to finalize termination

這個狀態持續的很短暫，我們幾乎看不到。當線程停止的時候顯示此狀態。

上述幾個狀態就是一次主從復制過程中Binlog dump 線程所經歷的狀態，如果我們是在測試的環境中，上述1、2、4狀態我們幾乎是看不到的，因為它執行的很快。

在主從系統中主服務器上的一個主要的文件就是bin-log日志，該線程操作的文件也是此日志文件，因此這是我們需要在配置文件my.cnf 中打開bin-log日志的原因，使用此文件來記錄我們的更新操作。 

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

還有一點需要注意，在上面已經說過，但是在這里覺得有必要再重復一遍，就是有多少個從服務器連接主服務器上就有多少個Binlog dump 線程。

bin-log日志文件管理

對于bin-log日志文件，其默認的名稱為 mysql-bin.xxxxxx。而且還有一個索引文件mysql-bin.index，其中記錄了當前所有的bin-log日志文件。

對于新的主服務器只有一個bin-log日志文件 mysql-bin.000001。此時所有的操作都有這個文件來記錄，如果我們想更換bin-log日志文件，可以使用如下命令 

Mysql>flush logs; 

此時會創建一個mysql-bin.000002文件來記錄以后的操作。除了使用上述命令以外，當bin-log日志文件達到其最大值的時候也會產生新的bin-log日志文件

其文件最大值和文件名包括索引文件的名稱可以使用 –max_binlog_size、–log-bin和—log-bin-index 選項來改變，具體命令如下

mysqld_safe –user=msyql –max_binlog_size=文件長度 –log-bin=新的日志文件名稱 –log-bin-index=新索引文件名 &

對于主服務器來說，總起來一句話：主服務器針對于每一個從服務器都創建一個Binlog dump線程，用來讀取bin-log日志中更新的操作將其發送給從服務器，發送完畢以后繼續等待bin-log日志是否有更新。

五、從服務器流程分析

在主服務器探究這篇文章中我們提到過，在一次主從復制過程中需要用到三個線程：Binlog dump 線程、Slave I/O 線程和Slave SQL線程，其中Binlog dump 線程在主服務器上面，剩下的兩個線程是在從服務器上面工作的。

這兩個線程在從服務器上面的工作流程如下圖所示：

對于這兩個線程隨著從服務器開啟slave而產生 

mysql> START SLVAE; 

使用 

Mysql> SHOW SLAVE STATUS\G 

查看這兩個線程情況 

……  
Master_Log_File: mysql-bin.000003  
Read_Master_Log_Pos: 1264  
Relay_Log_File: localhost-relay-bin.000002  
Relay_Log_Pos: 878  
Relay_Master_Log_File: mysql-bin.000003  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
……  

上面結果中的 Slave_IO_Running:Yes和Slave_SQL_Running:Yes表示這兩個線程正在運行。

然后我們在從服務器上面使用命令 

mysql> SHOW PROCESSLIAT\G 

顯示如下結果（記為 結果一） 

*************************** 1. row ***************************  
Id: 22  
User: system user  
Host:  
db: NULL  
Command: Connect  
Time: 4  
State: Waiting for master to send event  
Info: NULL  
*************************** 2. row ***************************  
Id: 23  
User: system user  
Host:  
db: NULL  
Command: Connect  
Time: 4  
State: Slave has read all relay log; waiting for the slave I/O thread to update it  
Info: NULL  

從State信息可以看出Id 22是I/O線程，正在等待主服務器發送更新的內容；Id 23是Slave SQL線程，已經讀取了relay log 文件中所有更新的內容，正在等待I/O線程更新該文件。

使用命令停止slave機制

mysql> STOP SLVAE; 

然后我們再次查看會發現結果如下

……  
Master_Log_File: mysql-bin.000003  
Read_Master_Log_Pos: 1264  
Relay_Log_File: localhost-relay-bin.000002  
Relay_Log_Pos: 878  
Relay_Master_Log_File: mysql-bin.000003  
Slave_IO_Running: No  
Slave_SQL_Running: No  
……  

說明這兩個線程已經停止了運行。此時再次使用 SHOW PROCESSLIST\G命令，則沒有結果顯示

5.1、Slave I/O線程

Slave I/O 線程去連接主服務器的Binlog dump 線程并要求其發送binlog日志中記錄的更新的操作，然后它將Binlog dump 線程發送的數據拷貝到從服務器上（也就是本地）的文件relay log中。

當然要查看此線程是否運行，除了上面介紹的方法，還可以使用

mysql> SHOW SLAVE LIKE ‘Slave_running’; 

這時如果出現下面的結果說明該線程正在運行 

+-----------------+-------------------+  
| Variable_name | Value |  
+-----------------+-------------------+  
| Slave_running | ON |  
+-----------------+-------------------+  

在上述結果一中我們可以看到1.row即是Slave I/O線程的信息，其State: Waiting for master to send event 表示正在等待主服務器發送內容。當然State不止這一個值，它還有其它的值，下面列出了State的所有的值

1. Waiting for master update

在連接到主服務器之前的初始狀態

2. Connecting to master

該線程正在連接主服務器，當然如果我們的網絡環境優異的話，此狀態我們幾乎是看不到的

3. Checking master version

這個狀態發生的時間也非常短暫，該狀態在該線程和主服務器建立連接之后發生。

4. Registering slave on master

在主服務器上面注冊從服務器，每當有新的從服務器連接進來以后都要在主服務器上面進行注冊

5. Requesting binlog dump

向主服務器請求binlog日志的拷貝

6. Waiting to reconnect after a failed binlog dump request

如果5中失敗，則該線程進入睡眠狀態，此時State就是這個值，等待著去定期重新連接主服務器，那這個周期的大小可以通過CHANGE MASTER TO 來指定

7. Reconnecting after a failed binlog dump request

去重新連接主服務器

8. Waiting for master to send event

此值就是我們上述結果所顯示的，正常情況下我們查看的時候一般都是這個值。其具體表示是這個線程已經和主服務器建立了連接，正在等待主服務器上的binlog 有更新，如果主服務器的Binlog dump線程一直是空閑的狀態的話，那此線程會等待很長一段時間。當然也不是一直等待下去，如果時間達到了slave_net_timeout規定的時間，會發生等待超時的情況，在這種情況下I/O線程會重新去連接主服務器

9. Queueing master event to the relay log

該線程已經讀取了Binlog dump線程發送的一個更新事件并且正在將其拷貝到relay log文件中

10. Waiting to reconnect after a failed master event read

當該線程讀取Binlog dump 線程發送的更新事件失敗時，該線程進入睡眠狀態等待去重新連接主服務器，這個等待的時間默認是60秒，當然這個值也可以通過CHANGE MASTER TO來設置

11. Reconnecting after a failed master event read

該線程去重新連接主服務器，當連接成功以后，那這個State的值會改變為 Waiting for master to send event

12. Waiting for the Slave SQL thread to free enough relay log space

relay log space的大小是通過relay_log_space_limit來設定的，隨著relay logs變得越來越大所有的大小合起來會超過這個設定值。這時該線程會等待SQL線程釋放足夠的空間刪除一些relay log文件

13. Waiting for slave mutex on exit

當線程停止的時候會短暫的出現該情況

以上就是State可能會出現的值，以及都是在什么情況下出現。

5.2、Slave SQL線程

Slave SQL線程是在從服務器上面創建的，主要負責讀取由Slave I/O寫的relay log文件并執行其中的事件。

在上述結果一中2.row即是Slave SQL線程的信息，同樣有一個State表示該線程的當前狀態。

下面也列出了State所有可能出現的情況。

1. Waiting for the next event in relay log

該狀態是讀取relay log之前的初始狀態

2. Reading event from the relay log

該狀態表示此線程已經在relay log中讀取了一個事件準備執行

3. Making temp file

該狀態表示此線程正在執行LOAD_DATA_INFILE并且正在創建一個臨時文件來保存從服務器將要讀取的數據

4. Slave has read all relay log; waiting for the slave I/O thread to update it

該線程已經處理完了relay log中的所有事件，現在正在等待slave I/O線程更新relay log文件

5. Waiting for slave mutex on exit

當線程停止的時候會短暫的出現該情況

上面是對從服務器上的兩個線程的簡單的介紹，在運行過程中我們會發現這兩個線程都離不開的文件就是relay log文件，下面我們簡單介紹一下relay log文件。

5.3、relay log文件

relay log 和 主服務器上的bin log很相似，都是一系列的文件，這些文件包括那些包含描述數據庫改變的操作事件的文件和索引文件，這個索引文件是relay logs文件的名稱集合。

relay log 文件和 bin log文件一樣，也是二進制文件，不能直接查看，需要使用mysql自帶工具mysqlbinlog查看。

] # mysqlbinlog mysql安裝路徑/data/relay-log文件

當然其索引文件的內容我們是可以直接使用 vim查看的。

對于relay logs 文件的名稱的命名規則默認使用的是 host_name-relay-bin.nnnnnn，以我的系統來說，其文件名默認為localhost-relay-bin.000001。對于索引文件的命名規則為host_name-relay-bin.index，同樣在我的系統中的名稱為localhost-relay-bin.index。這兩個名稱是可以通過—relay-log 和 –relay-log-index來改變的，其使用方式如下：

mysqld_safe –user=mysql –relay-log=文件名 –relay-log-index=新索引文件名 &

在這里如果改變這兩個名稱的話，可能會引起‘不能打開relay log’文件和‘在初始化relay log 過程中不能發現目標log’等錯誤。這也算是mysql設計的一個bug，沒有什么好的解決辦法，如果我們不想使用默認的文件名稱的話，唯一的辦法就是我們可以預料到從服務器的主機名稱可能在將來會發生改變，在開始初始化從服務器的時候就使用以上兩個選項指定文件名，這樣就可以使文件名不再依賴于服務器的主機名。

對于這些relay log文件并不是一直在增加的，當Slave SQL線程執行完一個relay log文件中所有的事件并且不再需要它的時候會把改relay log文件刪除。由于是Slave SQL線程來做這些事情，所以也沒有什么明確的規則來指定如何刪除relay log文件

以上的所有內容大概描述了主從復制系統中從服務器的主要工作流程。

六、如何提高Mysql主從復制的效率？

MySQL的主從復制，實際上就是Master記錄自己的執行日志binlog，然后發送給Slave，Slave解析日志并執行，來實現數據復制。對于復制效率，binlog的大小是非常重要的因素，因為它涉及了I/O和網絡傳輸

主從復制涉及到了兩端：master/slave，看下這兩端可以如何優化

（1）master 端

master端有2個參數可以控制

Binlog_Do_DB : 設定哪些數據庫需要記錄Binlog

Binlog_Ignore_DB : 設定哪些數據庫不要記錄Binlog

這兩項很重要，指定必要數據庫，忽略不需要復制的數據庫，可以減少binlog的大小，提高了I/O效率，加快網絡傳輸。

但這兩項也同樣比較危險，需要謹慎使用，因為可能會有主從數據不一致和復制出錯的風險。

因為MySQL判斷是否須要復制某個Event，不是根據產生該Event的語句所在的數據庫，而是根據執行時所在的默認數據庫，也就是登錄時指定的數據庫，或運行“USE DATABASE”中所指定的數據庫。

如果執行語句中明確指定了數據庫名稱，而這個數據庫是被指定不記錄Binlog的，那么這個語句在slave中執行時就會出錯。

例如

garbage 庫是被指定不記錄日志的

product 庫是指定要記錄日志的

執行下面的語句

use product;

delete from garbage.junk;

delete語句會被發送給slave，但slave中沒有garbage庫，所以執行時報錯，復制失敗

（2）slave 端

slave端有6個參數可以控制

Replicate_Do_DB : 設定須要復制的數據庫，多個DB用逗號分隔

Replicate_Ignore_DB : 設定可以忽略的數據庫

Replicate_Do_Table : 設定須要復制的Table

Replicate_Ignore_Table : 設定可以忽略的Table

Replicate_Wild_Do_Table : 功能同Replicate_Do_Table，但可以帶通配符來進行設置

Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table，可帶通配符設置

slave端的配置優化效果要明顯小于master端的，因為master端日志都寫完了，日志也傳過來了

但這幾個參數可以幫助我們減少日志的應用量，因為設置了過濾，實際寫入的sql數量變少了，slave端的復制也就加快了。