Killing the process by using CONCAT command in MySQL / MariaDB

We have 2 options , either you can restart the MariaDB / MySQL  service which in turn will remove these thread or alternate option is to kill then either one by one or you can use below query to do the same in bulk. 

mysql -u<user> -p<password> -e "select concat('KILL ',id,';')  into outfile '/tmp/sleep_processes.txt' from information_schema.processlist where Command = 'Sleep' and USER not in ('system user', 'monitor_user');"

Note: List out all the USER  in above query which you don't want to kill , make sure none of the system_user or replication user defined should get killed , better you can add them in above query in section "....USER not in ('system user', 'monitor_user', '......','...........')""

Run the generated sleep_processes.txt output with in MariaDB server / MySQL Database server , it will kill all of the sleeping threads. 

mysql -u<user> -p<password> -e "source /tmp/sleep_processes.txt;"

For example :- 

[root@mysql_server ]# mysql -u root  -pxxxx  -e "select concat('KILL ',id,';')  into outfile '/tmp/sleep_processes.txt' from information_schema.processlist where Command = 'Sleep' and USER not in ('system user', 'monitor_user')"

[root@mysql_server tmp]# ls -ltr

-rw-rw-rw- 1 root root 21 Apr 27 03:09 sleep_processes.txt

[root@mysql_server tmp]# cat sleep_processes.txt

KILL 78;

KILL 156;

You can randomly verify the above thread id with in MariaDB server / MySQL Database Server to cross check none of the valid user get killed . 

[root@mysql_server tmp]# mysql -u root -pxxxxxx

Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 108.Server version: 10.5.6-4-MariaDB-enterprise-log MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show processlist;

+-----+-------------+-----------------------+------+---------+---------+-------------

| Id  | User        | Host                  | db   | Command | Time    | State              | Info             | Progress |

+-----+-------------+-----------------------+------+---------+---------+-------------

|   1 | system user |                       | NULL | Sleep   | 2075783 | wsrep aborter idle | NULL             |    0.000 |

|   2 | system user |                       | NULL | Sleep   | 2075783 | closing tables     | NULL             |    0.000 |

|  93 | maxmon_user   | 192.168.140.100:37858 | NULL | Sleep   |       0 |                    | NULL             |    0.000 |

| 104 | app_user    | 192.168.140.156:37156 | NULL | Sleep   |      47 |                    | NULL             |    0.000 |

| 108 | root        | localhost             | NULL | Query   |       0 | starting           | show processlist |    0.000 |

+-----+-------------+-----------------------+------+---------+---------+-------------

5 rows in set (0.000 sec)