Mysql中通过processlist方式查询Mysql数据库当前连接的用户及会话信息

Bertha 。 2022-08-11 08:00 296阅读 0赞

在Mysql中，可以通过如下方式查询Mysql数据库当前连接的用户及会话信息：  
1. show full processlist

> mysql> show full processlist;  
> \+----------+-----------+--------------------+-------------+---------+------+-------+-----------------------+  
> | Id       | User      | Host               | db          | Command | Time | State | Info                  |  
> \+----------+-----------+--------------------+-------------+---------+------+-------+-----------------------+  
> | 26321235 | root      | localhost          | socialciv   | Sleep   |    3 |       | NULL                  |   
> | 26322405 | root      | localhost          | NULL        | Query   |    0 | NULL  | show full processlist |   
> | 26322588 | dmadbuser | 192.168.0.51:51395 | user\_silo\_0 | Sleep   |    0 |       | NULL                  |   
> | 26322621 | dmadbuser | 192.168.0.51:51400 | soooalciv   | Sleep   |    0 |       | NULL                  |   
> | 26322645 | dmadbuser | 192.168.0.51:51433 | user\_silo\_2 | Sleep   |    0 |       | NULL                  |   
> | 26322646 | dmadbuser | 192.168.0.52:42308 | user\_silo\_1 | Sleep   |    1 |       | NULL                  |   
> | 26322648 | dmadbuser | 192.168.0.51:51439 | user\_silo\_0 | Sleep   |    0 |       | NULL                  |   
> | 26322650 | dmadbuser | 192.168.0.51:51442 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> | 26322659 | dmadbuser | 192.168.0.51:51454 | user\_silo\_1 | Sleep   |    1 |       | NULL                  |   
> | 26322667 | dmadbuser | 192.168.0.51:51469 | soooalciv   | Sleep   |    0 |       | NULL                  |   
> | 26322671 | dmadbuser | 192.168.0.51:51475 | user\_silo\_0 | Sleep   |    0 |       | NULL                  |   
> | 26322674 | dmadbuser | 192.168.0.51:51481 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> | 26322677 | dmadbuser | 192.168.0.51:51485 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> | 26322678 | dmadbuser | 192.168.0.52:42356 | user\_silo\_2 | Sleep   |    0 |       | NULL                  |   
> | 26322679 | dmadbuser | 192.168.0.51:51486 | user\_silo\_0 | Sleep   |    0 |       | NULL                  |   
> | 26322680 | dmadbuser | 192.168.0.51:51487 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> | 26322681 | dmadbuser | 192.168.0.52:42359 | user\_silo\_2 | Sleep   |    0 |       | NULL                  |   
> | 26322684 | dmadbuser | 192.168.0.51:51493 | user\_silo\_2 | Sleep   |    0 |       | NULL                  |   
> | 26322685 | dmadbuser | 192.168.0.52:42367 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> | 26322688 | dmadbuser | 192.168.0.51:51500 | user\_silo\_2 | Sleep   |    0 |       | NULL                  |   
> | 26322689 | dmadbuser | 192.168.0.52:42368 | user\_silo\_0 | Sleep   |    0 |       | NULL                  |   
> | 26322690 | dmadbuser | 192.168.0.52:42371 | user\_silo\_1 | Sleep   |    0 |       | NULL                  |   
> \+----------+-----------+--------------------+-------------+---------+------+-------+-----------------------+  
>

2. mysqladmin -uroot -p -hlocalhost processlist

> \-bash-3.2$ mysqladmin -uroot -p -hlocalhost processlist  
> Enter password:   
> \+----------+-----------+--------------------+-------------+---------+------+-------+------------------+  
> | Id       | User      | Host               | db          | Command | Time | State | Info             |  
> \+----------+-----------+--------------------+-------------+---------+------+-------+------------------+  
> | 26309321 | dmadbuser | 192.168.0.53:33532 | soooalciv   | Sleep   | 2    |       |                  |  
> | 26309374 | dmadbuser | 192.168.0.53:33611 | soooalciv   | Sleep   | 2    |       |                  |  
> | 26309387 | dmadbuser | 192.168.0.53:33631 | user\_silo\_1 | Sleep   | 3    |       |                  |  
> | 26309390 | dmadbuser | 192.168.0.53:33636 | user\_silo\_1 | Sleep   | 0    |       |                  |  
> | 26309393 | dmadbuser | 192.168.0.53:33639 | user\_silo\_0 | Sleep   | 0    |       |                  |  
> | 26309419 | dmadbuser | 192.168.0.53:33677 | user\_silo\_2 | Sleep   | 2    |       |                  |  
> | 26309420 | dmadbuser | 192.168.0.53:33679 | user\_silo\_2 | Sleep   | 0    |       |                  |  
> | 26309424 | dmadbuser | 192.168.0.53:33685 | user\_silo\_1 | Sleep   | 2    |       |                  |  
> | 26309442 | dmadbuser | 192.168.0.51:33589 | user\_silo\_1 | Sleep   | 1    |       |                  |  
> | 26309445 | dmadbuser | 192.168.0.53:33723 | user\_silo\_0 | Sleep   | 1    |       |                  |  
> | 26309457 | dmadbuser | 192.168.0.51:33612 | user\_silo\_2 | Sleep   | 1    |       |                  |  
> | 26309459 | dmadbuser | 192.168.0.53:33745 | user\_silo\_1 | Sleep   | 1    |       |                  |  
> | 26309469 | dmadbuser | 192.168.0.53:33759 | user\_silo\_2 | Sleep   | 0    |       |                  |  
> | 26309470 | dmadbuser | 192.168.0.53:33760 | user\_silo\_2 | Sleep   | 0    |       |                  |  
> | 26309471 | dmadbuser | 192.168.0.51:33632 | user\_silo\_2 | Sleep   | 0    |       |                  |  
> | 26309472 | dmadbuser | 192.168.0.53:33763 | user\_silo\_2 | Sleep   | 0    |       |                  |  
> | 26309473 | root      | localhost          |             | Query   | 0    |       | show processlist |  
> \+----------+-----------+--------------------+-------------+---------+------+-------+------------------+  
>

max\_connections 参数可以用于控制数据库的最大连接数：

> mysql> show variables like '%connect%';  
> \+--------------------------+-------------------+  
> | Variable\_name            | Value             |  
> \+--------------------------+-------------------+  
> | character\_set\_connection | latin1            |   
> | collation\_connection     | latin1\_swedish\_ci |   
> | connect\_timeout          | 10                |   
> | init\_connect             |                   |   
> | max\_connect\_errors       | 10                |   
> | max\_connections          | 4000              |   
> | max\_user\_connections     | 0                 |   
> \+--------------------------+-------------------+  
>   
>

以下命令用于查询Mysql数据库的状态信息：

> mysqladmin  -uroot -p -hlocalhost status  
>   
> \-bash-3.2$ mysqladmin  -uroot -p -hlocalhost status  
> Enter password:   
> 
> 
> Uptime: 540120  Threads: 21  Questions: 306833244  Slow queries: 37  Opens: 320214  Flush tables: 19  Open tables: 64  Queries per second avg: 568.083
> 
>   
> 
> 
> 转：http://www.eygle.com/archives/2012/01/mysql\_processlist.html