mysql show 过滤_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

参考