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