服务器之家

服务器之家 > 正文

MySQL 查看每个 IP 的连接数

时间:2020-11-05 22:15     来源/作者:piaoranyuji
  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)
  1. 查看连接 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)
  1. 查看当前 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)
  1. 查看 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)
  1. 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

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址 2020-08-12
最新idea2020注册码永久激活(激活到2100年)
最新idea2020注册码永久激活(激活到2100年) 2020-07-29
返回顶部