首页 > 代码库 > 关于Mysql查看某个ip连接数及删除掉这个ip连接的方法
关于Mysql查看某个ip连接数及删除掉这个ip连接的方法
统计mysql各ip的连接总数：
mysql> select SUBSTRING_INDEX(host,‘:‘,1) as ip , count(*) from information_schema.processlist group by ip;
状态如下：
+----------------+----------+ | ip | count(*) | +----------------+----------+ | | 3 | | 10.182.41.191 | 1 | | 10.190.249.204 | 1 | | 10.204.161.60 | 10 | | localhost | 1 | +----------------+----------+
如ip10.190.249.204有一个进程正在连接mysql，我们要将其杀掉。
注：还有其他方法来查看mysql各ip连接总数，如在linux上通过mysql命令远程查看其状态。
mysql -u root -h127.0.0.1 -e "show processlist\G;"| egrep "Host\:" | awk -F: ‘{ print $2 }‘| sort | uniq -c或
mysql -u root -h127.0.0.1 --skip-column-names -e "show processlist;"|awk ‘{print $3}‘|awk -F":" ‘{print $1}‘|sort|uniq –c使用如下命令具体查看此ip的具体连接状态:
mysql> show full processlist;
状态如下：
+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 16664843 | Waiting on empty queue | NULL | | 3 | tencentroot | :34481 | NULL | Sleep | 5 | | NULL | | 33 | tencentroot | :38939 | NULL | Binlog Dump | 16663717 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 460426 | tencentroot | :45751 | NULL | Sleep | 2 | | NULL | | 573982 | root | 10.190.249.204:41661 | db_gfxy_gdb_239 | Sleep | 24 | | NULL | | 594340 | root | 10.204.161.60:40129 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594341 | root | 10.204.161.60:40130 | db_gfxy_gdb_239 | Sleep | 7 | | NULL | | 594342 | root | 10.204.161.60:40131 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594343 | root | 10.204.161.60:40132 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594344 | root | 10.204.161.60:40133 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594345 | root | 10.204.161.60:40134 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594346 | root | 10.204.161.60:40135 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594347 | root | 10.204.161.60:40136 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594348 | root | 10.204.161.60:40137 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594349 | root | 10.204.161.60:40138 | db_gfxy_gdb_239 | Sleep | 6 | | NULL | | 594402 | root | 10.182.41.191:55110 | mysql | Query | 0 | NULL | show full processlist |
Ip:10.190.249.204对应的id为573982，直接杀掉其id:
mysql> kill 573982;
即杀掉10.190.249.204的连接。
关于Mysql查看某个ip连接数及删除掉这个ip连接的方法
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。