排查MySQL主從

■ 河南 劉進(jìn)京

編者按：數(shù)據(jù)庫(kù)直接關(guān)系到單位IT系統(tǒng)的安全穩(wěn)定運(yùn)行，筆者遇到某單位的業(yè)務(wù)系統(tǒng)后臺(tái)采用了MySQL主從結(jié)構(gòu)，最近出現(xiàn)了數(shù)據(jù)異常的問(wèn)題。

某單位的業(yè)務(wù)系統(tǒng)后臺(tái)采用了MySQL主從結(jié)構(gòu)，最近出現(xiàn)了數(shù)據(jù)異常的問(wèn)題。

問(wèn)題主要表現(xiàn)在使用相同的查詢語(yǔ)句，在主從服務(wù)器中查詢到不同的結(jié)果，并且在從服務(wù)器上執(zhí)行“show slave status G”命令時(shí)，在返回信息中的“Slave_IO_Running”欄 中 顯 示“Connecting”， 在“Slave_SQL_Running”欄 中 顯 示“Yes”，在“Last_IO_Errno”欄中顯示“1045”，在其下顯示“error connecting to master 'xxx' – retrytime :60 retries:1”之類的錯(cuò)誤信息。

故障排查

對(duì)以上故障進(jìn)行分析，可以看到從服務(wù)器是無(wú)法連接到主服務(wù)器上的，才導(dǎo)致主從數(shù)據(jù)庫(kù)中的數(shù)據(jù)存在不一致的問(wèn)題。要尋找連接不上主服務(wù)器的原因，可以從多個(gè)方面進(jìn)行排查。例如檢測(cè)主從服務(wù)器之間的網(wǎng)絡(luò)是否暢通，如果連接處于中斷狀態(tài)，Slave自然無(wú)法連接到Master。如果網(wǎng)絡(luò)連接沒有問(wèn)題，可以檢測(cè)是否在Master端啟動(dòng)了防火墻，對(duì)數(shù)據(jù)庫(kù)端口進(jìn)行了過(guò)濾。對(duì)復(fù)制鏈路配置的用戶和密碼進(jìn)行檢查，查看該賬戶是否擁有相應(yīng)的權(quán)限。

在從服務(wù)器上執(zhí)行“ping xxx.xxx.xxx.xxx”命令，對(duì)主服務(wù)器進(jìn)行探測(cè)，其中的“xxx”標(biāo)書主服務(wù)器的IP。根據(jù)返回信息，可以看到網(wǎng)絡(luò)是暢通的。執(zhí)行“telnet xxx.xxx.xxx.xxx 3306”命令，使用Telnet連接主服務(wù)器的TCP 3306端口，根據(jù)返回信息，可以看到是可以通過(guò)TCP 3306連接到MySQL實(shí)例的，說(shuō)明主服務(wù)器的TCP 3306端口沒有被防火墻過(guò)濾。執(zhí)行“mysql -urepl -p xxxxxx -h yyy”命令，來(lái)連接從數(shù)據(jù)庫(kù)，這里的賬戶名為“repl”， 密碼為“xxxxxx”?！皔yy”為從服務(wù)器的IP，連接是沒有問(wèn)題的。在“mysql>”提示符下執(zhí)行“show grants for current_user;”命令，查看當(dāng)前賬戶的授權(quán)信息。

這里僅僅顯示“GRANT USAGE ON *.* 'repl'@'x.x.x.%'”信息，這說(shuō)明僅僅在該節(jié)點(diǎn)上創(chuàng)建了一個(gè)賬戶，但是并沒有授權(quán)，導(dǎo)致從服務(wù)器的IO進(jìn)程無(wú)法通過(guò)該賬號(hào)到主節(jié)點(diǎn)上獲取增量的二進(jìn)制日志。

至于為什么出現(xiàn)權(quán)限丟失的問(wèn)題，可能是操作人員在最近時(shí)間內(nèi)的誤操作所致。解決的方法很簡(jiǎn)單，在主界面上執(zhí)行“mysql-uroot -p”命令，以root賬戶登錄 MySQL，執(zhí)行“grant replication slave on *.*to repl@'x.x.x.%';”命令，為從節(jié)點(diǎn)授予復(fù)制的權(quán)限。

其 中 的“x.x.x.%”為具體的網(wǎng)段。返回從節(jié)點(diǎn)，在MySQL中執(zhí)行“show slave status G” 命 令，在返回信息中的“Slave_IO_Running”欄中依然顯示“Connecting”，執(zhí) 行“stop salve;”和“start slave;”命令，重啟從節(jié)點(diǎn)MySQL進(jìn)程 ，之后再次進(jìn)行查看，在“Slave_IO_Running”欄中顯示為“Yes”，說(shuō)明從節(jié)點(diǎn)已經(jīng)可以從主節(jié)點(diǎn)復(fù)制數(shù)據(jù)了。

雖然主從數(shù)據(jù)庫(kù)連接已經(jīng)正常，但依然面對(duì)兩者數(shù)據(jù)庫(kù)數(shù)據(jù)不一致的問(wèn)題。對(duì)于此類問(wèn)題，首先需要確認(rèn)主從數(shù)據(jù)庫(kù)是否存在延遲，這里不存在延遲問(wèn)題。并且經(jīng)過(guò)以上以上處理，Slave實(shí)例上的IO線程和SQL線程的狀態(tài)均為“Yes”，但是相同的查詢?cè)谥鲝臄?shù)據(jù)庫(kù)中的結(jié)果存在差異。并且對(duì)相關(guān)表進(jìn)行checksum檢查時(shí)，得到的校驗(yàn)值是不同的。所以可以確定主從數(shù)據(jù)庫(kù)中的數(shù)據(jù)確實(shí)存在不一致問(wèn)題。

之所以會(huì)出現(xiàn)該問(wèn)題，原因可能不止一個(gè)，例如有人因誤操作對(duì)Slave節(jié)點(diǎn)的數(shù)據(jù)進(jìn)行了修改，因?yàn)樵赟lave上的寫操作并不會(huì)同步到Master上，就會(huì)造成主從主從數(shù)據(jù)的不一致。另外，在進(jìn)行主從故障處理時(shí)使用了sql_slave_skip_counter或注入空事務(wù)的方式來(lái)修復(fù)錯(cuò)誤，造成在Master節(jié)點(diǎn)上執(zhí)行的事務(wù)，在Slave節(jié)點(diǎn)上應(yīng)用也會(huì)造成主從數(shù)據(jù)的差異。此外，如果在主從復(fù)制中使用statement格式的二進(jìn)制日志，因?yàn)樵摳袷降娜罩緯?huì)分別在主從節(jié)點(diǎn)上執(zhí)行SQL語(yǔ)句來(lái)完成對(duì)數(shù)據(jù)的修改，對(duì)于諸如UUID等不確定的函數(shù)來(lái)說(shuō)，會(huì)提取當(dāng)前系統(tǒng)時(shí)間作為默認(rèn)值，來(lái)生成對(duì)應(yīng)的數(shù)據(jù)列，會(huì)在主從節(jié)點(diǎn)上產(chǎn)生不一致的數(shù)據(jù)。

故障解決

根據(jù)不同的錯(cuò)誤原因可采取對(duì)應(yīng)的策略，例如對(duì)于在從節(jié)點(diǎn)的誤操作，可在Slave實(shí)例中設(shè)置“read_only=ON”參數(shù)，讓不具有Super權(quán)限用戶無(wú)法對(duì)從節(jié)點(diǎn)進(jìn)行寫操作，對(duì)于擁有Super權(quán)限的用戶，可以設(shè)置“super_read_only=ON”參數(shù)進(jìn)行限制，但需要在MySQL 5.7版本之后才具備該功能。

對(duì)于采用statement格式的二進(jìn)制日志來(lái)說(shuō)，為了避免出現(xiàn)主從數(shù)據(jù)差異，可以改用row格式的二進(jìn)制日志，該格式對(duì)于Master實(shí)例進(jìn)行實(shí)際修改后，在Slave端直接應(yīng)用，就可以避免主從的數(shù)據(jù)差異。當(dāng)然，也可以使用pt_table_sync這一工具來(lái)修復(fù)主從數(shù)據(jù)庫(kù)的數(shù)據(jù)異常。

在主節(jié)點(diǎn)上執(zhí)行“pt_table_sync --execute--c h a r s s e t=u t f 8--database=tb1 --table=tk1--sync-to-master h=x.x.x.x,u=dba,p=mima”命令，其中的“tb1”為目標(biāo)數(shù)據(jù)庫(kù)的名稱，“tk1”為表的名稱。表示對(duì)指定庫(kù)中的表進(jìn)行數(shù)據(jù)同步，同步的僅僅是不一致的數(shù)據(jù)。如果僅僅指定數(shù)據(jù)庫(kù)，表示對(duì)該庫(kù)進(jìn)行整體數(shù)據(jù)同步。其中的“syncto-master”參數(shù)表示向Master 進(jìn)行同步，“x.x.x.x”為從庫(kù)的 IP，“dba”為具有訪問(wèn)該數(shù)據(jù)庫(kù)的的賬戶名，“mima”為該賬戶的密碼。當(dāng)該命令執(zhí)行成功后，在Slave節(jié)點(diǎn)查看相關(guān)的數(shù)據(jù)庫(kù)，可以看到其已經(jīng)和Master節(jié)點(diǎn)一致了。在主從節(jié)點(diǎn)上分別執(zhí)行“checksum table tb1.tk1”命令，可以看到對(duì)于目標(biāo)數(shù)據(jù)庫(kù)的校驗(yàn)和是一致的。