2

The output from 'mysqladmin processlist' looks like this:

+-------+------+-----------+-------+---------+------+-------------------+------------------------+
| Id    | User | Host      | db    | Command | Time | State             | Info                   |
+-------+------+-----------+-------+---------+------+-------------------+------------------------+
| 4411  | root | localhost | mydb1 | Sleep   | 86   |                   |                        |
| 12911 | root | localhost | mydb1 | Sleep   | 50   |                   |                        |
| 65142 | root | localhost | mydb1 | Query   | 4    | copy to tmp table | OPTIMIZE TABLE my_tble |
| 65428 | root | localhost |       | Query   | 0    |                   | show processlist   |
+-------+------+-----------+-------+---------+------+-------------------+------------------------+

From an 'Id' value in that table, how can I trace back to the PID of the process which created the connection, to figure out who is doing what? All the connections are via a local socket.

3 Answers 3

2

I would recommend using multiple user accounts. An alternative is to keep a connection history by writing out connection id, process id, script name, and a date/time stamp to a log file or table. I don't know of any other way to track this information.

2

There is no such mapping in mysql.

1

I have answered this in my tech blog here: http://blog.fotios.org/2018/02/find-which-process-holds-particular.html

Basically, there's two steps: 1) do a "show full processlist;" in mysql; this listing includes the port number of each socket/connection, 2) grep for that number in the "netstat -np" output

2
  • Please note that this solution does not work when you make your connection literally via localhost, since mysql client treats localhost specially and connects using unix socket file, thus omitting TCP/IP. However, when using 127.0.0.1, mysql defaults to TCP/IP. Reference: dev.mysql.com/doc/refman/8.0/en/connecting.html Jan 14, 2019 at 13:20
  • @MichałSacharewicz The post is not about identifying connections to mysqld from mysql client which is often moot. Regardless, I would think "show full processlist" would still list any unix socket based connections which are then also not excluded from the listing in "netstat -np" Jan 15, 2019 at 15:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .