从库mysqldump会导致复制中断

1、从库my sq Idum p会导致复制中断环境数据库版本：主从架构：一主两从搭配mha操作步骤,开发要求truncate一张表，在删除之前需要备份下数据； .在从库上使用mysqldump命令做逻辑备份，备份sql ：mysqldump -uXXX -p osdc osdc_XXX /tmp/osdc_info.sql;.这台从库与主库的同步出现中断，报错为：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；重新启停同步服务后，恢复正常。分析_之前形形色色的主从问题遇到过很多,但是1756这个错误还是第一次遇到，所以事后还是很 有兴趣去深入分析下这个问题。.详细报错查看数据库的error log，这个报错的具体输出如下：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错误日志里有个Lock wait timeout exceeded，这个我们先记下，稍后再说。先找至master事务的gtid，直接去主库的binlog里找下这个gtid所对应的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可以看到，这时候主库对osdc_XXX表进行了 update操作。2 .问题分析,首先，mysqldump不加任何参数去执行，会对备份的表加表级锁。.这时候从库需要执行从主库同步过来的update

6、语句，因为mysqldump表锁的存在，该 语句会处理等待状态。.update等待超时，这个错误我们在上面的error log里可以看到(Lock wait timeout exceeded)。.因为在版本下在使用MTS的情况下，slave_transaction_retries是不生效的。 所以当update语句超时后，它没有重试机制，导致整个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这个 bug 很早就由 Yoshinori Matsunobu 提在 ? id=68465 了 官方也采纳了，并于修复掉了该问题。Yoshinori Matsunobu就是大名鼎鼎的MHA的作者!.问题复现相关参数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|+测试数据：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显性开启事务，让它持续持有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表的一条数据，此时从库的复制线程会等待这条sql的应用，并处于 system lock 状态： 从库：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错误日志开始打印错误信息：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报的错与当时的内容一致。.总结.mysqldump即使在从库中执行，也必须加上-single-transcation等参数，直接执行会上 表锁，本钱大大。.局部mysql工具，如navicat直接使用它自带的导出功能，也会锁住全表。所以尽量不要 使用I具去处理导出工作。mysql show open tAbles where in_use 0 ; TOC o 1-5 h