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

前言

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

主從復(fù)制原理

我們先來(lái)簡(jiǎn)單了解下MySQL主從復(fù)制的原理。

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

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

binlog寫(xiě)入機(jī)制

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

binlog的寫(xiě)入時(shí)機(jī)也非常簡(jiǎn)單，事務(wù)執(zhí)行過(guò)程中，先把日志寫(xiě)到 binlog cache ，事務(wù)提交的時(shí)候，再把binlog cache寫(xiě)到binlog文件中。因?yàn)橐粋€(gè)事務(wù)的binlog不能被拆開(kāi)，無(wú)論這個(gè)事務(wù)多大，也要確保一次性寫(xiě)入，所以系統(tǒng)會(huì)給每個(gè)線程分配一個(gè)塊內(nèi)存作為binlog cache。

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

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

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

查看binlog日志

我們可以使用mysqlbinlog這個(gè)工具來(lái)查看下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)計(jì)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)中某個(gè)事務(wù)竟然占用4個(gè)G。

3. 通過(guò)start-position和stop-position統(tǒng)計(jì)這個(gè)事務(wù)各個(gè)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)最大的一個(gè)SQL竟然占用了32M的大小，那超過(guò)10M的大概有多少個(gè)呢？

4. 通過(guò)超過(guò)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)計(jì)結(jié)果顯示竟然有200多個(gè)，毛估一下，也有近4個(gè)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，分析了下，這個(gè)表正好有個(gè)blob字段，統(tǒng)計(jì)了下blob字段總合大概有3個(gè)G大小，然后我們業(yè)務(wù)上有個(gè)導(dǎo)入操作，這是一個(gè)非常 大的事務(wù) ，會(huì)頻繁更新這表中記錄的更新時(shí)間，導(dǎo)致生成binlog非常大。

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

binlog的模式

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

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

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

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

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

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

3. MIXED模式

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

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

總結(jié)

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