根据ip杀掉mysql连接_关于Mysql查看某个ip连接数及删除掉这个ip连接的方法

最新推荐文章于 2024-08-13 04:14:00 发布

Tengfei Jiang

最新推荐文章于 2024-08-13 04:14:00 发布

阅读量795

点赞数

文章标签：根据ip杀掉mysql连接

本文链接：https://blog.csdn.net/weixin_36342477/article/details/113235880

版权

本文介绍了在MySQL中统计各IP连接数的方法，并提供了通过IP查找并结束特定连接的步骤。首先，使用`SELECT SUBSTRING_INDEX(host, ':', 1) as ip, count(*) FROM information_schema.processlist GROUP BY ip;`来统计IP连接数，然后通过`SHOW FULL PROCESSLIST`检查详细状态，最后使用`KILL`命令结束指定ID的连接。" 135469927,7337247,认知障碍与计算机决策：准确性对比与未来趋势,"['人工智能', '大数据', '算法', '语言模型', '架构设计']

摘要由CSDN通过智能技术生成

统计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;

状态如下：

+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+

+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+

| 3 | tencentroot | :34481 | NULL | Sleep | 5 | | 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 |