排查MySQL主從

■ 河南 劉進京

編者按：數據庫直接關系到單位IT系統的安全穩定運行，筆者遇到某單位的業務系統后臺采用了MySQL主從結構，最近出現了數據異常的問題。

某單位的業務系統后臺采用了MySQL主從結構，最近出現了數據異常的問題。

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

故障排查

對以上故障進行分析，可以看到從服務器是無法連接到主服務器上的，才導致主從數據庫中的數據存在不一致的問題。要尋找連接不上主服務器的原因，可以從多個方面進行排查。例如檢測主從服務器之間的網絡是否暢通，如果連接處于中斷狀態，Slave自然無法連接到Master。如果網絡連接沒有問題，可以檢測是否在Master端啟動了防火墻，對數據庫端口進行了過濾。對復制鏈路配置的用戶和密碼進行檢查，查看該賬戶是否擁有相應的權限。

在從服務器上執行“ping xxx.xxx.xxx.xxx”命令，對主服務器進行探測，其中的“xxx”標書主服務器的IP。根據返回信息，可以看到網絡是暢通的。執行“telnet xxx.xxx.xxx.xxx 3306”命令，使用Telnet連接主服務器的TCP 3306端口，根據返回信息，可以看到是可以通過TCP 3306連接到MySQL實例的，說明主服務器的TCP 3306端口沒有被防火墻過濾。執行“mysql -urepl -p xxxxxx -h yyy”命令，來連接從數據庫，這里的賬戶名為“repl”， 密碼為“xxxxxx”。“yyy”為從服務器的IP，連接是沒有問題的。在“mysql>”提示符下執行“show grants for current_user;”命令，查看當前賬戶的授權信息。

這里僅僅顯示“GRANT USAGE ON *.* 'repl'@'x.x.x.%'”信息，這說明僅僅在該節點上創建了一個賬戶，但是并沒有授權，導致從服務器的IO進程無法通過該賬號到主節點上獲取增量的二進制日志。

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

其 中 的“x.x.x.%”為具體的網段。返回從節點，在MySQL中執行“show slave status G” 命 令，在返回信息中的“Slave_IO_Running”欄中依然顯示“Connecting”，執 行“stop salve;”和“start slave;”命令，重啟從節點MySQL進程 ，之后再次進行查看，在“Slave_IO_Running”欄中顯示為“Yes”，說明從節點已經可以從主節點復制數據了。

雖然主從數據庫連接已經正常，但依然面對兩者數據庫數據不一致的問題。對于此類問題，首先需要確認主從數據庫是否存在延遲，這里不存在延遲問題。并且經過以上以上處理，Slave實例上的IO線程和SQL線程的狀態均為“Yes”，但是相同的查詢在主從數據庫中的結果存在差異。并且對相關表進行checksum檢查時，得到的校驗值是不同的。所以可以確定主從數據庫中的數據確實存在不一致問題。

之所以會出現該問題，原因可能不止一個，例如有人因誤操作對Slave節點的數據進行了修改，因為在Slave上的寫操作并不會同步到Master上，就會造成主從主從數據的不一致。另外，在進行主從故障處理時使用了sql_slave_skip_counter或注入空事務的方式來修復錯誤，造成在Master節點上執行的事務，在Slave節點上應用也會造成主從數據的差異。此外，如果在主從復制中使用statement格式的二進制日志，因為該格式的日志會分別在主從節點上執行SQL語句來完成對數據的修改，對于諸如UUID等不確定的函數來說，會提取當前系統時間作為默認值，來生成對應的數據列，會在主從節點上產生不一致的數據。

故障解決

根據不同的錯誤原因可采取對應的策略，例如對于在從節點的誤操作，可在Slave實例中設置“read_only=ON”參數，讓不具有Super權限用戶無法對從節點進行寫操作，對于擁有Super權限的用戶，可以設置“super_read_only=ON”參數進行限制，但需要在MySQL 5.7版本之后才具備該功能。

對于采用statement格式的二進制日志來說，為了避免出現主從數據差異，可以改用row格式的二進制日志，該格式對于Master實例進行實際修改后，在Slave端直接應用，就可以避免主從的數據差異。當然，也可以使用pt_table_sync這一工具來修復主從數據庫的數據異常。

在主節點上執行“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”為目標數據庫的名稱，“tk1”為表的名稱。表示對指定庫中的表進行數據同步，同步的僅僅是不一致的數據。如果僅僅指定數據庫，表示對該庫進行整體數據同步。其中的“syncto-master”參數表示向Master 進行同步，“x.x.x.x”為從庫的 IP，“dba”為具有訪問該數據庫的的賬戶名，“mima”為該賬戶的密碼。當該命令執行成功后，在Slave節點查看相關的數據庫，可以看到其已經和Master節點一致了。在主從節點上分別執行“checksum table tb1.tk1”命令，可以看到對于目標數據庫的校驗和是一致的。