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语句，已经消失了，查看数据，已经被删除了。