Daily Tasks - Sessions

1> To grab list of count of sessions connected to Database group by host.

mysql> select distinct substring_index(host,':',1) "host", count(user) "count" from information_schema.processlist group by substring_index(host,':',1);

Output:
+-------------------------------------------+-------+
| host                                                    | count |
+-------------------------------------------+-------+
| test1                                                   |   150 |
| test2                                                   |   150 |
+-------------------------------------------+-------+
2 rows in set (0.00 sec)


2> Kill connections of all users connected to DB from backend

mysql> SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user in ('test')  INTO outfile '/tmp/killSessions001.txt';
mysql> source /tmp/killSessions001.txt;

Output:
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Note:
1> Don't use it in production as it may cause some thread hang which results in high CPU and I/O and lead to DB crash. If face some situation, stop the services and restart Database immediately.
2> This is useful, when you need to apply some DDL patch at some DEV/STAG env. while application is running, and don't want to stop all services box.