從庫mysqldump會(huì)導(dǎo)致復(fù)制中斷

1、從庫my sq Idum p會(huì)導(dǎo)致復(fù)制中斷環(huán)境數(shù)據(jù)庫版本：主從架構(gòu)：一主兩從搭配mha操作步驟,開發(fā)要求truncate一張表，在刪除之前需要備份下數(shù)據(jù)； .在從庫上使用mysqldump命令做邏輯備份，備份sql ：mysqldump -uXXX -p osdc osdc_XXX /tmp/osdc_info.sql;.這臺從庫與主庫的同步出現(xiàn)中斷，報(bào)錯(cuò)為：SlAve_IO_Running: Yes SlAve_SQL_Running: No Last_Errno: 1756Last_Error: . . . The slAve coordinAtor And worker thrAds A

2、re stoppdERROR NO 是1756，而且只是 Slave_SQL_Running 停了。處理方式.停止同步：stop slAve;.翻開同步：stArt sIav；重新啟停同步服務(wù)后，恢復(fù)正常。分析_之前形形色色的主從問題遇到過很多,但是1756這個(gè)錯(cuò)誤還是第一次遇到，所以事后還是很 有興趣去深入分析下這個(gè)問題。.詳細(xì)報(bào)錯(cuò)查看數(shù)據(jù)庫的error log，這個(gè)報(bào)錯(cuò)的具體輸出如下：2020-08-18 22:04:19 208840 WArning SlAve SQL: Worker 2 fAiled executin ; Could not execute UpdAte_rows e

3、vent on tAble osdc.osdc_XXX; Lock wAit mAstr log mysql-bin.003536, end_log_pos 861741231r Error_code: 12052020-08-18 22:04:19 208840 Note Worker 2 is exiting: killd 0, error 12020-08-18 22:04:19 208840 ERROR SlAve SQL: . The slAve coordinAtoromAticAlly, Although using non-trAnsActionAl storAge for d

4、Ata or info ta s). Error code: 1756錯(cuò)誤日志里有個(gè)Lock wait timeout exceeded，這個(gè)我們先記下，稍后再說。先找至master事務(wù)的gtid，直接去主庫的binlog里找下這個(gè)gtid所對應(yīng)的sql ：SET SESSION.GTID_NEXT= 1fe0ed3ca-cacc-11e9-8d49-6c92bfcf73b6:137762972# At 861740635#200818 21:58:42 server id 31 end_log_pos 861740715 CRC32 0 xA9Ab0dd2 SET TIMESTAMP=15

5、97 7 5 9122/* ! * / ;BEGIN/*!*/；# At 861740715#200818 21:58:42 server id 31 end_log_pos 861740840 CRC32 0 xbd8bA613updAte osdc_XXXset modif y_time=now () ,stAtus= *04*where id=92 98XXXXX And stAtus=1011可以看到，這時(shí)候主庫對osdc_XXX表進(jìn)行了 update操作。2 .問題分析,首先，mysqldump不加任何參數(shù)去執(zhí)行，會(huì)對備份的表加表級鎖。.這時(shí)候從庫需要執(zhí)行從主庫同步過來的update

6、語句，因?yàn)閙ysqldump表鎖的存在，該 語句會(huì)處理等待狀態(tài)。.update等待超時(shí)，這個(gè)錯(cuò)誤我們在上面的error log里可以看到(Lock wait timeout exceeded)。.因?yàn)樵诎姹鞠略谑褂肕TS的情況下，slave_transaction_retries是不生效的。 所以當(dāng)update語句超時(shí)后，它沒有重試機(jī)制，導(dǎo)致整個(gè)SQL threads都停掉了(這也 就是為什么 Slave_IO_Running 顯示YES，而 Slave_SQL_Running 顯示NO )。在rpl_slave.cc里可以看到：/* MTS tchnicAl 1imitAtion no su

7、pport of trAns rtry */i f (mi-rli-opt_slAve_pArAllel_workers != 0 & & slAve_trAns_retries != 0這個(gè) bug 很早就由 Yoshinori Matsunobu 提在 ? id=68465 了 官方也采納了，并于修復(fù)掉了該問題。Yoshinori Matsunobu就是大名鼎鼎的MHA的作者!.問題復(fù)現(xiàn)相關(guān)參數(shù)slave_parallel_workers ： 3 TOC o 1-5 h z rootlocAlhost(nona) show vAriAbles like 1_pArAllel_work +I

8、 VAriAble_nAme| VAlue |+-f-| slAvepArAllel workers| 5|+innodb_lock_wait_timeout ： 50rootlocAlhost (none) show vAriAbles like , %innodb_lock_wAit_tim TOC o 1-5 h z +I VAriAbl_nAmI VaIu |+ HYPERLINK l bookmark26 o Current Document I innodb_lock_wAit_timeout | 50|+slave_transaction_retries ： 10rootlocA

9、lhostnone show vAriAbles like 1%slAve_trAnsAction_retr+4-|VAriAble_nAme|VAlue |+ HYPERLINK l bookmark28 o Current Document IsIavrAnsAction_rtries |10|+測試數(shù)據(jù)：rootlocAlhost bug select * from rpl_tst;+I id|info|+I1 | tom |I2 | j erry |+從庫加鎖操作從庫：rootlocAlhost bug begin;updAte repl_test st info=1 Al1 ,;Qu

10、ery OK, 0 rows Affected(0.00 sec)(0.01 sec)Rows mAtched: 2ChAnged: 2 WArnings: 0Query OK, 2 rows Affected顯性開啟事務(wù)，讓它持續(xù)持有InnoDB lock主庫更新操作主庫：rootlocAlhost bugupdAte repl test set info=1 All * where id=l;commitQuery OK, 1 row Affected (0.00 sec)Rows mAtched: 1 ChAnged: 1 WArnings: 0Query OK, 0 rows Affe

11、cted (0.00 sec)手工更新repl_test表的一條數(shù)據(jù)，此時(shí)從庫的復(fù)制線程會(huì)等待這條sql的應(yīng)用，并處于 system lock 狀態(tài)： 從庫：rootIocaIhost (none) show processlist;+-1JId1, 4-User+1+Host_i_+db|_1_+CommAnd |_1_一+Time | _1.StAt 1161vnt_.scheduler |locAlhostNULL |DAemon |545 |WAiting on191root1locAlhostbug |Sleep|10 |1151systemuser1NULL |Connect I4

12、4 |WAiting for1161systemuser1NULL |Connect |5 |SlAve hAs r1171systemuser1NULL |Connect I44 |WAiting for1181systemuser1NULL |Connect I44 |WAiting for1191systemuser1NULL |Connect |5 |System lock1201root1locAlhostNULL |Query|0 |init+8 rows in set (0.04 sec)errorlog錯(cuò)誤日志開始打印錯(cuò)誤信息：2020-08-20 11: 01:00 2239

13、12020-08-20 11:01:00 223912020-08-20 11:01:0022391WArning SlAve SQL: ERROR SlAVe SQL:. Note Error rAdingWorker 2 fAild xcuting , The slAve coordinAtor a rlAy log vnt: sIav SQ報(bào)的錯(cuò)與當(dāng)時(shí)的內(nèi)容一致。.總結(jié).mysqldump即使在從庫中執(zhí)行，也必須加上-single-transcation等參數(shù)，直接執(zhí)行會(huì)上 表鎖，本錢大大。.局部mysql工具，如navicat直接使用它自帶的導(dǎo)出功能，也會(huì)鎖住全表。所以盡量不要 使用I具去處理導(dǎo)出工作。mysql show open tAbles where in_use 0 ; TOC o 1-5 h