percona mysql5.7进程出现大量unauthenticated user解决记录
现象:http://task.chinasoft.com.wx/ 所有任务能打开,我的任务打开很慢 有些人能用,有些人不能用,数据库出现大量的未认证用户连接,连接数利用率超过70% 重启nginx,apache,mysql没有用 问题依旧 Kill 掉cdn的推送延迟脚本,问题依旧 后面不知道是否是重启 php-fpm 解决?未知 # 批量kill mysql的进程 for id in `mysqladmin -uroot -p"pass" processlist|grep -i "unauthenticated"|awk '{print $2}'` do mysqladmin -uroot -p"pass" kill ${id} done mysqladmin -uroot -p processlist|grep -i "unauthenticated" mysql -uxtrabackup -p>/dev/null | grep -i "unauthenticated"|wc -l mysql> show processlist; +-------+----------------------+---------------------+------------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +-------+----------------------+---------------------+------------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | | rep | | NULL | Binlog Dump | | Master has sent all binlog to slave; waiting | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | root | localhost | NULL | Sleep | | | NULL | | | | | unauthenticated user | connecting host | NULL | Killed | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | yuantuan | | cms_global | Sleep | | | NULL | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | root | localhost | NULL | Query | | starting | show processlist | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | unauthenticated user | connecting host | NULL | Connect | | | | | | yuantuan | | cms_global | Query | | Sending data | SELECT count(*) FROM search_site_pages_6 WHERE `site_id`= AND `html` LIKE '%de/video-converters/t | 0 | 0 | +-------+----------------------+---------------------+------------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ rows in set (0.00 sec) mysql>
后续还是经常出现类似的情况,用kill干掉回话也不行,后面不知道修改了哪里又好了,具体的修改内容如下,供参考
已经做过的操作:
1.调整my.cnf参数
#加大连接池数
thread_pool_size = 128
# 加到最大thread数量
thread_pool_max_threads = 512
table_open_cache = 32768
# 跳过dns解析
skip-name-resolve
# 减小超时时间
wait_timeout = 60
interactive_timeout = 60
# 增大最大连接数
max_connections = 1200
max_connect_errors = 10000
max_allowed_packet = 512M
back_log = 49152
# 更改binlog日志为row类型,之前是mixed
binlog_format=row
在Linux下这个参数的值不能大于系统参数tcp_max_syn_backlog的值
通过以下命令可以查看当前值:cat /proc/sys/net/ipv4/tcp_max_syn_backlog
通过以下命令进行修改sysctl -w net.ipv4.tcp_max_syn_backlog=98304
[root@newcms:~]# cat /proc/sys/net/ipv4/tcp_max_syn_backlog
16384
[root@newcms:~]# sysctl -w net.ipv4.tcp_max_syn_backlog=98304
net.ipv4.tcp_max_syn_backlog = 98304
[root@newcms:~]# cat /proc/sys/net/ipv4/tcp_max_syn_backlog
98304
# 观察mysql日志,修改相关的参数
2019-09-28T00:31:55.707617Z 0 [ERROR] Threadpool could not create additional thread to handle queries, because the number of allowed threads was reached. Increasing 'thread_pool_max_threads' parameter can help in this situation.
If 'extra_port' parameter is set, you can still connect to the database with superuser account (it must be TCP connection using extra_port as TCP port) and troubleshoot the situation. A likely cause of pool blocks are clients that lock resources for long time. 'show processlist' or 'show engine innodb status' can give additional hints.
# 修改my.cnf的参数
thread_pool_max_threads = 512
2.跳转php.ini参数,加强对session的回收
3.删除一些表
4.将非应用连接的db账号权限全部取消,排除连接干扰
之前应用连接的账号权限比较大:对所有的数据库都有增删改查的权限,将权限控制在部分数据库中
5.查看数据库的查询状态:
[root@newcms:~]# mysqladmin -uroot -p status -i 1
Enter password:
Uptime: 13805 Threads: 595 Questions: 207060 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 14.998
Uptime: 13806 Threads: 595 Questions: 207126 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.002
Uptime: 13807 Threads: 595 Questions: 207206 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.007
Uptime: 13808 Threads: 595 Questions: 207285 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.011
Uptime: 13809 Threads: 595 Questions: 207356 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.016
Uptime: 13810 Threads: 595 Questions: 207434 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.020
Uptime: 13811 Threads: 595 Questions: 207519 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.025
Uptime: 13812 Threads: 595 Questions: 207583 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.029
Uptime: 13813 Threads: 595 Questions: 207659 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.033
Uptime: 13814 Threads: 595 Questions: 207733 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.037
Uptime: 13815 Threads: 595 Questions: 207793 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.041
Uptime: 13816 Threads: 595 Questions: 207879 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.046
Uptime: 13817 Threads: 595 Questions: 207956 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.050
Uptime: 13818 Threads: 595 Questions: 208035 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.055
Uptime: 13819 Threads: 595 Questions: 208102 Slow queries: 127 Opens: 33078 Flush tables: 1 Open tables: 9132 Queries per second avg: 15.059
最新文章
- Atitit 管理原理与实践attilax总结
- 【repost】JavaScript 事件模型 事件处理机制
- 如何设计一个 App 的注册登录流程?
- java mail
- (苹果AppleWWDRCA.cer证书过期)Failed to locate or generate matching signing assets
- [ Redis ] Redis 未授权访问漏洞被利用,服务器登陆不上
- linux中sudoers别名规则
- 高效SQL语句(SQL Server)
- 浅谈c语言代码段 数据段 bss段
- Unable to boot : please use a kernel appropriate for your cpu
- ubuntu 开启 ftp 服务 | mingming-killer
- laravel资源路由详解
- 基于NodeJS进行前后端分离
- Spark之搜狗日志查询实战
- Rest分页接口开发
- Java中日期格式(String、Date、Calendar)的相互转换
- Js/使用js来改变css的样式
- c# 敏捷3 连接,批量处理,分页
- 【java】JDK环境变量配置
- android studio的jni和so