About MySQL View the number of IP connections and remove this IP connection method

Count the total number of MySQL connections for each IP:

Mysql> Select Substring_index (Host, ': ', 1) as IP, COUNT (*) from the information_schema.processlist group by IP;

The status is as follows:

+----------------+----------+| IP |                COUNT (*) |+----------------+----------+|        | 3 | |        10.182.41.191 | 1 | |        10.190.249.204 | 1 | |       10.204.161.60 | 10 | |        localhost | 1 | +----------------+----------+

If ip10.190.249.204 has a process that is connecting to MySQL, we will kill it.

Note: There are other ways to view the total number of MySQL IP connections, such as the MySQL command on Linux to view their status remotely.

Mysql-u root-h127.0.0.1-e "show processlist\g;" | Egrep "Host\:" | Awk-f: ' {print $} ' | Sort | Uniq-c

Or

Mysql-u root-h127.0.0.1--skip-column-names-e "show processlist;" | awk ' {print $} ' |awk-f ': ' ' {print '} ' |sort|uniq–c

Use the following command to see the specific connection status of this 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 |                                                         &nbsP;                | 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        &nBsp;    | 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 the corresponding ID is 573982, directly kill its ID:

Mysql> kill 573982;

That kills 10.190.249.204 of the connection.

About MySQL View the number of IP connections and remove this IP connection method