mysqldump引发数据库死锁的问题（mysqldump一直卡住）

今天在网上看到一篇关于MySQL导出引发的系列问题的文章，分享给大家学习一下

这个命令在印象中没什么问题，时不时就会用，也没出现过问题。但是这次，线上数据库直接“炸了”！！！

当执行完这个命令后，等待了一段时间发现t.sql的大小一直是 0 说明这次dump没有成功，下意识的登陆主库 show processlist了一下，发现所有查询连接都处于Waiting for table flush状态，经过寻找发现是mysqldump的命令一直夯死在那里，kill掉mysqldump连接后，线上数据库瞬间恢复。影响还是非常严重，造成线上数据库响应异常3分钟，基本上就是事故了。好在用户没怎么投诉，大事化小小事化了了。不过这个非常常规的操作居然有这么大的影响必须要一探究竟。

复盘阶段

一、当时的场景

事发的数据库当时是处于高并发的，且查询的都是同一张大表，dump的表并不是高并发的那张

二、问题分析

当时数据库高并发的大表本身是没有什么问题的，之前经过sql优化，索引优化等处理后连慢查询都没有了

我dump的是一张没什么访问量的小表，理应不会出什么问题

元凶多半是mysqldump的执行过程跟什么东西冲突了

三、复现问题

准备一张大表，并发100查询，同时查询不走索引

执行如下命令：

执行dump的连接出现了不友好的东西，有锁了

但是，当下一秒再执行show processlist时又恢复正常了，同时mysqldump也执行完毕。

琢磨不透是为什么，只能再多执行几次该dump语句，基本都是这个情况，上锁、瞬间释放、备份数据成功。

终于终于，在一次dump中神奇的事情发生了，出现跟线上事故一样的场景，所有连接集体炸裂

分析一下：

主要就三个参数：

–set-gtid-purged=off：主要是控制是否记录gtid，没影响

–master-data=2：该参数会以非注释的方式展示位点信息及注释的方式展示位点信息

–single-transaction：在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables，然后执行导出,导出过程不影响其它事务或业务连接

分别以这三个参数互相搭配观察general.log

当–master-data=2\1时为获取一致性位置点，需要上两把锁

FLUSH /*!40101 LOCAL */ TABLES

FLUSH TABLES WITH READ LOCK

总结

如果备份出的.sql文件需要binlog信息则使用master-data,其他时候不要加！！！否则在高并发情况下，有可能出现锁争用情况，导致数据库死锁。以前一直这么用也没有问题，可能是数据库压力不大情况下吧。这次本身数据库处于高负载又恰好在两次上锁间出现死锁（经过上面验证当–master-data=2\1时会上两次锁，但都是瞬间释放的），虽然是小概率事件，大家还需要注意一下。

如果我们主库下游还有一个从库（相当于实时备份库，不参与业务读写），那么需要dump的时候我们都在从库执行是不是就没有问题了呢，反正从库没有业务没什么负载。经过验证，在从库dump,如果–master-data=2\1时，也会出现死锁情况（可能dump100次出现一次吧），从而导致主从无限延迟(kill掉dump连接，延迟恢复)。

有遗漏或者不对的可以在我的公众号留言哦