MySQL 主從復(fù)制解決了什么問題？出現(xiàn)同步延遲如何解決？

主從復(fù)制解決的問題

• 數(shù)據(jù)分布：通過復(fù)制將數(shù)據(jù)分布到不同地理位置

• 負(fù)載均衡：讀寫分離以及將讀負(fù)載到多臺從庫

• 備份：可作為實時備份

• 高可用性：利用主主復(fù)制實現(xiàn)高可用

復(fù)制原理

復(fù)制的原理其實很簡單，僅分為以下三步：

• 在主庫上把數(shù)據(jù)更改記錄到二進制日志binary log中，具體是在每次準(zhǔn)備提交事務(wù)完成數(shù)據(jù)更新前，主庫將數(shù)據(jù)更新的事件記錄到二進制日志中去，Mysql會按照事務(wù)提交的順序來記錄二進制日志的。日志記錄好之后，主庫通知存儲引擎提交事務(wù)。

• 從庫會啟動一個IO線程，該線程會連接到主庫。而主庫上的binlog dump線程會去讀取主庫本地的binlog日志文件中的更新事件。發(fā)往從庫，從庫接收到日志之后會將其記錄到本地的中繼日志relay-log當(dāng)中。

• 從庫中的SQL線程讀取中繼日志relay-log中的事件，將其重放到從庫中。（在5.6版本之前SQL線程是單線程的，使得主從之間延遲更大）

兩種復(fù)制方式

日志文件中記錄的到底是什么呢？mysql支持了兩種日志格式，這兩種日志格式也體現(xiàn)了各自的復(fù)制方式

基于語句復(fù)制

基于語句的復(fù)制相當(dāng)于邏輯復(fù)制，即二進制日志記錄了操作的語句，通過這些語句在從庫進行重放來實現(xiàn)復(fù)制。

這種方式簡單，二進制日志占用空間少，使得帶寬小傳輸效率較高。但是基于語句的更新依賴于其他因素，比如插入數(shù)據(jù)時利用時間戳函數(shù)調(diào)用當(dāng)前時間作為時間值也會出現(xiàn)問題，因為由于主從之間的延遲導(dǎo)致時間值不一致。存儲過程和觸發(fā)器也可能出現(xiàn)問題。

所以在開發(fā)當(dāng)中我們應(yīng)該將邏輯盡量放在代碼層，而不應(yīng)放到mysql中，不易擴展。

基于行復(fù)制

基于行的復(fù)制相當(dāng)于物理復(fù)制，即二進制日志記錄了實際更新數(shù)據(jù)的每一行。這樣導(dǎo)致行復(fù)制的壓力比較大，因為日志占用空間較大，傳輸占用帶寬也較高。但是比基于語句復(fù)制更加精確，可以屏蔽一些由于主庫從庫之間的差異導(dǎo)致的不一致。如剛才提到的時間戳函數(shù)。

二者對比：

語句復(fù)制

• 傳輸效率高，減少延遲。

• 在從庫更新不存在的記錄時，語句賦值不會失敗。而行復(fù)制會導(dǎo)致失敗，從而更早發(fā)現(xiàn)主從之間的不一致。

• 設(shè)表里有一百萬條數(shù)據(jù)，一條sql更新了所有表，基于語句的復(fù)制僅需要發(fā)送一條sql，而基于行的復(fù)制需要發(fā)送一百萬條更新記錄

行復(fù)制

• 不需要執(zhí)行查詢計劃。

• 不知道執(zhí)行的到底是什么語句。

例如一條更新用戶總積分的語句，需要統(tǒng)計用戶的所有積分再寫入用戶表。如果是基于語句復(fù)制的話，從庫需要再一次統(tǒng)計用戶的積分，而基于行復(fù)制就直接更新記錄，無需再統(tǒng)計用戶積分。

因為兩種方式各有優(yōu)缺點，所以mysql在這兩種復(fù)制模式進行動態(tài)的切換。默認(rèn)是語句。

配置要點

# 如果在雙主復(fù)制結(jié)構(gòu)中沒有設(shè)置ID的話就會導(dǎo)致循環(huán)同步問題
server_id=1

# 即日志中記錄的是語句還是行更新或者是混合
binlog_format=mixed

# 在進行n次事務(wù)提交以后，Mysql將執(zhí)行一次fsync的磁盤同步指令。將緩沖區(qū)數(shù)據(jù)刷新到磁盤。
# 為0的話由Mysql自己控制頻率。
sync_binlog=n

# 為0的話，log buffer將每秒一次地寫入log file中并且刷新到磁盤。
# mysqld進程崩潰會丟失一秒內(nèi)的所有事務(wù)。
# 為1的話，每次事務(wù)log buffer會寫入log file并刷新到磁盤。（較為安全）
# 在崩潰的時候,僅會丟失一個事務(wù)。
# 為2的話，每次事務(wù)log buffer會寫入log file，但一秒一次刷新到磁盤
innodb_flush_logs_at_trx_commit=0


# 阻止從庫崩潰后自動啟動復(fù)制，給一些時間來修復(fù)可能的問題，
# 崩潰后再自動復(fù)制可能會導(dǎo)致更多的問題。并且本身就是不一致的
skip_slave_start=1 


# 是否將從庫同步的事件也記錄到從庫自身的bin-log中
# 允許備庫將重放的事件也記錄到自身的二進制日志中去，可以將備庫當(dāng)做另外一臺主庫的從庫
log_slave_update 

# 日志過期刪除時間，延遲嚴(yán)重的話會導(dǎo)致日志文件占用磁盤
expire_logs_days=7

innodb_flush_logs_at_trx_commit的三個參數(shù)很容易弄混。以下是詳細(xì)的解析：

mysql先將日志寫到log buffer緩沖區(qū)當(dāng)中，再將log buffer緩沖區(qū)的數(shù)據(jù)寫到log file日志文件中，此時寫入的是內(nèi)存中的log file，最終仍需操作系統(tǒng)將內(nèi)存中的數(shù)據(jù)刷寫到磁盤上。

參數(shù)0：mysql每秒都會將log buffer的數(shù)據(jù)寫入到log file中并且刷新到磁盤。意味著mysql崩潰的時候?qū)G失一秒內(nèi)的所有事務(wù)。

參數(shù)1：每次事務(wù)提交都會將log buffer寫入到log file并刷新到磁盤。意味著在mysql崩潰的時候,僅會丟失一個事務(wù)。

參數(shù)2：每次事務(wù)提交都會將log buffer寫入到log file但不同時寫入到磁盤，由mysql自行控制每秒將log file刷寫到磁盤上，當(dāng)mysql崩潰的時候操作系統(tǒng)沒崩潰的時候，log_file中僅會丟失一個事務(wù)，操作系統(tǒng)仍會將log file刷寫到磁盤，而如果操作系統(tǒng)也崩潰或斷電的話，則會丟失一秒內(nèi)的事務(wù)。

推薦使用：

innodb_flush_logs_at_trx_commit=2
sync_binlog=500

性能會較快

innodb_flush_logs_at_trx_commit=1
sync_binlog=1

較為安全

延遲問題

延遲的產(chǎn)生

當(dāng)主庫的TPS并發(fā)較高時，由于主庫上面是多線程寫入的，而從庫的SQL線程是單線程的，導(dǎo)致從庫SQL可能會跟不上主庫的處理速度（生產(chǎn)者比消費者快，導(dǎo)致商品堆積）。

延遲的解決

• 網(wǎng)絡(luò)方面：將從庫分布在相同局域網(wǎng)內(nèi)或網(wǎng)絡(luò)延遲較小的環(huán)境中。

• 硬件方面：從庫配置更好的硬件，提升隨機寫的性能。

• 配置方面：

從庫配置

sync_binlog=0
innodb_flush_log_at_trx_commit=2
logs-slave-updates=0
增大 innodb_buffer_pool_size

讓更多操作在Mysql內(nèi)存中完成，減少磁盤操作。或者升級Mysql5.7版本使用并行復(fù)制。

• 架構(gòu)方面：比如在事務(wù)當(dāng)中盡量對主庫讀寫，其他非事務(wù)中的讀在從庫。消除一部分延遲帶來的數(shù)據(jù)庫不一致。增加緩存降低一些從庫的負(fù)載。

筆者個人心得，如有錯誤懇請評論指正。

來源：juejin.cn/post/6844903968259178504

推薦閱讀