背景
有客户需要根据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: PROCESSLIST
Create 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参考
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
