How to find MySQL process list and to kill those processes?

mysql kill-process
Ashish Dadhich picture Ashish Dadhich · May 26, 2017 · Viewed 226.2k times · Source

The MySQL database hangs, due to some queries.

How can I find the processes and kill them?

Answer

Ashish Dadhich picture Ashish Dadhich · May 26, 2017

Here is the solution:

  1. Login to DB;
  2. Run a command show full processlist;to get the process id with status and query itself which causes the database hanging;
  3. Select the process id and run a command KILL <pid>; to kill that process.

Sometimes it is not enough to kill each process manually. So, for that we've to go with some trick:

  1. Login to MySQL;
  2. Run a query Select concat('KILL ',id,';') from information_schema.processlist where user='user'; to print all processes with KILL command;
  3. Copy the query result, paste and remove a pipe | sign, copy and paste all again into the query console. HIT ENTER. BooM it's done.

Related questions

Query is locking tables, can't kill that process

I have a query locking tables in MySQL (using InnoDB): UPDATE table SET status = 2 WHERE status = 3 This query caused a deadlock on the table and can't bypass it. I tried to do: show processlist then kill the process id but …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More