MySQL主從複製原理及必備知識總結

本文主要是關於mysql主從複製的一些原理以及必備知識的梳理，關於mysql主從環境的搭建可參考另一篇部落格：MySQL主從複製環境部署【http://blog.itpub.net/31015730/viewspace-2153251/】

Mysql複製概念說明

Mysql內建的複製功能是構建大型、高效能應用程式的基礎。將Mysql的資料分佈到多個系統上去，這種分佈的機制，是通過將Mysql的某一臺主機的資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器將更新寫入二進位制日誌檔案，並維護檔案的一個索引以跟蹤日誌迴圈。這些日誌可以記錄傳送到從伺服器的更新。當一個從伺服器連線主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

需要注意的是：

在進行mysql複製時，所有對複製中的表的更新必須在主伺服器上進行。否則必須要小心，以避免使用者對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的衝突。

1、Mysql支援那些複製
(1)基於語句的複製： 在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。 
(2)基於行的複製：把改變的內容複製過去，而不是把命令在從伺服器上執行一遍. 從mysql5.0開始支援
(3)混合型別的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。

2、Mysql複製能解決的問題
（1）資料分佈 (Data distribution )
（2）負載平衡(load balancing)
（3）資料備份(Backups) ，保證資料安全
（4）高可用性和容錯行(High availability and failover)
（5）實現讀寫分離，緩解資料庫壓力

3、Mysql主從複製原理

master伺服器將資料的改變記錄二進位制binlog日誌，當master上的資料發生改變時，則將其改變寫入二進位制日誌中；salve伺服器會在一定時間間隔內對master二進位制日誌進行探測其是否發生改變，如果發生改變，則開始一個I/OThread請求master二進位制事件，同時主節點為每個I/O執行緒啟動一個dump執行緒，用於向其傳送二進位制事件，並儲存至從節點本地的中繼日誌中，從節點將啟動SQL執行緒從中繼日誌中讀取二進位制日誌，在本地重放，使得其資料和主節點的保持一致，最後I/OThread和SQLThread將進入睡眠狀態，等待下一次被喚醒。

注意：
1--master將操作語句記錄到binlog日誌中，然後授予slave遠端連線的許可權（master一定要開啟binlog二進位制日誌功能；通常為了資料安全考慮，slave也開啟binlog功能）。
2--slave開啟兩個執行緒：IO執行緒和SQL執行緒。其中：IO執行緒負責讀取master的binlog內容到中繼日誌relay log裡；SQL執行緒負責從relay log日誌裡讀出binlog內容，並更新到slave的資料庫裡，這樣就能保證slave資料和master資料保持一致了。
3--Mysql複製至少需要兩個Mysql的服務，當然Mysql服務可以分佈在不同的伺服器上，也可以在一臺伺服器上啟動多個服務。
4--Mysql複製最好確保master和slave伺服器上的Mysql版本相同（如果不能滿足版本一致，那麼要保證master主節點的版本低於slave從節點的版本）
5--master和slave兩節點間時間需同步

Mysql複製的流程圖如下：

如上圖所示：

第一部分就是master記錄二進位制日誌。在每個事務更新資料完成之前，master在二日誌記錄這些改變。MySQL將事務序列的寫入二進位制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位制日誌完成後，master通知儲存引擎提交事務。

第二部分就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作執行緒——I/O執行緒。I/O執行緒在master上開啟一個普通的連線，然後開始binlog dump process。Binlog dump process從master的二進位制日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O執行緒將這些事件寫入中繼日誌。

SQL slave thread（SQL從執行緒）處理該過程的最後一步。SQL執行緒從中繼日誌讀取事件，並重放其中的事件而更新slave的資料，使其與master中的資料一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。
   此外，在master中也有一個工作執行緒：和其它MySQL的連線一樣，slave在master中開啟一個連線也會使得master開始一個執行緒。複製過程有一個很重要的限制——複製在slave上是序列化的，也就是說master上的並行更新操作不能在slave上並行操作。

4、Mysql複製的模式
（1）主從複製：主庫授權從庫遠端連線，讀取binlog日誌並更新到本地資料庫的過程；主庫寫資料後，從庫會自動同步過來（從庫跟著主庫變）；
（2）主主複製：主從相互授權連線，讀取對方binlog日誌並更新到本地資料庫的過程；只要對方資料改變，自己就跟著改變；

5、Mysql主從複製的優點
（1）在從伺服器可以執行查詢工作(即我們常說的讀功能)，降低主伺服器壓力;（主庫寫，從庫讀，降壓）
（2）在從主伺服器進行備份，避免備份期間影響主伺服器服務;（確保資料安全）
（3）當主伺服器出現問題時，可以切換到從伺服器。（提升效能）

6、Mysql主從複製工作流程細節
（1）MySQL支援單向、非同步複製，複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。MySQL複製基於主伺服器在二進位制日誌中跟蹤所有對資料庫的更改(更新、刪除等等)。因此，要進行復制，必須在主伺服器上啟用二進位制日誌。每個從伺服器從主伺服器接收主伺服器上已經記錄到其二進位制日誌的儲存的更新。當一個從伺服器連線主伺服器時，它通知主伺服器定位到從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，並在本機上執行相同的更新。然後封鎖並等待主伺服器通知新的更新。從伺服器執行備份不會干擾主伺服器，在備份過程中主伺服器可以繼續處理更新。

（2）MySQL使用3個執行緒來執行復制功能，其中兩個執行緒(Sql執行緒和IO執行緒)在從伺服器，另外一個執行緒(IO執行緒)在主伺服器。

當發出START SLAVE時，從伺服器建立一個I/O執行緒，以連線主伺服器並讓它傳送記錄在其二進位制日誌中的語句。主伺服器建立一個執行緒將二進位制日誌中的內容傳送到從伺服器。該執行緒可以即為主伺服器上SHOW PROCESSLIST的輸出中的Binlog Dump執行緒。從伺服器I/O執行緒讀取主伺服器Binlog Dump執行緒傳送的內容並將該資料拷貝到從伺服器資料目錄中的本地檔案中，即中繼日誌。第3個執行緒是SQL執行緒，由從伺服器建立，用於讀取中繼日誌並執行日誌中包含的更新。在從伺服器上，讀取和執行更新語句被分成兩個獨立的任務。當從伺服器啟動時，其I/O執行緒可以很快地從主伺服器索取所有二進位制日誌內容，即使SQL執行緒執行更新的遠遠滯後。

7、知識點總結

主從資料完成同步的過程：

1）在Slave 伺服器上執行sart slave命令開啟主從複製開關，開始進行主從複製。

2）此時，Slave伺服器的IO執行緒會通過在master上已經授權的複製使用者許可權請求連線master伺服器，並請求從執行binlog日誌檔案的指定位置（日誌檔名和位置就是在配置主從複製服務時執行change master命令指定的）之後開始傳送binlog日誌內容

3）Master伺服器接收到來自Slave伺服器的IO執行緒的請求後，其上負責複製的IO執行緒會根據Slave伺服器的IO執行緒請求的資訊分批讀取指定binlog日誌檔案指定位置之後的binlog日誌資訊，然後返回給Slave端的IO執行緒。返回的資訊中除了binlog日誌內容外，還有在Master伺服器端記錄的IO執行緒。返回的資訊中除了binlog中的下一個指定更新位置。

4）當Slave伺服器的IO執行緒獲取到Master伺服器上IO執行緒傳送的日誌內容、日誌檔案及位置點後，會將binlog日誌內容依次寫到Slave端自身的Relay Log（即中繼日誌）檔案（Mysql-relay-bin.xxx）的最末端，並將新的binlog檔名和位置記錄到master-info檔案中，以便下一次讀取master端新binlog日誌時能告訴Master伺服器從新binlog日誌的指定檔案及位置開始讀取新的binlog日誌內容

5）Slave伺服器端的SQL執行緒會實時檢測本地Relay Log 中IO執行緒新增的日誌內容，然後及時把Relay LOG 檔案中的內容解析成sql語句，並在自身Slave伺服器上按解析SQL語句的位置順序執行應用這樣sql語句，並在relay-log.info中記錄當前應用中繼日誌的檔名和位置點

主從複製必須條件

1）開啟Binlog功能

2）主庫要建立賬號

3）從庫要配置master.info（CHANGE MASTER to...相當於配置密碼檔案和Master的相關資訊）

4）start slave 開啟複製功能

主從複製需要了解的：

1）3個執行緒，主庫IO，從庫IO和SQL及作用

2）master.info（從庫）作用

3）relay-log 作用

4）非同步複製

5）binlog作用（如果需要級聯需要開啟Binlog）

注意事項：

1）主從複製是非同步的邏輯的SQL語句級的複製

2）複製時，主庫有一個I/O執行緒，從庫有兩個執行緒，I/O和SQL執行緒

3）實現主從複製的必要條件是主庫要開啟記錄binlog功能

4）作為複製的所有Mysql節點的server-id都不能相同

5）binlog檔案只記錄對資料庫有更改的SQL語句（來自主庫內容的變更），不記錄任何查詢（select，show）語句

徹底解除主從複製關係

1)stop slave;

2)reset slave; 或直接刪除master.info和relay-log.info這兩個檔案；

3)修改my.cnf刪除主從相關配置引數。

讓slave不隨MySQL自動啟動

修改my.cnf 在[mysqld]中增加 skip-slave-start 選項。

做了MySQL主從複製以後，使用mysqldump對資料備份時，一定要注意按照如下方式：

mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename

這樣就可以保留 file 和 position 的資訊，在新搭建一個slave的時候，還原完資料庫， file 和 position 的資訊也隨之更新，接著再start slave 就可以很迅速的完成增量同步！

需要限定同步哪些資料庫，有3個思路：

1）在執行grant授權的時候就限定資料庫；

2）在主伺服器上限定binlog_do_db = 資料庫名；

3）主伺服器上不限定資料庫，在從伺服器上限定replicate-do-db = 資料庫名；

如果想實現 主-從（主）-從 這樣的鏈條式結構，需要設定：

log-slave-updates      只有加上它，從前一臺機器上同步過來的資料才能同步到下一臺機器。

當然，二進位制日誌也是必須開啟的：

log-bin=/opt/mysql/binlogs/bin-log

log-bin-index=/opt/mysql/binlogs/bin-log.index

還可以設定一個log儲存週期：

expire_logs_days=14

生產場景下輕鬆部署MySQL主從複製

快速步驟MySQL主從複製

1）安裝好配置從庫的資料庫，配置好log-bin和server-id引數

2）無需配置主庫my.cnf，主庫的log-bin和server-id引數預設就是配置好的。

3）登入主庫，增加從庫連線同步的賬戶，例如：rep，並授權replication同步的許可權

4）使用mysqldump命令帶-x和–master-data=2的命令及引數全備資料，把它恢復到從庫

5）從庫執行CHANGE MASTER TO….語句，需要binlog檔案及對應點（因為–master-data=2已經帶了）

6）從庫開啟同步開關，start slave

7）從庫show slave status\G，檢查同步狀態，並在主庫更新測試

主庫I/O工作狀態

從庫I/O執行緒工作狀態show processlist;

 文章連線：http://www.cnblogs.com/kevingrace/p/6256603.html

 

相關連線：
MySQL主從複製環境部署：【http://blog.itpub.net/31015730/viewspace-2153251/】
MySQL多例項環境部署：【http://blog.itpub.net/31015730/viewspace-2153184/】
安裝mysql資料庫及問題解決方法：【http://blog.itpub.net/31015730/viewspace-2152272/】



作者：SEian.G（苦練七十二變，笑對八十一難）