How to view MySQL links and kill abnormal links

# Ordinary users can only see the links initiated by the current usermysql> select user();
+--------------------+
| user() |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------+-----------+--------+---------+------+----------+------------------+
| 769386 | testuser | localhost | NULL | Sleep | 201 | | NULL |
| 769390 | testuser | localhost | testdb | Query | 0 | starting | show processlist |
+--------+----------+-----------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist;
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 769386 | testuser | localhost | NULL | Sleep | 210 | | NULL |
| 769390 | testuser | localhost | testdb | Query | 0 | executing | select * from information_schema.processlist |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)

# After granting PROCESS permission, you can see the links of all usersmysql> grant process on *.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@% |
+----------------------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'testuser'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| 769347 | root | localhost | testdb | Sleep | 53 | | NULL |
| 769357 | root | 192.168.85.0:61709 | NULL | Sleep | 521 ​​| | NULL |
| 769386 | testuser | localhost | NULL | Sleep | 406 | | NULL |
| 769473 | testuser | localhost | testdb | Query | 0 | starting | show processlist |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

# View only the link information of a certain ID select * from information_schema.processlist where id = 705207;

# Filter out the links of a certain user select * from information_schema.processlist where user = 'testuser';

# Filter out all non-idle links select * from information_schema.processlist where command != 'Sleep';

# Filter out links with idle time of more than 600 seconds select * from information_schema.processlist where command = 'Sleep' and time > 600;

# Filter out links in a certain state select * from information_schema.processlist where state = 'Sending data';

# Filter the links of a certain client IP select * from information_schema.processlist where host like '192.168.85.0%';

# Kill the links that are idle for more than 600 seconds and concatenate them to get the kill statement select concat('KILL ',id,';') from information_schema.`processlist`
 where command = 'Sleep' and time > 600;

# Kill the links in a certain state and concatenate them to get the kill statement select concat('KILL ',id,';') from information_schema.`processlist`
 where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.`processlist`
 where state = 'Waiting for table metadata lock';

# Kill a link initiated by a user and concatenate the kill statement select concat('KILL ',id,';') from information_schema.`processlist`
  user = 'testuser';