MySQL 中主庫跑太快,從庫追不上怎麼整?

2021-02-19 程式設計師的成長之路

來自：萊烏

寫這篇文章是因為之前有一次刪庫操作，需要進行批量刪除數據，當時沒有控制好刪除速度，導致產生了主從延遲，出現了一點小事故。今天我們就來看看為什麼會產生主從延遲以及主從延遲如何處理等相關問題。隨著日益增長的訪問量，單臺資料庫的應接能力已經捉襟見肘。因此採用主庫寫數據，從庫讀數據這種將讀寫分離開的主從架構便隨之衍生了出來。在生產環境中，常見的主從架構有很多種，在這裡給大家介紹幾種比較常見的架構模式。

了解了主從的基本架構及相關配置後，下面就要進入正題了。對於主從來說，通常的操作是主庫用來寫入數據，從庫用來讀取數據。這樣的好處是通過將讀寫壓力分散開，避免了所有的請求都打在主庫上。同時通過從庫進行水平擴展使系統的伸縮性及負載能力也得到了很大的提升。
但是問題就來了，讀從庫時的數據要與主庫保持一致，那就需要主庫的數據在寫入後同步到從庫中。如何保持主庫與從庫的數據一致性，主庫又是通過什麼樣的方式將數據實時同步到從庫的？

binlog（二進位日誌文件）

relay log（中繼日誌文件）

在主從同步的過程中，主庫會將所有的操作事件記錄在 binlog 中，從庫通過開啟一個 I/O 線程保持與主庫的通信，並在一定時間間隔內探測 binlog 日誌文件是否發生改變。如果 binlog 日誌發生了變化，主庫生成一個 binlog dump 線程向從庫 I/O 線程傳送 binlog。從庫上的 I/O 線程將 binlog 複製到自己的 relay log 中。最終由從庫中的 SQL 線程讀取 relay log 中的事件重放到從庫上。上面的流程我們已經知道了主從複製的相關過程了，但是主庫有更新就會同步從庫，那為什麼會出現主從延遲的情況呢？Mysql 主庫中寫 binlog 的操作是順序寫的，之前我們提到過，磁碟的順序讀寫速度是很快的。同樣的，從庫中的 I/O 線程操作日誌的速度效率也是很高的。但是別忘了，還有一個 SQL 線程來進行數據重放，而重放的過程是隨機寫盤的。到這裡你應該就明白了吧，某一時刻 relay log 裡的數據來不及重放進從庫，就會產生主從延遲的情況。知道了從庫中 SQL 線程的重放情況，對於主庫並發高導致主從延遲肯定就不難理解了。某一時刻，大量寫請求打到主庫上，意味著要不斷對 binlog 進行寫入，此時從庫中的 SQL 線程就會應接不暇，自然會產生主從延遲。對於 SQL 單線程來說，當遇到阻塞時就會一直等待，直到執行成功才會繼續進行。如果某一時刻從庫因為查詢產生了鎖等待的情況，此時只有當前的操作執行完成後才會進行下面的操作，同理也就產生了主從延遲的情況。知道了主從延遲的原因，接下來我們看看如何來進行處理。既然 SQL 單線程進行重放時速度有限，那麼能不能採用多線程的方式來進行重放呢？MySQL 5.6 版本後，提供了一種並行複製的方式，通過將 SQL 線程轉換為多個 work 線程來進行重放，這樣就解決了主從延遲的問題。你可能會說了，我現在用的低版本的資料庫，也沒法升版本啊，那我怎麼整。對於主庫並發高的情況，這種方式你只能通過控制並發來解決延遲了，多用用 Redis。這種情況你肯定不陌生，對於一些實時性要求比較高的數據，你總不能讀從庫去拿吧，萬一延遲個大半天，你不得貢獻自己的年終獎啊。

推薦閱讀：

如何從一張外國軍車照片，判斷它要去哪裡？

一個比 c3p0 快200倍的資料庫連接池，這麼牛？

5T技術資源大放送！包括但不限於：C/C++，Linux，Python，Java，PHP，人工智慧，單片機，樹莓派，等等。在公眾號內回復「2048」，即可免費獲取！！

微信掃描二維碼，關注我的公眾號

朕已閱 

相關焦點

  • 主庫n -> 從庫s - MySQL5.7多主一從(多源複製)同步配置 - 計算機...
    多主一從，也稱為多源複製，數據流向：主庫1 -> 從庫s主庫2 -> 從庫s主庫n -> 從庫s應用場景數據匯總，可將多個主資料庫同步匯總到一個從資料庫中，方便數據統計分析。讀寫分離，從庫只用於查詢，提高資料庫整體性能。部署環境註：使用docker部署mysql實例，方便快速搭建演示環境。
  • 面試被問MySQL 主從複製,怎麼破?
    log）-- 從庫 I/O 線程；從庫讀取中繼日誌中的事件，將其重放到數據中 -- 從庫 SQL 線程。三、配置主庫# 3.1 創建用戶為了安全起見，準備創建一個新用戶用於從庫連接主庫。若主庫可以停機，則直接拷貝所有資料庫文件。若主庫是在線生產庫，可採用 mysqldump 備份數據，因為它對所有存儲引擎均可使用。
  • MySQL GTID(Global Transaction Identifier)深入理解
    MySQL GTID簡介GTID（ Global Transaction Identifier）全局事務標識，由主庫上生成的與事務綁定的唯一標識，這個標識不僅在主庫上是唯一的，在MySQL集群內也是唯一的。
  • MySQL 5.7基於GTID及多線程主從複製
    MySQL中主從複製的優點橫向擴展解決方案在多個從庫之間擴展負載以提高性能。在這種環境中，所有寫入和更新在主庫上進行。但是，讀取可能發生在一個或多個從庫上。該模型可以提高寫入的性能（由於主庫專用於更新），同時在多個從庫上讀取，可以大大提高讀取速度。
  • mysql show processlist Time為負數的思考
    想閱讀八怪源碼文章歡迎訂閱本文建議橫屏觀看，效果更佳一、問題來源這是一個朋友問我的一個問題，問題如下，在MTS中Worker線程看到Time為負數是怎麼回事，如下：2、從庫單Sql線程和Worker線程其實不管單Sql線程還是Worker線程都是執行Event的，這裡的start_time將會被設置為Event header中timestamp的時間（query event/dml event），這個時間實際就是主庫命令發起的時間。
  • 技術分享 | mysql show processlist Time 為負數的思考
    =0x7ffe701ed330, rli=0x7ffe7003c050) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/log_event.cc:11417thd->set_time(&(common_header->when))實際上就是這一行
  • MySQL基於MHA的FailOver過程
    如果檢查腳本不通過MHA無法啟動3.MHA-manager 通過masterha_master_monitor腳本(每隔ping_interval秒)4.masterha_master_monitor探測主庫3次無心跳之後，主認為主庫宕機了5.進行選主過程
  • MySQL從庫實用技能教程 巧用slave_exec_mode參數
    想必從庫異常中斷的情況不在少數，其中報錯信息中 1032 及 1062 的錯誤佔了不少的比重錯誤 1032 指的是從庫中找不到對應行的記錄錯誤 1062 指的是主鍵衝突遇到此報錯時，大多DBA會使用如下方法進行處理1 手動處理方法一： 找出引起異常的數據然後手動在從庫處理後重啟
  • MySql 高頻企業面試題
    日誌中會記錄成每一行數據被修改的形式，然後在slave端再對相同的數據進行修改每一條修改的數據都會完整的記錄到主庫master的binlog裡面，在slave上完整執行在master執行的sql語句結合前面的兩種模式，如果在工作中有使用函數
  • 深入理解MySQL 5.7 GTID系列(六):MySQL啟動初始化GTID模塊
    從庫開啟GTID開啟BINLOG不開啟log_slave_updates參數。因為這兩種使我們通常設置的方式，下面簡稱主庫和從庫。其原理為一行一行的讀取mysql.gtid_executed表的內容加入到gtid_state.executed_gtids中，我們來看源碼：// Initialize executed_gtids from mysql.gtid_executed table.
  • openGauss備機追數Catchup過程中主庫寫入阻塞問題
    最近在測試openGauss主從複製時發現一個問題：當備機落後主機很多時（比如停了一段時間後再啟動），啟動後會自動的追數，追數的過程狀態是catchup，而在catchup的過程中，主庫上的寫入會全部阻塞，當然經過進一步驗證，如果存在其他正常的備庫（狀態是normal），那麼其中一個備庫catchup不會阻塞主庫。
  • MySQL資料庫遭到攻擊篡改---使用備份和binlog進行數據恢復
    本文來自資料庫技術專家張正，主要描述了MySQL遭到攻擊篡改數據，利用從庫的備份和主庫的Binlog進行不完全恢復。 以下是作者原文：一、發現問題今天是2014-09-26，開發大清早就說昨晚資料庫遭到了攻擊。資料庫中某文章表的文章內容欄位遭到篡改，全部改成了同一篇文章。
  • MySQL主從複製高級進階
    延時從庫介紹及配置SQL線程延時：數據已經寫入relaylog中了，SQL線程稍後執行。所以如果從庫原本已經是延遲從庫了，需要重新設置延遲數值時，重啟從庫複製以後，會發現原有的relay log被清空了，並重新生成了序號從1開始的relay log。但不用擔心複製會被影響，因重新生成的relay log,是根據上一次從庫停止時所應用到的主庫log pos重新從主庫拉取binlog的。
  • MySQL十大經典錯誤案例,你遇到過幾個?
    Top 1：Too many connections（連接數過多，導致連接不上資料庫，業務無法正常進行）問題還原mysql> show variables like '%max_connection%';| Variable_name | Value |mysql> set global max_connections
  • 這 10 個 MySQL 經典錯誤,遇到過才是老司機!
    Top 1：Too many connections（連接數過多，導致連接不上資料庫，業務無法正常進行）問題還原mysql> show variables like '%max_connection%';| Variable_name
  • MySQL 數據校驗工具-愛可生
    其原理是在主庫執行基於 statement 的 SQL 語句來生成主庫數據塊的checksum，把相同的 SQL 語句傳遞到從庫執行，並在從庫上計算相同數據塊的 checksum，最後，比較主從庫上相同數據塊的 checksum 值，由此判斷主從數據是否一致。它能在非常大的表上工作的一個原因是，它把每個表分成行塊，並檢查每個塊與單個替換。選擇查詢。它改變塊的大小，使校驗和查詢在所需的時間內運行。
  • 沒遇到過這十個MySQL資料庫經典錯誤,你一定不是個好工程師
    跟無頭蒼蠅一樣，會不加思索地把錯誤粘到百度上，希望趕緊查找一下有沒有好的問題處理方法。我想上述這個應該是剛從事資料庫的小白都會遇到的窘境。今天就給大家列舉 MySQL 資料庫中最經典的十大錯誤案例，並附有處理問題的解決思路和方法。希望能給剛入行或資料庫愛好者一些幫助，今後再遇到任何報錯，我們都可以很淡定地去處理。學習任何一門技術的同時，其實就是自我修煉的過程。
  • MySQL資料庫「十宗罪」(十大經典錯誤案例)
    Top 1：Too many connections（連接數過多，導致連接不上資料庫，業務無法正常進行）問題還原:Top 2：（主從複製報錯類型）Last_SQL_Errno: 1062  （從庫與主庫數據衝突）
  • MySQL 資料庫「十宗罪」(十大經典錯誤案例)
    今天給大家說說《資料庫中十大經典錯誤案例》老牛我在剛開始學習資料庫的時候，沒少走彎路。經常會遇到各種稀奇古怪的 error 信息，遇到報錯會很慌張，急需一個解決問題的辦法。跟無頭蒼蠅一樣，會不加思索地把錯誤粘到百度上，希望趕緊查找一下有沒有好的處理問題的方法。我想這個應該是剛從事資料庫的小白，都會遇到窘境。
  • 深入理解MySQL 5.7 GTID系列(八):GTID帶來的運維改變
    ，如果從庫報錯我們需要獲得從庫執行的最後一個事務，方法有如下：show slave status \G 中的 Executed_Gtid_Set。這也為我的故障案例埋下了伏筆，案例中在詳細描述。當然也可以使用 --set-gtid-purged=OFF選項來告訴mysqldump不需要加入SQL_LOG_BIN= 0和GTID_PURGED，但是初始化搭建基於GTID的主從一定不要設置為OFF。下面是這個選項的含義。