背景

有客户需要根据processlist做一些监控，需要用到过滤上面的操作。

根据某个用户过滤

mysql> select * from information_schema.processlist where User='UserName';+----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+| ID       | USER       | HOST                | DB   | COMMAND          | TIME     | STATE                                                            | INFO |+----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+| 18396093 | UserName | *.*.*.*:47712 | NULL | Binlog Dump GTID |  4608287 | Master has sent all binlog to slave; waiting for binlog to be up | NULL |+----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+1 rows in set (0.00 sec)

杀死某些线程

mysql> select concat('kill ',ID,';') from information_schema.processlist where User='UserName';+------------------------+| concat('kill ',ID,';') |+------------------------+| kill 18396093;         || kill 22658331;         || kill 482;              |+------------------------+3 rows in set (0.00 sec)

监控统计每个用户的访问

mysql> select User,count(*) as cnt from information_schema.processlist group by user;+-----------------+-----+| User            | cnt |+-----------------+-----+| User1         | 168 || rep      |   1 || User2            |   2 |+-----------------+-----+3 rows in set (0.00 sec)

监控其他信息

凡是在processlist里面的都可以用来做过滤

mysql> show create table information_schema.processlist\G*************************** 1. row ***************************       Table: PROCESSLISTCreate Table: CREATE TEMPORARY TABLE `PROCESSLIST` (  `ID` bigint(21) unsigned NOT NULL DEFAULT '0',  `USER` varchar(16) NOT NULL DEFAULT '',  `HOST` varchar(64) NOT NULL DEFAULT '',  `DB` varchar(64) DEFAULT NULL,  `COMMAND` varchar(16) NOT NULL DEFAULT '',  `TIME` int(7) NOT NULL DEFAULT '0',  `STATE` varchar(64) DEFAULT NULL,  `INFO` longtext) ENGINE=MyISAM DEFAULT CHARSET=utf8