重庆思庄Oracle、Redhat认证学习论坛

标题: 在mysql 里面或操作系统看mysql 的会话情况 [打印本页]

作者: jikcheng 时间: 2014-11-10 16:16
标题: 在mysql 里面或操作系统看mysql 的会话情况
在mysql 交互式会话中查看当前连接情况。
(root@localhost) [(none)]> show processlist;
+-------+------+-----------------------+-------+---------+------+-------+------------------+
| Id    | User | Host                  | db    | Command | Time | State | Info             |
+-------+------+-----------------------+-------+---------+------+-------+------------------+
|     4 | root | localhost:52778       | mysql | Sleep   |    2 |       | NULL             |
| 1495 | root | 192.168.154.1:57040   | world | Sleep   | 1881 |       | NULL             |
| 10241 | root | 192.168.154.190:51707 | world | Sleep   | 649 |       | NULL             |
| 12671 | root | localhost             | NULL | Query   |    0 | init | show processlist |
| 13279 | root | localhost:38329       | mysql | Sleep   |   50 |       | NULL             |
| 13362 | root | localhost:38419       | mem   | Sleep   |    2 |       | NULL             |
+-------+------+-----------------------+-------+---------+------+-------+------------------+
6 rows in set (0.00 sec)

(root@localhost) [(none)]>

通过查表方式：
(root@localhost) [(none)]> select *from INFORMATION_SCHEMA.PROCESSLIST
    -> ;
+-------+------+-----------------------+-------+---------+------+-----------+---------------------------------------------+
| ID    | USER | HOST                  | DB    | COMMAND | TIME | STATE     | INFO                                        |
+-------+------+-----------------------+-------+---------+------+-----------+---------------------------------------------+
|     4 | root | localhost:52778       | mysql | Sleep   |   11 |           | NULL                                        |
| 1495 | root | 192.168.154.1:57040   | world | Sleep   | 2100 |           | NULL                                        |
| 12671 | root | localhost             | NULL | Query   |    0 | executing | select *from INFORMATION_SCHEMA.PROCESSLIST |
| 13708 | root | localhost:38782       | mem   | Sleep   |   10 |           | NULL                                        |
| 13675 | root | localhost:38746       | mem   | Sleep   |   11 |           | NULL                                        |
| 13707 | root | localhost:38781       | mem   | Sleep   |    8 |           | NULL                                        |
| 13279 | root | localhost:38329       | mysql | Sleep   |   29 |           | NULL                                        |
| 10241 | root | 192.168.154.190:51707 | world | Sleep   | 868 |           | NULL                                        |
+-------+------+-----------------------+-------+---------+------+-----------+---------------------------------------------+
8 rows in set (0.00 sec)

(root@localhost) [(none)]>

当前自己的会话信息：
(root@192.168.154.190) [world]> select connection_id()
    -> ;
+-----------------+
| connection_id() |
+-----------------+
|           10241 |
+-----------------+
1 row in set (0.02 sec)

(root@192.168.154.190) [world]>

然后杀死这个会话。
mysql>kill 10241
则这个会话出错。
(root@192.168.154.190) [world]> select connection_id()
    -> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
(root@192.168.154.190) [world]>

显示当前所有的连接会话（包括系统的）
(root@192.168.154.190) [world]> SHOW FULL PROCESSLIST
    -> ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    14556
Current database: world

+-------+------+-----------------------+-------+---------+------+-------+-----------------------+
| Id    | User | Host                  | db    | Command | Time | State | Info                  |
+-------+------+-----------------------+-------+---------+------+-------+-----------------------+
|     4 | root | localhost:52778       | mysql | Sleep   |    9 |       | NULL                  |
| 1495 | root | 192.168.154.1:57040   | world | Sleep   | 2622 |       | NULL                  |
| 12671 | root | localhost             | NULL | Sleep   |   71 |       | NULL                  |
| 14228 | root | localhost:39316       | mysql | Sleep   |   11 |       | NULL                  |
| 14499 | root | localhost:39600       | mem   | Sleep   |   10 |       | NULL                  |
| 14511 | root | localhost:39612       | mem   | Sleep   |   10 |       | NULL                  |
| 14512 | root | localhost:39613       | mem   | Sleep   |   11 |       | NULL                  |
| 14513 | root | localhost:39614       | mem   | Sleep   |   10 |       | NULL                  |
| 14514 | root | localhost:39615       | mem   | Sleep   |    9 |       | NULL                  |
| 14515 | root | localhost:39617       | mem   | Sleep   |   10 |       | NULL                  |
| 14516 | root | localhost:39616       | mem   | Sleep   |   10 |       | NULL                  |
| 14517 | root | localhost:39619       | mem   | Sleep   |   10 |       | NULL                  |
| 14518 | root | localhost:39620       | mem   | Sleep   |    9 |       | NULL                  |
| 14519 | root | localhost:39621       | mem   | Sleep   |   10 |       | NULL                  |
| 14520 | root | localhost:39622       | mem   | Sleep   |    9 |       | NULL                  |
| 14521 | root | localhost:39623       | mem   | Sleep   |    9 |       | NULL                  |
| 14522 | root | localhost:39624       | mem   | Sleep   |   10 |       | NULL                  |
| 14523 | root | localhost:39625       | mem   | Sleep   |    9 |       | NULL                  |
| 14524 | root | localhost:39627       | mem   | Sleep   |    9 |       | NULL                  |
| 14525 | root | localhost:39626       | mem   | Sleep   |    9 |       | NULL                  |
| 14526 | root | localhost:39630       | mem   | Sleep   |   10 |       | NULL                  |
| 14527 | root | localhost:39629       | mem   | Sleep   |    9 |       | NULL                  |
| 14528 | root | localhost:39632       | mem   | Sleep   |   10 |       | NULL                  |
| 14529 | root | localhost:39633       | mem   | Sleep   |   10 |       | NULL                  |
| 14530 | root | localhost:39634       | mem   | Sleep   |    9 |       | NULL                  |
| 14531 | root | localhost:39637       | mem   | Sleep   |    9 |       | NULL                  |
| 14532 | root | localhost:39636       | mem   | Sleep   |   10 |       | NULL                  |
| 14533 | root | localhost:39639       | mem   | Sleep   |   10 |       | NULL                  |
| 14534 | root | localhost:39640       | mem   | Sleep   |   10 |       | NULL                  |
| 14535 | root | localhost:39642       | mem   | Sleep   |   10 |       | NULL                  |
| 14536 | root | localhost:39643       | mem   | Sleep   |   10 |       | NULL                  |
| 14537 | root | localhost:39644       | mem   | Sleep   |   10 |       | NULL                  |
| 14538 | root | localhost:39645       | mem   | Sleep   |   10 |       | NULL                  |
| 14539 | root | localhost:39646       | mem   | Sleep   |    9 |       | NULL                  |
| 14540 | root | localhost:39647       | mem   | Sleep   |   10 |       | NULL                  |
| 14541 | root | localhost:39649       | mem   | Sleep   |   10 |       | NULL                  |
| 14542 | root | localhost:39650       | mem   | Sleep   |    9 |       | NULL                  |
| 14543 | root | localhost:39651       | mem   | Sleep   |    9 |       | NULL                  |
| 14545 | root | localhost:39653       | mem   | Sleep   |   10 |       | NULL                  |
| 14556 | root | 192.168.154.190:56159 | world | Query   |    0 | init | SHOW FULL PROCESSLIST |
+-------+------+-----------------------+-------+---------+------+-------+-----------------------+
40 rows in set (0.04 sec)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)

Powered by Discuz! X3.2