How to find an errant MySQL client

A common story: You’ve got some connection, either it’s busy running something it shouldn’t be, it’s in Sleep but holding some important lock, or you just don't know why it's connected to your database server in the first place. You see it in your SHOW PROCESSLIST like so:

mysql> show processlist G
*************************** 1. row ***************************
     Id: 5979887
   User: root
   Host: localhost:55997
     db: NULL
Command: Sleep
   Time: 475
  State: 
   Info: NULL

How do you find that client, especially if it's on another host? MySQL is providing you all the information you need above: localhost:55997. Of course localhost is the host or IP address, and 55997 is the source port of the socket; the port number (usually randomly assigned) on the far end of the socket, from the MySQL server's perspective. You can turn that number into something useful—the PID and user—, by running the following command on the host that made the connection:

# lsof -nPi :55997
COMMAND  PID  USER   FD   TYPE    DEVICE SIZE NODE NAME
mysqld  5026 mysql 1654u  IPv4 303329996       TCP 127.0.0.1:3306->127.0.0.1:55997 (ESTABLISHED)
mysql   9146 jcole    3u  IPv4 303329995       TCP 127.0.0.1:55997->127.0.0.1:3306 (ESTABLISHED)

(Note that here you can see both sides of the socket, since I'm running these commands on localhost. Disregard the first entry, as it's the half of the connection owned by the MySQL server.)

You can then find out the full command-line of the process with ps:

# ps -fp 9146
UID        PID  PPID  C STIME TTY          TIME CMD
jcole     9146  8740  0 12:53 pts/3    00:00:00 mysql -h 127.0.0.1 -u root -p

And see what it's doing with strace (waiting on a read of stdin in this dumb test):

# strace -p 9146
Process 9146 attached - interrupt to quit
read(0,  <unfinished ...>
Process 9146 detached

I hope that's helpful! It's a pretty common debugging trick for me.

3 thoughts on “How to find an errant MySQL client

  1. Tweets that mention jcole’s weblog: Jeremy Cole’s take on life. » Blog Archive » How to find an errant MySQL client -- Topsy.com

  2. what kind of MySQL patch you used? i check my 5.0 and 5.1 mysql “show processlist” find on port in local connection

    *************************** 13. row ***************************
    Id: 157
    User: root
    Host: localhost
    db: NULL
    Command: Query
    Time: 0
    State: NULL
    Info: show processlist

    • justlooks: If you connect with Unix socket (the default) there is no TCP port, and thus none is shown. If you want to force a localhost connection to use TCP/IP, use -h 127.0.0.1.

What do you think?

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