詳細講解mysql主從同步原理、配置以及延遲

本文介紹了mysql的主從同步原理、主從同步配置、主從同步延遲，首先我們先來瞭解什麼是主從同步，主從同步，顧名思義也稱為主從複製，用來建立一個和主要資料庫完全一樣的資料庫環境。主從同步使得資料可以從一個資料庫伺服器複製到其他伺服器上，實現主要資料庫的資料和從資料庫的資料保持一致。

• 叢集是共用儲存的,是data-sharing . 主從複製中沒有任何共用 . 每台機器都是獨立且完整的系統,是nothing-sharing.

主從同步的原理

• 從mysql5.6之後主從複製的實現方式主要有3種:
  

  1. 非同步複製

  2. 全同步複製

  3. 半同步複製
  

• 主從同步原理圖
  

1.當主要資料庫的更新事件(update、insert、delete)被寫到binary-log .

2.從庫建立一個I/O線程，該線程串連到主庫並請求主庫發送binlog裡面的更新記錄到從庫上 .主庫建立一個binlog dump thread線程，把binlog的內容發送到從庫 ,從庫的I/O線程讀取主庫的輸出線程發送的更新並拷貝這些更新到本地relay log檔案中 .

3.從庫建立一個SQL線程，這個線程讀取從庫I/O線程寫到relay log的更新事件並執行 .

主從同步的實現(非同步複製,資料庫在不同伺服器)

1.配置主要資料庫開啟binary-log

vim /etc/my.cnf在[mysqld]下添加server-id=1(用來標識不同的資料庫)log-bin=master-bin(開啟bin-log並設定檔名為master-bin)log-bin-index=master-bin.index(區分不同的log-bin檔案)

重啟資料庫:systemctl restart mariadb.service

2.配置從資料庫開啟relay-log

vim /etc/my.cnf在[mysqld]下添加server-id=2relay-log=slave-relay-bin(開啟relay-log並設定檔名為slave-relay-bin)relay-log-index=slave-relay-bin.index

重啟資料庫:systemctl restart mariadb.service

3.串連兩個資料庫

在主要資料庫中:建立使用者repl ,每一個從伺服器都需要用到主要資料庫一個賬戶名和密碼來串連主伺服器 .

CREATE USER 'repl'@'114.116.77.213' IDENTIFIED BY '12312';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'114.116.77.213' IDENTIFIED BY '12312';

在從資料庫中:

change master to master_host='47.106.78.106',master_user='repl',master_password='12312',master_log_file='master-bin.000001',master_log_pos=0;

啟動同步:start slave;

4.驗證

在主要資料庫建立一個資料庫,然後在從資料庫查看

主從同步的作用

1. 做資料的熱備，作為後備資料庫，主要資料庫伺服器故障後，可切換到從資料庫繼續工作，避免資料丟失 .

2. 讀寫分離,使資料庫能支撐更大的並發 .

主從同步的注意事項

1. 主庫可以讀寫資料,而從庫只能讀資料,因為當從庫寫了資料positon會變化,但是主庫的position是不會變的,當主庫寫資料變化position的時候就可能會有衝突.

2. 當主庫的binatylog檔案儲存體的資料很多,也就是position很大的時候,會再分裂一個新的binarylog檔案,position置為0;

3. 主從庫的mysql版本可以不一樣,但是從庫的mysql版本要比主庫的版本要高,如果不是的話,那麼主庫的語句到了從庫可能就不能執行.
   因為mysql是向後相容的,也就是說低版本的語句在高版本裡面是支援的,但是高版本的有些語句在低版本是不支援的.

面試相關

（如果問到資料庫主從問題，必問以下問題）：

1. 主從的好處是？

2. 主從的原理是？

3. 從資料庫的讀的延遲問題瞭解嗎？如何解決？

4. 做主從後主伺服器掛了怎麼辦？

5. 主從同步的延遲的原因

6. 主從同步的延遲的原因
   

主從同步延遲問題

1. 主從同步的延遲的原因

我們知道， 一個伺服器開放Ｎ個連結給用戶端來串連的，　這樣有會有大並發的更新操作, 但是從伺服器的裡面讀取binlog 的線程僅有一個， 當某個SQL在從伺服器上執行的時間稍長 或者由於某個SQL要進行鎖表就會導致，主伺服器的SQL大量積壓，未被同步到從伺服器裡。這就導致了主從不一致， 也就是主從延遲。

2. 主從同步延遲的解決辦法

實際上主從同步延遲根本沒有什麼一招制敵的辦法， 因為所有的SQL必須都要在從伺服器裡面執行一遍，但是主伺服器如果不斷的有更新操作源源不斷的寫入， 那麼一旦有延遲產生， 那麼延遲加重的可能性就會原來越大。 當然我們可以做一些緩解的措施。

a. 我們知道因為主伺服器要負責更新操作， 他對安全性的要求比從伺服器高， 所有有些設定可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog， innodb_flush_log_at_trx_commit 也 可以設定為0來提高sql的執行效率 這個能很大程度上提高效率。另外就是使用比主庫更好的硬體裝置作為slave。

b. 就是把，一台從伺服器當度作為備份使用， 而不提供查詢， 那邊他的負載下來了， 執行relay log 裡面的SQL效率自然就高了。

c. 增加從伺服器嘍，這個目的還是分散讀的壓力， 從而降低伺服器負載。