MySQL 查看每个 IP 的连接数

1. 查看 MySQL 每个 IP 的连接数语句：

mysql> SELECT SUBSTRING_INDEX(HOST,':',1) AS ip , COUNT(*) FROM information_schema.processlist GROUP BY ip;
+--------------+----------+
| ip           | COUNT(*) |
+--------------+----------+
| 106.54.90.27 |        3 |
+--------------+----------+
1 row in set (0.00 sec)

2. 查看连接 MySQL 的 IP 和端口列表：

mysql> SELECT HOST AS ip FROM information_schema.processlist;
+--------------------+
| ip                 |
+--------------------+
| 106.54.90.27:32998 |
| 106.54.90.27:48800 |
| 106.54.90.27:54948 |
+--------------------+
3 rows in set (0.00 sec)

3. 查看当前 MySQL 数据库中，有哪些客户端保持了连接， 每个客户端分别保持了多少连接：

mysql> SELECT substring_index(host, ':',1) AS host_name, state, count(*) FROM information_schema.processlist GROUP BY state, host_name;
+--------------+-----------+----------+
| host_name    | state     | count(*) |
+--------------+-----------+----------+
| 106.54.90.27 |           |        2 |
| 106.54.90.27 | executing |        1 |
+--------------+-----------+----------+
2 rows in set (0.00 sec)

4. 查看 MySQL 每个 IP 的状态：

mysql> SELECT SUBSTRING_INDEX(HOST, ':',1) AS host_name, state FROM information_schema.processlist GROUP BY state, host_name;
+--------------+-----------+
| host_name    | state     |
+--------------+-----------+
| 106.54.90.27 |           |
| 106.54.90.27 | executing |
| 101.54.82.27 | query end |
+--------------+-----------+
3 rows in set (0.00 sec)

5. SUBSTRING_INDEX(str, delim, count) 按关键字截取字符串：
   str: 被截取字段；
   delim: 关键字；
   count: 关键字出现的次数。
   举例如下，截取字符串 “blog.jb51.net” 中第二个 “.” 左边的子串：

mysql> select substring_index("blog.jb51.net", ".", 2) as result;
+-----------+
| result    |
+-----------+
| blog.jb51 |
+-----------+
1 row in set (0.01 sec)

  备注：如果关键字出现的次数是负数，如 -2，则是从后倒数，到字符串结束。如下所示：

mysql> select substring_index("blog.jb51.net", ".", -2) as result;
+----------+
| result   |
+----------+
| jb51.net |
+----------+
1 row in set (0.00 sec)

  博客参考：

• MySQL查看所有连接的客户端ip
• mysql 函数substring_index()
• Mysql中查看每个IP的连接数

本文地址：https://blog.csdn.net/piaoranyuji/article/details/109464169