一個操作把MySQL主從復制整崩了

前言

最近公司某項目上反饋mysql主從復制失敗，被運維部門記了一次大過，影響到了項目的驗收推進，那么究竟是什么原因?qū)е碌哪兀慷鲝膹椭频脑碛质鞘裁茨?？本文就對排查分析的過程做一個記錄。

主從復制原理

我們先來簡單了解下MySQL主從復制的原理。

1. 主庫master 服務器會將 SQL 記錄通過 dump 線程寫入到 二進制日志binary log 中；
2. 從庫slave 服務器開啟一個 io thread 線程向服務器發(fā)送請求，向 主庫master 請求 binary log。主庫master 服務器在接收到請求之后，根據(jù)偏移量將新的 binary log 發(fā)送給 slave 服務器。
3. 從庫slave 服務器收到新的 binary log 之后，寫入到自身的 relay log 中，這就是所謂的中繼日志。
4. 從庫slave 服務器，單獨開啟一個 sql thread 讀取 relay log 之后，寫入到自身數(shù)據(jù)中，從而保證主從的數(shù)據(jù)一致。

以上是MySQL主從復制的簡要原理，更多細節(jié)不展開討論了，根據(jù)運維反饋，主從復制失敗主要在IO線程獲取二進制日志bin log超時，一看主數(shù)據(jù)庫的binlog日志竟達到了4個G，正常情況下根據(jù)配置應該是不超過300M。

binlog寫入機制

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

binlog的寫入時機也非常簡單，事務執(zhí)行過程中，先把日志寫到 binlog cache ，事務提交的時候，再把binlog cache寫到binlog文件中。因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統(tǒng)會給每個線程分配一個塊內(nèi)存作為binlog cache。

1. 上圖的write，是指把日志寫入到文件系統(tǒng)的page cache，并沒有把數(shù)據(jù)持久化到磁盤，所以速度比較快
2. 上圖的fsync，才是將數(shù)據(jù)持久化到磁盤的操作, 生成binlog日志中

生產(chǎn)上MySQL中binlog中的配置max_binlog_size為250M, 而max_binlog_size是用來控制單個二進制日志大小，當前日志文件大小超過此變量時，執(zhí)行切換動作。,該設置并不能嚴格控制Binlog的大小，尤其是binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，可能不做切換日志的動作，只能將該事務的所有$QL都記錄進當前日志，直到事務結(jié)束。一般情況下可采取默認值。

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

查看binlog日志

我們可以使用mysqlbinlog這個工具來查看下binlog中的內(nèi)容，具體用法參考官網(wǎng)：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

2. 以事務為單位統(tǒng)計binlog日志文件中占用的字節(jié)大小

./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

生產(chǎn)中某個事務竟然占用4個G。

3. 通過start-position和stop-position統(tǒng)計這個事務各個SQL占用字節(jié)大小

./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

發(fā)現(xiàn)最大的一個SQL竟然占用了32M的大小，那超過10M的大概有多少個呢？

4. 通過超過10M大小的數(shù)量

./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

統(tǒng)計結(jié)果顯示竟然有200多個，毛估一下，也有近4個G了

5. 根據(jù)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

根據(jù)sql，分析了下，這個表正好有個blob字段，統(tǒng)計了下blob字段總合大概有3個G大小，然后我們業(yè)務上有個導入操作，這是一個非常 大的事務 ，會頻繁更新這表中記錄的更新時間，導致生成binlog非常大。

問題： 明明只是簡單的修改更新時間的語句，壓根沒有動blob字段，為什么生產(chǎn)的binlog這么大？因為生產(chǎn)的binlog采用的是row模式。

binlog的模式

binlog日志記錄存在3種模式，而生產(chǎn)使用的是row模式，它最大的特點，是很精確，你更新表中某行的任何一個字段，會記錄下整行的內(nèi)容，這也就是為什么blob字段都被記錄到binlog中，導致binlog非常大。此外，binlog還有statement和mixed兩種模式。

1. STATEMENT模式 ,基于SQL語句的復制

• 優(yōu)點： 不需要記錄每一行數(shù)據(jù)的變化，減少binlog日志量，節(jié)約IO，提高性能。
• 缺點： 由于只記錄語句，所以，在statement level下 已經(jīng)發(fā)現(xiàn)了有不少情況會造成MySQL的復制出現(xiàn)問題，主要是修改數(shù)據(jù)的時候使用了某些定的函數(shù)或者功能的時候會出現(xiàn)。

2. ROW模式，基于行的復制

5.1.5版本的MySQL才開始支持，不記錄每條sql語句的上下文信息，僅記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。

• 優(yōu)點: binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅僅只需要記錄那一條被修改。所以rowlevel的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。不會出現(xiàn)某些特定的情況下的存儲過程或function，以及trigger的調(diào)用和觸發(fā)無法被正確復制的問題
• 缺點： 所有的執(zhí)行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，會產(chǎn)生大量的日志內(nèi)容。

3. MIXED模式

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

在Mixed模式下，一般的語句修改使用statment格式保存binlog。如一些函數(shù)，statement無法完成主從復制的操作，則采用row格式保存binlog。

總結(jié)

最終分析下來，我們定位到原來是由于大事務+blob字段大致binlog非常大，最終我們采用了修改業(yè)務代碼，將blob字段單獨拆到一張表中解決。所以，在設計開發(fā)過程中，要盡量避免大事務，同時在數(shù)據(jù)庫建模的時候特別考慮將blob字段獨立成表。