« Mysql数据库 mysql-bin 日志的管理与维护 | Blog首页 | TNS-00583 错误解决案例一则 »

Mysql数据库 当前连接用户及会话查询

在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

供参考。

By eygle on 2012-01-10 08:02 | Comments (0) | Web | 2943 |

Mysql