原创

MySQL脚本删过期数据导致线上业务阻塞,差点填单子回家了

删库跑路没有发生在自己身上自己永远无法体验到其中的痛,笔者亲身经历过,也嘲笑过别人,当时还在想,”麻蛋,这种低级问题也会出现?搞笑吧”,殊不知,幸灾乐祸的我也终将面临这种事情,下面听我娓娓道来。

背景:

线上业务MySQL某个表目前已经达到1亿数据量,需要删掉过期脏数据,由于这种需求已经做过了,

三板斧:1.备机导出脏数据--》2.写脚本--》3.主库运行脚本删除

殊不知,其中有一个天大的隐患,差点让笔者填单子回家种地了。至今心有余悸,瑟瑟发抖,下面慢慢道来

1.线上表模拟:

CREATE TABLE `test_user_item` (
  `userID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`userID`,`itemID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `test_user_item` VALUES ('1', '1', '1');
INSERT INTO `test_user_item` VALUES ('1', '2', '0');
INSERT INTO `test_user_item` VALUES ('1', '3', '4');
INSERT INTO `test_user_item` VALUES ('2', '2', '2');

2.导出脏数据:

这里默认num为0的为脏数据,所以导出语句为:

mysql -h127.0.0.1 -utest -ptest1234 -e "use test;select userID,itemID,num from test_user_item where Num=0" >needDelInfo

查看数据:

userID  itemID  num
1       2       0

3.编写脚本

delItem.sh

#!/bin/sh
source /etc/profile

ShellPath=$(cd "$(dirname "$0")"; pwd)
NeedClearUser=${ShellPath}/needDelInfo
HadClearUser=${ShellPath}/hadClearInfo

function clearInfo(){
        userID=$1
		itemID=$2
        cmd="delete from test_user_item where  and userID=${userID} and itemID=${itemID} and num=0;"
		#echo $cmd
	    mysql -h127.0.0.1 -utest-ptest test -q -s -e "${cmd}"
}

while read line
do
	userID=$(echo ${line}|awk  '{print $1}')
	itemID=$(echo ${line}|awk  '{print $2}')
    clearInfo $userID ${itemID}
    echo ${line} >> ${HadClearUser}
done < ${NeedClearUser}

4.执行脚本

./delItem.sh &

5.问题

不知道做到这里,读者有看出什么问题?

不知道读者有没有注意到needDelInfo文件,第一行是什么?

第一行是userID itemID num这个就是问题的根源

如果执行脚本,那么输出的第一行mysql是什么?

是:delete from test_user_item where and userID=userID and itemID=itemID and num=0;

这个sql语句在本地不会有任何影响,但是在线上业务,特别是表中有1亿数据时,悲剧发生了。

他会阻塞test_user_item表,占用程序的连接池,当程序连接池被这个表占完的时候,业务就无法继续访问其他表了,雪崩就此出现~~~

mysql> select count(*) from test_user_item where num=0;
+----------+
| count(*) |
+----------+
| 13435829 |
+----------+
1 row in set (12 min 19.24 sec)

6.修复

线上mysql主库就这样阻塞了,咋办?跑路?笔者当时发现问题的时候手开始抖了,真的,作为一个多年的老司机,什么大风大浪没见过?这次确实虚了,怕了,咬着牙,镇定下来, 思路马上来了,立刻杀死这个mysql进程,说干就干

马上通过root进入mysql,

show full processlist;

找到那条delete语句

kill pid

业务恢复了。可是笔者内心的伤是永久的,越是经验老道,越是怕,怕对数据库的任何一条update,delete。

总结:

后面经过总结,反思,笔者发现问题出现是必然,规避方法:

  1. 本地或者内网演示一下所有步骤

  2. 操作数据应该在外网的备库操作,操作完毕通过reload将业务的mysql切到备库,这样万无一失。

  3. 清除数据操作还是交给DBA来操作,毕竟专业的和熟练的还是不一样。

正文到此结束