一個操作把MySQL主從復(fù)制整崩了

前言

最近公司某項目上反饋mysql主從復(fù)制失敗，被運(yùn)維部門記了一次大過，影響到了項目的驗(yàn)收推進(jìn)，那么究竟是什么原因?qū)е碌哪兀慷鲝膹?fù)制的原理又是什么呢？本文就對排查分析的過程做一個記錄。

主從復(fù)制原理

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

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

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

binlog寫入機(jī)制

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

binlog的寫入時機(jī)也非常簡單，事務(wù)執(zhí)行過程中，先把日志寫到 binlog cache ，事務(wù)提交的時候，再把binlog cache寫到binlog文件中。因?yàn)橐粋€事務(wù)的binlog不能被拆開，無論這個事務(wù)多大，也要確保一次性寫入，所以系統(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是用來控制單個二進(jìn)制日志大小，當(dāng)前日志文件大小超過此變量時，執(zhí)行切換動作。,該設(shè)置并不能嚴(yán)格控制Binlog的大小，尤其是binlog比較靠近最大值而又遇到一個比較大事務(wù)時，為了保證事務(wù)的完整性，可能不做切換日志的動作，只能將該事務(wù)的所有$QL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束。一般情況下可采取默認(rèn)值。

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

查看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. 以事務(wù)為單位統(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)中某個事務(wù)竟然占用4個G。

3. 通過start-position和stop-position統(tǒng)計這個事務(wù)各個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導(dǎo)致的

./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è)務(wù)上有個導(dǎo)入操作，這是一個非常 大的事務(wù) ，會頻繁更新這表中記錄的更新時間，導(dǎo)致生成binlog非常大。

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

binlog的模式

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

1. STATEMENT模式 ,基于SQL語句的復(fù)制

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

2. ROW模式，基于行的復(fù)制

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

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

3. MIXED模式

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

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

總結(jié)

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