一文詳解MySQL主從同步原理

2022-08-16 14:06:03

1. MySQL主從同步實現方式

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

Bin Log共有三種紀錄檔格式，可以binlog_format設定引數指定。

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

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

2. MySQL主從同步的作用

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

一主多從架構

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

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

雙主多從架構

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

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

3. 主動同步的原理

當主庫資料發生變更時，寫入本地Bin Log檔案
從庫IO執行緒發起dump主庫Bin Log檔案的請求
主庫IO執行緒推播Bin Log檔案到從庫中
從庫IO執行緒把Bin Log內容寫入原生的Relay Log檔案中
從庫SQL執行緒讀取Relay Log檔案內容
從庫SQL執行緒重新執行一遍SQL語句

4. 主從同步延遲問題

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

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

從庫機器效能較差：

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

從庫壓力更大：

讀寫分離後，主庫負責寫請求，從庫負責讀請求。
網際網路應用一般讀請求更多，所以從庫讀壓力更大，佔用更多CPU資源。

網路延遲：

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

主庫有大事務：

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

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

從庫機器效能較差：

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

從庫壓力更大：

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

網路延遲：

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

主庫有大事務：

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

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

從庫開啟多執行緒複製

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

修改同步模式，改為非同步

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

全同步複製：

當主庫執行完一個事務，並且所有從庫都執行完該事務後，才給使用者端返回成功。

半同步複製：

至少有一個從庫執行完成後，就給使用者端返回成功。

非同步複製：

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

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

修改從庫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。