Mysql主從不同步問題處理案例

標籤：mysql   master   slave   maatkit   

在使用Mysql的主從複製架構中，有兩個比較頭疼的問題：

1、主從資料不同步後如何處理

2、主從同步延遲問題如何解決

 

本文將根據實際案例來分析下問題1，至於問題2多數文檔介紹的辦法是啟用多線程複製來解決，言歸正傳，這裡的問題1還可以細分成兩種情況。

1、Slave_IO_Running和Slave_SQL_Running在YES情況下，主從資料不同步如何處理？

2、Slave_SQL_Running在NO情況下，主從資料不同步如何處理？

 

出現第一種情況通常原因是手工去修改了從庫的資料導致主從資料不一致，這種情況如果不及時處理，當主庫也更新了對應的資料的時候，就會演變為第二種情況。

 

舉個例子：

在一主一從的條件下，當前主從的資料是同步的。

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/82/AF/wKioL1defkyRX4N5AAAsgdsSjK0970.png-wh_500x0-wm_3-wmp_4-s_3473797334.png" title="1.png" alt="wKioL1defkyRX4N5AAAsgdsSjK0970.png-wh_50" />

人為去操作從庫的某張表資料,本例中以asm_user表為示範，其中id欄位為主鍵

mysql> insert into test.asm_user (id,name,salary) values (1,‘a‘,10000);

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M00/82/AF/wKioL1defnOQRVV1AAAbXUTtCaw457.png-wh_500x0-wm_3-wmp_4-s_2509679296.png" title="2.png" alt="wKioL1defnOQRVV1AAAbXUTtCaw457.png-wh_50" />

當主庫的這條資料未變動的時候，當前主從同步進程中Slave_IO_Running和Slave_SQL_Running還是為YES，目前只是asm_user這張表的資料不同步而已，對應其他schema上的資料還是會保持主從同步；

 

但如果這個情況，主庫執行相同的SQL語句：

mysql> insert into test.asm_user (id,name,salary) values (1,‘a‘,10000);

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/82/B1/wKiom1defYWD6mNsAAAulN7mfCk568.png-wh_500x0-wm_3-wmp_4-s_103739847.png" title="3.png" alt="wKiom1defYWD6mNsAAAulN7mfCk568.png-wh_50" />

對應的SQL apply到從庫的時候就會發現duplicate key,這個時候主從的同步就會停止掉。

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/82/B0/wKioL1deg_aB1GSFAADVt0D2mrI706.jpg-wh_500x0-wm_3-wmp_4-s_3095590387.jpg" title="1.jpg" alt="wKioL1deg_aB1GSFAADVt0D2mrI706.jpg-wh_50" />

# tail -f /home/mydata/localhost.localdomain.err

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M02/82/B2/wKiom1degxHSg_fYAAHU5lGGFJE782.jpg-wh_500x0-wm_3-wmp_4-s_3634734124.jpg" title="1.jpg" alt="wKiom1degxHSg_fYAAHU5lGGFJE782.jpg-wh_50" />

這種情況下，一般我們採用maatkit工具來校正主從資料庫的資料差異情況。

這個辦法其實回答了前面的問題1，Slave_IO_Running和Slave_SQL_Running在YES情況下，主從資料不同步如何處理？

# yum -y install perl-TermReadKey # wget ftp://ftp.netbsd.org/pub/pkgsrc/distfiles/maatkit-7540.tar.gz# tar -zxvpf maatkit-7540.tar.gz # cd maatkit-7540# perl Makefile.PL # make && make install# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306  h=192.168.115.7,u=root,p=123456,P=3306 -d test | mk-checksum-filter# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306 h=192.168.115.7,u=root,p=123456,P=3306 -d test

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/82/AF/wKioL1defwTiewiDAAAf3dxeEvM458.png-wh_500x0-wm_3-wmp_4-s_2283794377.png" title="6.png" alt="wKioL1defwTiewiDAAAf3dxeEvM458.png-wh_50" />

如果主從資料不一致則採用mk-table-sync進行資料同步

# mk-table-sync --execute --print --no-check-slave --transaction --databases test  h=192.168.115.6,u=root,p=123456 h=192.168.115.7,u=root,p=123456

很明顯當前test庫資料是一致的，目前主從同步這個錯誤是可以忽略的，因此我們採用跳過這個事務的辦法來處理主從資料庫不同步問題。通常在生產環境中，主庫的資料是不斷的更新的，這裡我們在主從資料不同步的情況下在主庫繼續插入一條資料，方便後續驗證。

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M02/82/B1/wKiom1defiiSepqiAAAHhCqI68I693.png-wh_500x0-wm_3-wmp_4-s_2961374103.png" title="7.png" alt="wKiom1defiiSepqiAAAHhCqI68I693.png-wh_50" />

下面我們開始處理主從不同步問題：

在未啟用GTID複製的情況下採用下面的方法跳過事務：

mysql>slave stop; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  //跳過一個事務 mysql>slave start;

Mysql5.6之後支援GTID複製，開啟GTID複製的好處很多，具體可以百度一下！但當開啟gtid後就不能採用前面那種辦法來跳過事務。

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/82/B1/wKiom1defmHANpENAAAbbunNJGg135.png-wh_500x0-wm_3-wmp_4-s_1301407293.png" title="8.png" alt="wKiom1defmHANpENAAAbbunNJGg135.png-wh_50" />

在show slave status \G;輸出中的最後幾條裡面，

Retrieved_Gtid_Set項：記錄了relay日誌從Master擷取了binlog日誌的位置

Executed_Gtid_Set項：記錄本機執行的binlog日誌位置（如果是從機，包括Master的binlog日誌位置和slave本身的binlog日誌位置）

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/82/B1/wKiom1defnejlgvOAABIDzTvZPo455.png-wh_500x0-wm_3-wmp_4-s_101777228.png" title="9.png" alt="wKiom1defnejlgvOAABIDzTvZPo455.png-wh_50" />

我們要跳過事務的GTID在錯誤記錄檔中有記錄

# tail -f /home/mydata/localhost.localdomain.err

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/82/B1/wKiom1defp6BFAf4AABxhFEkNRE916.png-wh_500x0-wm_3-wmp_4-s_143939303.png" title="10.png" alt="wKiom1defp6BFAf4AABxhFEkNRE916.png-wh_50" />

mysql> set session gtid_next=‘bd9e9912-2bc7-11e6-bade-000c29b8871c:1440‘;mysql> begin;commit;mysql> set session gtid_next=automatic;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/82/B1/wKiom1defruipUhkAAAWDyHizeU551.png-wh_500x0-wm_3-wmp_4-s_561589258.png" title="11.png" alt="wKiom1defruipUhkAAAWDyHizeU551.png-wh_50" />

mysql> start slave;mysql> show slave status \G;

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M02/82/B1/wKiom1deftez44ZfAAAxVx15lp4238.png-wh_500x0-wm_3-wmp_4-s_1155162403.png" title="12.png" alt="wKiom1deftez44ZfAAAxVx15lp4238.png-wh_50" />

驗證從庫資料是否和主庫一致

mysql> select * from test.asm_user;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/82/AF/wKioL1degAejA92LAAAJkqFK890385.png-wh_500x0-wm_3-wmp_4-s_3013576113.png" title="13.png" alt="wKioL1degAejA92LAAAJkqFK890385.png-wh_50" />

前面類比了Slave_SQL_Running在NO情況下，主從資料不同步情況的處理過程，在現實的環境中，往往情況要複雜的多，下面分享一則記憶體開發庫因為斷電導致主從資料不一致的故障處理：

1、因為電源故障，導致主從資料庫全部宕機，電源恢複後，主庫啟動正常，從庫無法啟動，通過分析日誌發現可能是電源故障導致從庫的固態盤異常，許多的binlog檔案許可權出現？？？，這些檔案甚至無法正常查看

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/82/AF/wKioL1degB-A0o4tAAIJHQ0T6_o437.png-wh_500x0-wm_3-wmp_4-s_3188579251.png" title="14.png" alt="wKioL1degB-A0o4tAAIJHQ0T6_o437.png-wh_50" />

1、通過fsck -y進行檔案系統校正修複壞塊，修複完成後從庫資料庫可以啟動，但開啟複製進程的時候報中繼日誌丟失

2、在沒有辦法的情況下，採用主庫dump資料，從庫重新source的辦法線上重做主從資料同步。整個操作過程中，主庫的資料不斷的寫入。

 

下面是大致的步驟：

3.1、主庫匯出全庫資料，注意一定要使用--single-transaction參數

# /usr/local/mysql/bin/mysqldump --all-databases --single-transaction --triggers --routines > /tmp/1.sql

3.2、將備份檔案拷貝到從庫進行source

3.3、開啟從庫的複製進程

mysql> change master to master_host=‘192.168.1.15‘,

master_user=‘rep1‘,master_password=‘123456‘,MASTER_AUTO_POSITION=1;

mysql> start slave;

本文出自 “斬月” 部落格，謝絕轉載！

Mysql主從不同步問題處理案例