参加了一个MySQL运维的面试,有一个没有回答上的问题,用什么Linux命令可以查看监控MySQL服务器有哪些连接访问。当时脑子里只想到MySQL里的“ SHOW PROCESSLIST ”和“ information_schema ”库里的“ PROCESSLIST ”表。因为平时自己查看MySQL进程列表就是用纯MySQL的方式。至于Linux命令,一下子有点反应不及。

     下来之后查了一下,找到了方法,就是“ lsof ”命令了。lsof( list open files )用于查看Linux系统打开的文件,因为Linux是文件型的操作系统,所以所有的系统操作最终都体现在文件I/O上。对于MySQL的连接,可以通过“ -i ”选项监控其访问端口(默认3306)。

     至此,查看MySQL服务器的连接访问,至少有以下四种方法:一种是Linux命令法;另外三种是MySQL进程列表法。

①Linux lsof命令

mysql> system lsof -i:3306 | grep -v \*
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1063 mysql   35u  IPv6  70220      0t0  TCP 123.123.123.123:mysql->123.123.123.1:55675 (ESTABLISHED)
mysqld  1063 mysql   36u  IPv6  73923      0t0  TCP 123.123.123.123:mysql->123.123.123.1:56990 (ESTABLISHED)

②MySQL安装自带mysqladmin命令行小公举(查看status选项输出中的“ Threads ”值)

mysql> system mysqladmin status
Uptime: 9361  Threads: 3  Questions: 55  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.005
mysql> system mysqladmin processlist status
+----+------+---------------------+--------------------+---------+------+----------+------------------+
| Id | User | Host                | db                 | Command | Time | State    | Info             |
+----+------+---------------------+--------------------+---------+------+----------+------------------+
|  6 | root | 123.123.123.1:55675 | NULL               | Sleep   | 2029 |          | NULL             |
|  8 | root | 123.123.123.1:56990 | information_schema | Sleep   |  277 |          | NULL             |
|  9 | root | localhost           | NULL               | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+---------------------+--------------------+---------+------+----------+------------------+
Uptime: 9363  Threads: 3  Questions: 53  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.005

③MySQL show命令(root账号可以加上FULL查询所有客户端连接)

mysql> SHOW PROCESSLIST;
+----+------+---------------------+--------------------+---------+------+----------+------------------+
| Id | User | Host                | db                 | Command | Time | State    | Info             |
+----+------+---------------------+--------------------+---------+------+----------+------------------+
|  6 | root | 123.123.123.1:55675 | NULL               | Sleep   | 2029 |          | NULL             |
|  8 | root | 123.123.123.1:56990 | information_schema | Sleep   |  277 |          | NULL             |
|  9 | root | localhost           | NULL               | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+---------------------+--------------------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

④MySQL表查询

mysql> SELECT * FROM `information_schema`.`PROCESSLIST`;
+----+------+---------------------+--------------------+---------+------+-----------+--------------------------------------------------+
| ID | USER | HOST                | DB                 | COMMAND | TIME | STATE     | INFO                                             |
+----+------+---------------------+--------------------+---------+------+-----------+--------------------------------------------------+
|  9 | root | localhost           | NULL               | Query   |    0 | executing | SELECT * FROM `information_schema`.`PROCESSLIST` |
|  8 | root | 123.123.123.1:56990 | information_schema | Sleep   |  282 |           | NULL                                             |
|  6 | root | 123.123.123.1:55675 | NULL               | Sleep   | 2034 |           | NULL                                             |
+----+------+---------------------+--------------------+---------+------+-----------+--------------------------------------------------+
3 rows in set (0.00 sec)

Logo

更多推荐