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.