问题
事情发生在前天,开发同学需要一个表的线上数据用来测试,我使用了一个常规mysqldump的命令导出数据。命令如下:
mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/t.sql;
这个命令在印象中没什么问题,时不时就会用,也没出现过问题。但是这次,线上数据库直接“炸了”!!!
当执行完这个命令后,等待了一段时间发现t.sql的大小一直是 0 说明这次dump没有成功,下意识的登陆主库 show processlist了一下,发现所有查询连接都处于waiting for table flush状态,经过寻找发现是mysqldump的命令一直夯死在那里,kill掉mysqldump连接后,线上数据库瞬间恢复。影响还是非常严重,造成线上数据库响应异常3分钟,基本上就是事故了。好在用户没怎么投诉,大事化小小事化了了。不过这个非常常规的操作居然有这么大的影响必须要一探究竟。
复盘阶段
一、当时的场景
1、只执行了mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/t.sql;
2、事发的数据库当时是处于高并发的,且查询的都是同一张大表
3、dump的表并不是高并发的那张
二、问题分析
1、当时数据库高并发的大表本身是没有什么问题的,之前经过sql优化,索引优化等处理后连慢查询都没有了
2、我dump的是一张没什么访问量的小表,理应不会出什么问题
3、元凶多半是mysqldump的执行过程跟什么东西冲突了
三、复现问题
1、准备一张大表,并发100查询,同时查询不走索引
虽然执行的慢,但是其乐融融不会有什么异常
2、此时执行命令
mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/t.sql;
执行dump的连接出现了不友好的东西,有锁了
破案了吗,好像并没有,因为当下一秒再执行show processlist时又恢复正常了,同时mysqldump也执行完毕。
猜想
说明该mysqldump语句确实会造成锁出现,但都是瞬间释放的,应该不会像本次事故一样影响这么恶劣啊
琢磨不透是为什么,只能在多执行几次该dump语句,基本都是这个情况,上锁、瞬间释放、备份数据成功。
终于终于,在一次dump中神奇的事情发生了,出现跟线上事故一样的场景,所有连接集体炸裂
是时候破案了
一、分析一下mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/t.sql;
主要就三个参数
1、–set-gtid-purged=off
2、–master-data=2
3、–single-transaction
–set-gtid-purged=off
之前研究过,主要是控制是否记录gtid,没影响
–master-data=2
该参数
1:会以非注释的方式展示位点信息
2:会以注释的方式展示位点信息
–single-transaction
在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables,然后执行导出,导出过程不影响其它事务或业务连接
具体官方解释如下:
二、分别以这三个参数互相搭配观察general.log
sql1
mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/t.sql;
这是常用备份语句
sql2
mysqldump -uxxx -pxxx -h192.168.xxx.xxx test t --set-gtid-purged=off --single-transaction > /tmp/t.sql;
这里没有加–master-data=2
破案破案
当–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连接,延迟恢复)。
很难受,无论在主库还是从库,都很难手动必现这个问题
因为flush /*!40101 local */ tables -> flush tables with read lock -> unlock tables都是瞬间执行的,所以无法跟大家截图分享了,目前只能通过调整参数避免。