- 查看 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)
- 查看连接 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)
- 查看当前 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)
- 查看 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)
-
SUBSTRING_INDEX(str, delim, count) 按关键字截取字符串：
str: 被截取字段；
delim: 关键字；
count: 关键字出现的次数。
举例如下，截取字符串 “blog.zzvips.com” 中第二个 “.” 左边的子串：
mysql> select substring_index("blog.zzvips.com", ".", 2) as result;
+-----------+
| result |
+-----------+
| blog.zzvips |
+-----------+
1 row in set (0.01 sec)
备注：如果关键字出现的次数是负数，如 -2，则是从后倒数，到字符串结束。如下所示：
mysql> select substring_index("blog.zzvips.com", ".", -2) as result;
+----------+
| result |
+----------+
| zzvips.com |
+----------+
1 row in set (0.00 sec)
博客参考：
- MySQL查看所有连接的客户端ip
- mysql 函数substring_index()
- Mysql中查看每个IP的连接数
本文地址：https://blog.csdn.net/piaoranyuji/article/details/109464169