MYSQL状态查询

查询当前用户以及器加密方式

SELECT user, host, plugin FROM mysql.user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | caching_sha2_password |
| your_user        | localhost | mysql_native_password |
| another_user     | localhost | caching_sha2_password |
| ...              | ...       | ...                   |
+------------------+-----------+-----------------------+

MySQL服务器允许的最大连接数 ：

SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

MySQL服务器自启动以来使用的最大连接数 ：

show status like 'max_used_connections'

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 100   |
+----------------------+-------+

查看当前所有连接的详细信息 ：

SHOW full PROCESSLIST;

这个命令将显示当前MySQL服务器上的所有活动连接，并提供每个连接的详细信息，例如ID、用户、主机、数据库、命令、时间、状态和查询等

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | test | Query   |    0 | init  | SHOW FULL PROCESSLIST |
|  2 | root | localhost | test | Sleep   |   10 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+

查询每个用户从每个主机连接到数据库的连接数 ：

SELECT user, host, COUNT(*) 
FROM information_schema.processlist 
GROUP BY user, host;

查看数据库 有关当前连接的详细信息 ：

SELECT id, user, host, db, command, time, state, COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY id, user, host, db, command, time, state;

查询当前连接到数据库的线程数：

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Threads_connected 的变量的当前值。这是一个表示当前打开的连接数的状态变量。

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 10    |
+-------------------+-------+