How to examine and kill MySQL client processes


A useful MySQL command, when it comes to keeping track of client-server transactions, is SHOW PROCESSLIST. It lists all the open connections to the server, together with a thread ID, information on the requesting host, user and database, the query string, and the current state of the connection. The command itself is simple to run from the MySQL client:

mysql> SHOW PROCESSLIST;

You can take a look at sample output in Listing A.

Note that since this command displays currently executing queries, it is only available to users with the SUPER privilege (such as the MySQL root user). A number of different states are possible for each connection—the MySQL manual lists them, together with explanations of each.

In addition to viewing processes, users with the SUPER privilege can also kill running threads—for example, threads which have “gone zombie” and are either not responding or tying up server resources—with the KILL command. To kill a thread completely, use the KILL command followed by the thread ID returned by SHOW PROCESSLIST:

mysql> KILL 27;
 Query OK, 0 rows affected (0.05 sec)

To kill the query being executed by a thread but leave the connection active (yes, MySQL even allows such fine-grained control), use the KILL QUERY command instead, followed by the appropriate thread ID.

Copy from http://www.techrepublic.com/article/how-to-examine-and-kill-mysql-client-processes/5211762

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s