mysql hang 删除_MySQL DELETE语句HANG住问题解决纪实

1、MySQL简单的delete语句hang住

同事说线上一个库执行简单的delete卡住了，我去check线上库的负载很低，show full processlist;也没有啥慢卡住或者锁的sql在执行，那问题在哪里呢？去查看错误日志，有如下信息

2017-03-14T01:32:22.628309Z 6553507 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:23.491555Z 6553508 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:24.718334Z 6553515 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:24.755244Z 6553516 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:25.816457Z 6553517 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:26.908096Z 6553519 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:28.078465Z 6553523 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:29.102774Z 6553527 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2017-03-14T01:32:30.205321Z 6553528 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

用more查看出现这个的最新时间是2016-05-03日，已经是很久以前的事情了

[root@prod_3_11 mysql]# more /usr/local/mysql/mysqld.log |grep 127.0.0.1 |more

2016-05-03T08:57:13.997474Z 1701 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T08:57:16.117305Z 1702 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T08:57:30.661653Z 1704 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T08:58:16.491907Z 1705 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T08:58:31.221777Z 1707 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T08:59:30.861211Z 1709 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2016-05-03T09:00:12.617069Z 1710 [Note] Access denied for user 'root'@'127.0.0.1' (using password: NO)

2、问题分析

突然想到2016-05-03，刚在这太mysql库里面添加了zabbix监控，难道是zabbix的agent引起的问题？看到这个登陆失败，于是关闭zabbix的agent

[root@prod_3_11 mysql]# killallzabbix_agentd

[root@prod_3_11 mysql]#

在查看mysql的错误日志/usr/local/mysql/mysqld.log，没有新的Access deniedfor user 'root'@'127.0.0.1' (using password: NO)错误信息过来，而mysql库也稳定下来了，看来问题出在zabbix的监控访问上面。

去查看zabbix的agent'启动方式，找到zabbix配置文件zabbix_agentd.conf

[root@prod_3_11 ~]# ps -eaf|grep zabbix

zabbix   27118     1  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf

zabbix   27119 27118  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd: collector [idle 1 sec]

zabbix   27120 27118  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd: listener #1 [waiting for connection]

zabbix   27121 27118  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd: listener #2 [waiting for connection]

zabbix   27122 27118  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd: listener #3 [waiting for connection]

zabbix   27123 27118  0 13:54 ?        00:00:00 /usr/sbin/zabbix_agentd: active checks #1 [idle 1 sec]

root     27590 15250  0 13:58 pts/4    00:00:00 grep zabbix

[root@prod_3_11 ~]#

再查看配置文件/etc/zabbix/zabbix_agentd.conf，进入到最文件最末端，找到关于mysql的配置

UserParameter=mysql.version,mysql -V

UserParameter=mysql.status[*],/usr/local/zabbix/chk_mysql_1.sh $1 $2

UserParameter=mysql.ping,nmap 192.168.3.11 -p 3317|grep open |wc -l

UserParameter=mongodmonitor,/usr/local/zabbix/mongodmonitor.sh "$1"

关于mysql的有2行记录，执行下这2行记录，看是否能顺利执行

(1)第一行是mysql.ping，ok，可以执行

[root@prod_3_11 ~]# nmap 192.168.3.11 -p 3317|grep open |wc -l

1

[root@prod_3_11 ~]#

(2)第二行是执行一个脚本

[root@prod_3_11 ~]# sh /usr/local/zabbix/chk_mysql_1.sh

Please input one arguement:

Usage:/usr/local/zabbix/chk_mysql_1.sh(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions)

[root@prod_3_11 ~]# sh /usr/local/zabbix/chk_mysql_1.sh Com_update

mysqladmin: connect to server at '127.0.0.1' failed

error: 'Access denied for user 'root'@'127.0.0.1' (using password: NO)'

[root@prod_3_11 ~]#

看到了，执行报错“'Accessdenied for user 'root'@'127.0.0.1' (using password: NO)'”，这个错误也是在mysql的后台错误日志频繁出现的，终于找到了，那么这里就要去看下这个脚本/usr/local/zabbix/chk_mysql_1.sh，为啥会频繁报错。

3、问题解决

看到脚本里面的密码好像是旧的，需要修复脚本/usr/local/zabbix/chk_mysql_1.sh，更新里面的用户名密码。

然后再启动zabbix的客户端引擎。

/usr/local/zabbix/sbin/zabbix_agentd -c/usr/local/zabbix/conf/zabbix_agentd.conf

回过头，查看mysql窗口等待的delete语句，已经消失了，查看数据，已经被删除了。