技術文章 » 麻了，一個操作把MySQL主從複製整崩了

麻了，一個操作把MySQL主從複製整崩了

2023-05-10 21:00:39

前言

最近公司某專案上反饋mysql主從複製失敗，被運維部門記了一次大過，影響到了專案的驗收推進，那麼究竟是什麼原因導致的呢？而主從複製的原理又是什麼呢？本文就對排查分析的過程做一個記錄。

主從複製原理

我們先來簡單瞭解下MySQL主從複製的原理。

  1. 主庫master 伺服器會將 SQL 記錄通過 dump 執行緒寫入到 二進位制紀錄檔binary log 中；
  2. 從庫slave 伺服器開啟一個 io thread 執行緒向伺服器傳送請求，向 主庫master 請求 binary log。主庫master 伺服器在接收到請求之後，根據偏移量將新的 binary log 傳送給 slave 伺服器。
  3. 從庫slave 伺服器收到新的 binary log 之後，寫入到自身的 relay log 中，這就是所謂的中繼紀錄檔。
  4. 從庫slave 伺服器，單獨開啟一個 sql thread 讀取 relay log 之後，寫入到自身資料中，從而保證主從的資料一致。

以上是MySQL主從複製的簡要原理，更多細節不展開討論了，根據運維反饋，主從複製失敗主要在IO執行緒獲取二進位制紀錄檔bin log超時，一看主資料庫的binlog紀錄檔竟達到了4個G，正常情況下根據設定應該是不超過300M。

binlog寫入機制

想要了解binlog為什麼達到4個G，我們來看下binlog的寫入機制。

binlog的寫入時機也非常簡單，事務執行過程中，先把紀錄檔寫到 binlog cache ，事務提交的時候，再把binlog cache寫到binlog檔案中。因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統會給每個執行緒分配一個塊記憶體作為binlog cache。

  1. 上圖的write，是指把紀錄檔寫入到檔案系統的page cache，並沒有把資料持久化到磁碟，所以速度比較快
  2. 上圖的fsync，才是將資料持久化到磁碟的操作, 生成binlog紀錄檔中

生產上MySQL中binlog中的設定max_binlog_size為250M, 而max_binlog_size是用來控制單個二進位制紀錄檔大小，當前紀錄檔檔案大小超過此變數時，執行切換動作。,該設定並不能嚴格控制Binlog的大小，尤其是binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，可能不做切換紀錄檔的動作，只能將該事務的所有$QL都記錄進當前紀錄檔，直到事務結束。一般情況下可採取預設值。

所以說懷疑是不是遇到了大事務，因而我們需要看看binlog中的內容具體是哪個事務導致的。

檢視binlog紀錄檔

我們可以使用mysqlbinlog這個工具來檢視下binlog中的內容，具體用法參考官網：https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html。

  1. 檢視binlog紀錄檔
./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816|more
  1. 以事務為單位統計binlog紀錄檔檔案中佔用的位元組大小
./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep GTID -B1|grep '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|sort -n -r|more

生產中某個事務竟然佔用4個G。

  1. 通過start-position和stop-position統計這個事務各個SQL佔用位元組大小
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816 |grep '^# at'| awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|sort -n -r|more

發現最大的一個SQL竟然佔用了32M的大小，那超過10M的大概有多少個呢？

  1. 通過超過10M大小的數量
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|awk '$1>10000000 {print $0}'|wc -l

統計結果顯示竟然有200多個，毛估一下，也有近4個G了

  1. 根據pos, 我們看下究竟是什麼SQL導致的
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep '^# atxxxx' -C5| grep -v '###' | more

根據sql，分析了下，這個表正好有個blob欄位，統計了下blob欄位總合大概有3個G大小，然後我們業務上有個匯入操作，這是一個非常大的事務，會頻繁更新這表中記錄的更新時間，導致生成binlog非常大。

問題： 明明只是簡單的修改更新時間的語句，壓根沒有動blob欄位，為什麼生產的binlog這麼大？因為生產的binlog採用的是row模式。

binlog的模式

binlog紀錄檔記錄存在3種模式，而生產使用的是row模式，它最大的特點，是很精確，你更新表中某行的任何一個欄位，會記錄下整行的內容，這也就是為什麼blob欄位都被記錄到binlog中，導致binlog非常大。此外，binlog還有statement和mixed兩種模式。

  1. STATEMENT模式 ,基於SQL語句的複製
  • 優點： 不需要記錄每一行資料的變化，減少binlog紀錄檔量，節約IO，提高效能。
  • 缺點： 由於只記錄語句，所以，在statement level下 已經發現了有不少情況會造成MySQL的複製出現問題，主要是修改資料的時候使用了某些定的函數或者功能的時候會出現。
  1. ROW模式，基於行的複製

5.1.5版本的MySQL才開始支援，不記錄每條sql語句的上下文資訊，僅記錄哪條資料被修改了，修改成什麼樣了。

  • 優點: binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅僅只需要記錄那一條被修改。所以rowlevel的紀錄檔內容會非常清楚的記錄下每一行資料修改的細節。不會出現某些特定的情況下的儲存過程或function，以及trigger的呼叫和觸發無法被正確複製的問題
  • 缺點： 所有的執行的語句當記錄到紀錄檔中的時候，都將以每行記錄的修改來記錄，會產生大量的紀錄檔內容。
  1. MIXED模式

從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。

在Mixed模式下，一般的語句修改使用statment格式儲存binlog。如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog。

總結

最終分析下來，我們定位到原來是由於大事務+blob欄位大致binlog非常大，最終我們採用了修改業務程式碼，將blob欄位單獨拆到一張表中解決。所以，在設計開發過程中，要儘量避免大事務，同時在資料庫建模的時候特別考慮將blob欄位獨立成表。

歡迎關注個人公眾號【JAVA旭陽】交流學習