Database Madness

Killing User Connections

MySQL

To kill a user's connection from MySQL (ie. a PHP process, CLI client, etc), you need
to figure out the process id ("pid") of that connection, first. You can do this like so:

mysql> show processlist;
+---------+----------+-----------+------+---------+------+-------+------------------+
| Id      | User     | Host      | db   | Command | Time | State | Info             |
+---------+----------+-----------+------+---------+------+-------+------------------+
| 1177101 | root     | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+---------+----------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.01 sec)

In this example, there is one connection (mine, from the CLI). That is the one I'd like to
disconnect, so:

mysql> kill 1177101;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1177102
Current database: *** NONE ***

At this point, the connection has gone away, and your CLI client will reconnect automatically.

PostgreSQL

PostgreSQL's method is decidedly less elegant. You look up the connection you want to nuke in the pg_stat_activity:

thedatabase=> select * from pg_stat_activity;
  datid  |   datname    | procpid | usesysid | usename  |          current_query          | waiting |           xact_start          |          query_start          |         backend_start         | client_addr | client_port
---------+--------------+---------+----------+----------+---------------------------------+---------+------------------------------+-------------------------------+-------------------------------+-------------+-------------
  301502 | thedatabase  |   67951 |    16384 | username | <IDLE>                          | f       |                              | 2010-08-26 16:11:20.257573+02 | 2010-08-26 14:09:38.259673+02 | 127.0.0.1   |       55453
  301502 | thedatabase  |   68825 |    16384 | username | <IDLE>                          | f       |                              | 2010-08-26 16:47:16.213753+02 | 2010-08-26 16:19:04.422091+02 | 127.0.0.1   |       60130
  301502 | thedatabase  |   68751 |    16384 | username | <IDLE>                          | f       |                              | 2010-08-26 16:47:51.891256+02 | 2010-08-26 16:07:28.045694+02 | 127.0.0.1   |       51628

Then you head out into your favourite shell and kill the procpid of the needed process.