高階程式設計師必知必會,一文詳解MySQL主從同步原理

語言: CN / TW / HK

1. MySQL主從同步實現方式

MySQL主從同步是基於Bin Log實現的,而Bin Log記錄的是原始SQL語句。

Bin Log共有三種日誌格式,可以binlog_format配置引數指定。

| 引數值 | 含義 | | --------- | ------------------------------------------------------------ | | Statement | 記錄原始SQL語句,會導致更新時間與原庫不一致。
比如 update_time=now() | | Row | 記錄每行資料的變化,保證了資料與原庫一致,缺點是資料量較大。 | | Mixed | Statement和Row的混合模式,預設採用Statement模式,涉及日期、函式相關的時候採用Row模式,既減少了資料量,又保證了資料一致性。 |

常見的主從同步架構有一主多從、雙主多從。

image-20220623221000975.png

image-20220623221025652.png

2. MySQL主從同步的作用

  1. 讀寫分離,提升資料庫效能
  2. 容災恢復,主伺服器不可用時,從伺服器提供服務,提高可用性
  3. 冗餘備份,主伺服器資料損壞丟失,從伺服器保留備份

一主多從架構:

一般是主庫負責所有讀寫請求,而從庫只負責容災恢復和冗餘備份。

如果做了讀寫分離的話,主庫負責寫請求,從庫負責讀請求,可以提升資料庫效能。

雙主多從架構:

一般是主庫1負責所有讀寫請求,主庫2不對外提供服務,只用來容災恢復。

相比一主多從架構,雙主多從架構可以減少宕機時間,更快恢復資料庫可用狀態。

3. 主動同步的原理

image-20220623221744177.png

  1. 當主庫資料發生變更時,寫入本地Bin Log檔案

  2. 從庫IO執行緒發起dump主庫Bin Log檔案的請求

  3. 主庫IO執行緒推送Bin Log檔案到從庫中

  4. 從庫IO執行緒把Bin Log內容寫入本地的Relay Log檔案中

  5. 從庫SQL執行緒讀取Relay Log檔案內容

  6. 從庫SQL執行緒重新執行一遍SQL語句

4. 主從同步延遲問題

主從同步最常遇到的問題就是主從同步延遲,可以通過在從庫上執行show slave status命令檢視延遲時間,Seconds_Behind_Master表示延遲的秒數。

image-20220620230713466.png

主從同步延遲的原因有哪些?

  1. 從庫機器效能較差

主庫負責所有讀寫請求,從庫只用來備份,會用效能較差的機器,執行時間自然較慢。

  1. 從庫壓力更大

讀寫分離後,主庫負責寫請求,從庫負責讀請求。

網際網路應用一般讀請求更多,所以從庫讀壓力更大,佔用更多CPU資源。

  1. 網路延遲

當主庫的Bin Log檔案往從庫上傳送時,可能產生網路延遲,也會導致從庫資料跟不上。

  1. 主庫有大事務

當主庫上有個大事務需要執行5分鐘,把Bin Log檔案傳送到從庫,從庫至少也需要執行5分鐘,所以這時候從庫就出現了5分鐘的延遲。

主從同步延遲的解決方案?

  1. 從庫機器效能較差

把從庫換成跟主庫同等規格的機器。

  1. 從庫壓力更大

多搞幾臺從庫,分擔讀請求壓力。

  1. 網路延遲

聯絡運維或者雲服務提供商解決。

  1. 主庫有大事務

把大事務分割成小事務執行,大事務不但會產生從庫延遲,還可能產生死鎖,降低資料庫併發效能,所以儘量少用大事務。

5. 如何提升主從同步效能

1. 從庫開啟多執行緒複製

就是在主從同步的最後兩步使用多執行緒,修改配置 slave_parallel_workers=4,代表開啟4個複製執行緒。

image-20220623222719256.png

2. 修改同步模式,改為非同步

主從同步共有三種複製方式:

  1. 全同步複製

當主庫執行完一個事務,並且所有從庫都執行完該事務後,才給客戶端返回成功。

  1. 半同步複製

至少有一個從庫執行完成後,就給客戶端返回成功。

  1. 非同步複製

主庫執行完後,立即返回成功,不關心從庫是否執行完成。

如果對資料安全性要求沒那麼高,可以把同步模式改成半同步複製或者非同步複製。

3. 修改從庫Bin Log配置

修改sync_binlog配置:

sync_binlog=0 ,表示寫binlog不立即重新整理磁碟,由系統決定什麼時候重新整理磁碟。

sync_binlog=1,每次寫binlog都重新整理磁碟,安全性高,效能差。

sync_binlog=N,寫N次binlog才重新整理磁碟。

從庫對資料安全性要求沒那麼高,可以設定sync_binlog=0。

修改innodb_flush_log_at_trx_commit配置:

innodb_flush_log_at_trx_commit=0,每隔一秒鐘,把事務日誌重新整理到磁碟。

innodb_flush_log_at_trx_commit=1,每次事務都重新整理到磁碟。

innodb_flush_log_at_trx_commit=2,每次事務都不主動重新整理磁碟,由系統決定什麼時候重新整理磁碟。

從庫對資料安全性要求沒那麼高,可以設定innodb_flush_log_at_trx_commit=2。

知識點總結:

主從同步知識點總結.png