MySQL主從資料庫同步延遲問題怎麼解決

MySQL的主從同步是一個很成熟的架構,優點為:

①在從伺服器可以執行查詢工作(即我們常說的讀功能),降低主伺服器壓力;

②在從主伺服器進行備份,避免備份期間影響主伺服器服務;③當主伺服器出現問題時,可以切換到從伺服器。

相信大家對於這些好處已經非常瞭解了,在專案的部署中也採用這種方案。但是MySQL的主從同步一直有從庫延遲的問題,那麼為什麼會有這種問題。這種問題如何解決呢?

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社群

2. MySQL資料庫主從同步延遲原理。

3. MySQL資料庫主從同步延遲是怎麼產生的。

4. MySQL資料庫主從同步延遲解決方案。

1. MySQL資料庫主從同步延遲原理。

答:談到MySQL資料庫主從同步延遲原理,得從mysql的資料庫主從複製原理說起,mysql的主從複製都是單執行緒的操作,主庫對所有DDL和   DML產生binlog,binlog是順序寫,所以效率很高,slave的Slave_IO_Running執行緒到主庫取日誌,效率很比較高,下一步,   問題來了,slave的Slave_SQL_Running執行緒將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的,不是順  序的,成本高很多,還可能可slave上的其他查詢產生lock爭用,由於Slave_SQL_Running也是單執行緒的,所以一個DDL卡主了,需要 執行10分鐘,那麼所有之後的DDL會等待這個DDL執行完才會繼續執行,這就導致了延時。有朋友會問:“主庫上那個相同的DDL也需要執行10分,為什 麼slave會延時?”,答案是master可以併發,Slave_SQL_Running執行緒卻不可以。

2. MySQL資料庫主從同步延遲是怎麼產生的。

答:當主庫的TPS併發較高時,產生的DDL數量超過slave一個sql執行緒所能承受的範圍,那麼延時就產生了,當然還有就是可能與slave的大型query語句產生了鎖等待。

3. MySQL資料庫主從同步延遲解決方案

答:最簡單的減少slave同步延時的方案就是在架構上做最佳化,儘量讓主庫的DDL快速執行。還有就是主庫是寫,對資料安全性較高,比如  sync_binlog=1,innodb_flush_log_at_trx_commit = 1  之類的設定,而slave則不需要這麼高的資料安全,完全可以講sync_binlog設定為0或者關閉binlog,innodb_flushlog也  可以設定為0來提高sql的執行效率。另外就是使用比主庫更好的硬體裝置作為slave。

mysql-5.6.3已經支援了多執行緒的主從複製。原理和丁奇的類似,丁奇的是以表做多執行緒,Oracle使用的是以資料庫(schema)為單位做多執行緒,不同的庫可以使用不同的複製執行緒。

基於區域網的master/slave機制在通常情況下已經可以滿足'實時'備份的要求了。如果延遲比較大,就先確認以下幾個因素:

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社群

2. 網路延遲

3. master負載

4. slave負載

一般的做法是,使用多臺slave來分攤讀請求,再從這些slave中取一臺專用的伺服器,只作為備份用,不進行其他任何操作,就能相對***限度地達到'實時'的要求了

slave_net_timeout單位為秒  預設設定為 3600秒 引數含義:當slave從主資料庫讀取log資料失敗後,等待多久重新建立連線並獲取資料  master-connect-retry單位為秒 預設設定為 60秒 引數含義:當重新建立主從連線時,如果連線建立失敗,間隔多久後重試。

通常配置以上2個引數可以減少網路問題導致的主從資料同步延遲

判斷主從延時,通常有兩個方法:

1.Seconds_Behind_Master vs 2. mk-heartbeat,下面具體說下兩者在實現功能的差別。

可以透過監控show slave statusG命令輸出的Seconds_Behind_Master引數的值來判斷,是否有發生主從延時。

其值有這麼幾種:

NULL - 表示io_thread或是sql_thread有任何一個發生故障,也就是該執行緒的Running狀態是No,而非Yes。0 - 該值為零,是我們極為渴望看到的情況,表示主從複製良好,可以認為lag不存在。正值 - 表示主從已經出現延時,數字越大表示從庫落後主庫越多。負值 - 幾乎很少見,只是聽一些資深的DBA說見過,其實,這是一個BUG值,該引數是不支援負值的,也就是不應該出現。

Seconds_Behind_Master是透過比較sql_thread執行的event的timestamp和io_thread複製好的   event的timestamp(簡寫為ts)進行比較,而得到的這麼一個差值。我們都知道的relay-log和主庫的bin-log裡面的內容完全一   樣,在記錄sql語句的同時會被記錄上當時的ts,所以比較參考的值來自於binlog,其實主從沒有必要與NTP進行同步,也就是說無需保證主從時鐘的   一致。你也會發現,其實比較真正是發生在io_thread與sql_thread之間,而io_thread才真正與主庫有關聯,於是,問題就出來了,  當主庫I/O負載很大或是網路阻塞,io_thread不能及時複製binlog(沒有中斷,也在複製),而sql_thread一直都能跟上  io_thread的指令碼,這時Seconds_Behind_Master的值是0,也就是我們認為的無延時,但是,實際上不是,你懂得。這也就是為什   麼大家要批判用這個引數來監控資料庫是否發生延時不準的原因,但是這個值並不是總是不準,如果當io_thread與master網路很好的情況下,那麼  該值也是很有價值的。(就好比:媽–兒子–媳婦的關係,媽與兒子親人,媳婦和兒子也親人,不見得媳婦與媽就很親。開個玩笑:-)之前,提到  Seconds_Behind_Master這個引數會有負值出現,我們已經知道該值是io_thread的最近跟新的ts與sql_thread執行到  的ts差值,前者始終是大於後者的,唯一的肯能就是某個event的ts發生了錯誤,比之前的小了,那麼當這種情況發生時,負值出現就成為可能。

方法2. mk-heartbeat,Maatkit***工具包中的一個工具,被認為可以準確判斷複製延時的方法。

mk-heartbeat的實現也是藉助timestmp的比較實現的,它首先需要保證主從伺服器必須要保持一致,透過與相同的一個NTP   server同步時鐘。它需要在主庫上建立一個heartbeat的表,裡面至少有id與ts兩個欄位,id為server_id,ts就是當前的時間戳  now(),該結構也會被複制到從庫上,表建好以後,會在主庫上以後臺程式的模式去執行一行更新操作的命令,定期去向表中的插入資料,這個週期預設為1   秒,同時從庫也會在後臺執行一個監控命令,與主庫保持一致的週期去比較,複製過來記錄的ts值與主庫上的同一條ts值,差值為0表示無延時,差值越大表示  延時的秒數越多。我們都知道複製是非同步的ts不肯完全一致,所以該工具允許半秒的差距,在這之內的差異都可忽略認為無延時。這個工具就是透過實打實的復  制,巧妙的借用timestamp來檢查延時。