Linux下查看MySQL连接访问列表方法小结

     参加了一个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 NAMEmysqld  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 statusUptime: 9361  Threads: 3  Questions: 55  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.005mysql> 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)