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

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

• 數(shù)據(jù)分布：通過復(fù)制將數(shù)據(jù)分布到不同地理位置
• 負(fù)載均衡：讀寫分離以及將讀負(fù)載到多臺(tái)從庫(kù)
• 備份：可作為實(shí)時(shí)備份
• 高可用性：利用主主復(fù)制實(shí)現(xiàn)高可用

復(fù)制原理

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

• 在主庫(kù)上把數(shù)據(jù)更改記錄到二進(jìn)制日志binary log中，具體是在每次準(zhǔn)備提交事務(wù)完成數(shù)據(jù)更新前，主庫(kù)將數(shù)據(jù)更新的事件記錄到二進(jìn)制日志中去，Mysql會(huì)按照事務(wù)提交的順序來記錄二進(jìn)制日志的。日志記錄好之后，主庫(kù)通知存儲(chǔ)引擎提交事務(wù)。
• 從庫(kù)會(huì)啟動(dòng)一個(gè)IO線程，該線程會(huì)連接到主庫(kù)。而主庫(kù)上的binlog dump線程會(huì)去讀取主庫(kù)本地的binlog日志文件中的更新事件。發(fā)往從庫(kù)，從庫(kù)接收到日志之后會(huì)將其記錄到本地的中繼日志relay-log當(dāng)中。
• 從庫(kù)中的SQL線程讀取中繼日志relay-log中的事件，將其重放到從庫(kù)中。（在5.6版本之前SQL線程是單線程的，使得主從之間延遲更大）

兩種復(fù)制方式

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

基于語句復(fù)制

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

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

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

基于行復(fù)制

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

二者對(duì)比：

• 語句復(fù)制

• 傳輸效率高，減少延遲。
• 在從庫(kù)更新不存在的記錄時(shí)，語句賦值不會(huì)失敗。而行復(fù)制會(huì)導(dǎo)致失敗，從而更早發(fā)現(xiàn)主從之間的不一致。
• 設(shè)表里有一百萬條數(shù)據(jù)，一條sql更新了所有表，基于語句的復(fù)制僅需要發(fā)送一條sql，而基于行的復(fù)制需要發(fā)送一百萬條更新記錄

• 行復(fù)制

• 不需要執(zhí)行查詢計(jì)劃。
• 不知道執(zhí)行的到底是什么語句。

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

因?yàn)閮煞N方式各有優(yōu)缺點(diǎn)，所以mysql在這兩種復(fù)制模式進(jìn)行動(dòng)態(tài)的切換。默認(rèn)是語句。

配置要點(diǎn)

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

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

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

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


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


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

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

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

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

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

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

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

推薦使用：

innodb_flush_logs_at_trx_commit=2
sync_binlog=500

性能會(huì)較快

innodb_flush_logs_at_trx_commit=1
sync_binlog=1

較為安全

延遲問題

延遲的產(chǎn)生

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

延遲的解決

• 網(wǎng)絡(luò)方面：將從庫(kù)分布在相同局域網(wǎng)內(nèi)或網(wǎng)絡(luò)延遲較小的環(huán)境中。
• 硬件方面：從庫(kù)配置更好的硬件，提升隨機(jī)寫的性能。
• 配置方面：

從庫(kù)配置

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

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

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

筆者個(gè)人心得，如有錯(cuò)誤懇請(qǐng)網(wǎng)友評(píng)論指正。

來源：juejin.cn/post/6844903968259178504