Sometimes we may have to kill several MySQL connections / processes, How can we do that effortlessly ? We have to first list down the connections you want to kill with ” SHOW FULL PROCESSLIST ” (Query 1 ), The Query 2 below will do mass killing MySQL connections / processes quickly .
Query 1 mysql> SHOW FULL PROCESSLIST\G; *************************** 1. row *************************** Id: 3 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST *************************** 2. row *************************** Id: 15008 User: root Host: localhost db: mysqlslap Command: Sleep Time: 0 State: Info: NULL *************************** 3. row *************************** Id: 19759 User: root Host: localhost db: mysqlslap Command: Query Time: 0 State: query end Info: INSERT INTO t1 VALUES (95275444,'bNIrBDBl81tjzdvuOpQRCXgX37xGtzLKEXBIcE3k7xK7aFtqxC99jqYnpTviK83bf6lGDgsKd4R3KLmHPnI8TqnIKj1gjw7N2sXFZNS2Svyg8cpZN7atxL39w4igsp') *************************** 4. row *************************** Id: 19760 User: root Host: localhost db: mysqlslap Command: Sleep Time: 0 State: Info: NULL *************************** 5. row *************************** Id: 19761 User: root Host: localhost db: mysqlslap Command: Query Time: 0 State: query end Info: INSERT INTO t1 VALUES (95275444,'bNIrBDBl81tjzdvuOpQRCXgX37xGtzLKEXBIcE3k7xK7aFtqxC99jqYnpTviK83bf6lGDgsKd4R3KLmHPnI8TqnIKj1gjw7N2sXFZNS2Svyg8cpZN7atxL39w4igsp') *************************** 6. row *************************** Id: 19762 User: root Host: localhost db: mysqlslap Command: Query Time: 0 State: query end Info: INSERT INTO t1 VALUES (95275444,'bNIrBDBl81tjzdvuOpQRCXgX37xGtzLKEXBIcE3k7xK7aFtqxC99jqYnpTviK83bf6lGDgsKd4R3KLmHPnI8TqnIKj1gjw7N2sXFZNS2Svyg8cpZN7atxL39w4igsp') *************************** 7. row *************************** Id: 19763 User: root Host: localhost db: mysqlslap Command: Query Time: 0 State: query end Info: INSERT INTO t1 VALUES (95275444,'bNIrBDBl81tjzdvuOpQRCXgX37xGtzLKEXBIcE3k7xK7aFtqxC99jqYnpTviK83bf6lGDgsKd4R3KLmHPnI8TqnIKj1gjw7N2sXFZNS2Svyg8cpZN7atxL39w4igsp')
The query below get you the script for mass killing of MySQL in one key stroke , I usually use this when I am suspecting something unacceptable is happening in MySQL.
Query 2
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 21159; |
| KILL 21158; |
| KILL 21157; |
| KILL 21156; |
| KILL 21155; |
| KILL 21154; |
| KILL 21153; |
| KILL 21152; |
| KILL 21151; |
| KILL 21150; |
| KILL 21149; |
| KILL 21148; |
| KILL 21147; |
| KILL 21146; |
| KILL 21145; |
| KILL 21144; |
| KILL 21143; |
| KILL 21142; |
| KILL 21141; |
| KILL 21140; |
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/var/lib/mysql-files/kill-mysql-conn.txt';
Query OK, 1 row affected (0.00 sec)
mysql> source /var/lib/mysql-files/kill-mysql-conn.txt
