MySQL主從複製原理詳情

前言：

對於現在的系統來說，在業務複雜的系統中，資料庫往往是應用的瓶頸，單機往往是不能扛住大型系統的並行壓力的，這時候需要從資料庫方面下手解決其瓶頸，例如sql語句需要鎖表，導致暫時不能使用讀的服務，那麼就很影響執行中的業務。使用主從之後，從庫的讀操作是不受影響的。

一、為什麼需要主從複製？

• 1，使用主從複製，讓主庫負責寫，從庫負責讀，這樣，即使主庫出現了鎖表的情景，通過讀從庫也可以保證業務的正常運作。
• 2，架構的擴充套件。業務量越來越大，I/O存取頻率過高，單機無法滿足，此時做多庫的儲存，降低磁碟I/O存取的頻率，提高單個機器的I/O效能。
• 3，主從多臺伺服器，同樣也可以當作資料備份的。

二、什麼是mysql的主從複製？

MySQL 主從複製是指資料可以從一個MySQL資料庫伺服器主節點複製到一個或多個從節點。MySQL 預設採用非同步複製方式，這樣從節點不用一直存取主伺服器來更新自己的資料，資料的更新可以在遠端連線上進行，從節點可以複製主資料庫中的所有資料庫或者特定的資料庫或者特定的表。

三、mysql複製原理

原理：

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

也就是說：

• 從庫會生成兩個執行緒,一個I/O執行緒,一個SQL執行緒;
• I/O執行緒會去請求主庫的binlog,並將得到的binlog寫到原生的relay-log(中繼紀錄檔)檔案中;
• 主庫會生成一個log dump執行緒,用來給從庫I/O執行緒傳binlog;
• SQL執行緒,會讀取relay log檔案中的紀錄檔,並解析成sql語句逐一執行;

注意：

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

圖來源《高效能mysql》

具體步驟

• 1、從庫通過手工執行change master to 語句連線主庫，提供了連線的使用者一切條件（user 、password、port、ip），並且讓從庫知道，二進位制紀錄檔的起點位置（file名 position 號）； start slave
• 2、從庫的IO執行緒和主庫的dump執行緒建立連線。
• 3、從庫根據change master to 語句提供的file名和position號，IO執行緒向主庫發起binlog的請求。
• 4、主庫dump執行緒根據從庫的請求，將本地binlog以events的方式發給從庫IO執行緒。
• 5、從庫IO執行緒接收binlog events，並存放到本地relay-log中，傳送過來的資訊，會記錄到master.info中。
• 6、從庫SQL執行緒應用relay-log，並且把應用過的記錄到relay-log.info中，預設情況下，已經應用過的relay 會自動被清理purge。

四、mysql主從同步延時分析

mysql的主從複製都是單執行緒的操作，主庫對所有DDL和DML產生的紀錄檔寫進binlog，由於binlog是順序寫，所以效率很高，slave的sql thread執行緒將主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨機的，不是順序，所以成本要高很多，另一方面，由於sql thread也是單執行緒的，當主庫的並行較高時，產生的DML數量超過slave的SQL thread所能處理的速度，或者當slave中有大型query語句產生了鎖等待，那麼延時就產生了。

解決方案：

• 1.業務的持久化層的實現採用分庫架構，mysql服務可平行擴充套件，分散壓力。
• 2.單個庫讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫壓力比主庫高，保護主庫。
• 3.服務的基礎架構在業務和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。
• 4.不同業務的mysql物理上放在不同機器，分散壓力。
• 5.使用比主庫更好的硬體裝置作為slave，mysql壓力小，延遲自然會變小。
• 6.使用更加強勁的硬體裝置。

mysql5.7之後使用MTS並行複製技術，永久解決複製延時問題 這個後面文章在說下吧

五、主從複製的設定

1、基礎設定準備

本次測試mysql的版本是5.7. 比較窮，在一臺機子上裝了兩個mysql範例，修改下不同埠即可。 當然如果有錢準備兩臺能互相存取的機子安裝兩個mysql也是可以的。

測試階段兩個mysql範例IP相同都是本機(ip=127.0.0.1),區分下分別命名 主是node1，從是node2，埠不同 我實際測試用的是3306和3307)

2、安裝mysql資料庫

網上很多按照的例子，這裡就不重複說了，請自行百度/google(結果是資料庫能正常使用),待兩臺mysql都按照完成之後，我們開始設定主從複製了。

3、在兩臺資料庫中分別建立資料庫

--注意兩臺必須全部執行,兩臺的資料庫保持相同
create database test;

4、在主（node1）伺服器進行如下設定：

#修改組態檔，執行以下命令開啟mysql組態檔
vi /etc/my.cnf
#在mysqld模組中新增如下設定資訊
log-bin=master-bin #二進位制檔名稱
#二進位制紀錄檔格式，有row、statement、mixed三種格式，
binlog-format=ROW  
server-id=1   #要求各個伺服器的id必須不一樣
binlog-do-db=test   #同步的資料庫名稱

二進位制紀錄檔格式，有row、statement、mixed三種格式; row指的是把改變的內容複製過去，而不是把命令在從伺服器上執行一遍;statement指的是在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高; mixed指的是預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。

5、設定從(node2)伺服器登入主伺服器的賬號授權

--授權操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--重新整理許可權
flush privileges;

6、從(node2)伺服器的設定

#修改組態檔，執行以下命令開啟mysql組態檔
vi /etc/my.cnf
#在mysqld模組中新增如下設定資訊
log-bin=master-bin #二進位制檔案的名稱
binlog-format=ROW  #二進位制檔案的格式
server-id=2 #伺服器的id

7、重啟主伺服器的mysqld服務

#重啟mysql服務
service mysqld restart
#登入mysql資料庫
mysql -uroot -p
#檢視master的狀態
show master status；

8、重啟從伺服器並進行相關設定

#重啟mysql服務
service mysqld restart
#登入mysql
mysql -uroot -p
#連線主伺服器(master_host是主的IP地址，我測試本地)
change master to master_host='127.0.0.1',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;
#啟動slave
start slave
#檢視slave的狀態
show slave statusG #(注意沒有分號)

這樣主從的設定已經完成，此時可以在主伺服器進行相關的資料新增刪除工作，在從伺服器看相關的狀態，檢視對應資料有沒有變化。

到此這篇關於MySQL主從複製原理詳情的文章就介紹到這了,更多相關MySQL主從複製內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！