MySQL show processlist过滤

背景

有客户需要根据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

https://dev.mysql.com/doc/refman/5.7/en/processlist-table.html

https://stackoverflow.com/questions/929612/how-to-customize-show-processlist-in-mysql