關於Mysql查看某個ip串連數及刪除掉這個ip串連的方法

標籤：linux   mysql   

統計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串連的方法