How to check and kill long running MySQL query

Answer:

1. Get into the MySQL shell, type 'SHOW PROCESSLIST' to see which query is blocking

mysql> SHOW PROCESSLIST;

2. It will print out all the current executing queries, and you will find the thread Id.

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

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

+-----+-------------+--------------------+-------+---------+-------+-
| 3928 |root  |192.168.1.5:59202 |db1 |Query    | 84   | Locked  |

select * from table1 where name like '%test%' 

3. Remember this Id, to kill it, use the "KILL" command

mysql> KILL 3928;

Query OK, 0 rows affected (0.05 sec)