Who is behind the connections?

Periodically, situations arise when you want to understand what kind of process on the server is to blame for a particular connection to the DBMS. For example, there are a lot of connections to the database and I want to know where they are coming from. Or, there are some kind of “heavy” connections (through which some heavy requests go that slow down everything).

Is it even possible to find out this information? It turned out that this is nothing complicated ! However, each time with your hands it is rather dreary to establish correspondence. So why not automate the process?

Nothing is easier!

Who are you strangers?

So, to find out who is behind what connection in the automatic version, you can use this command: The output will be something like this: Yes, if you run the above command from outside the root, there will be some amount of slag, as you can see above. And, of course, there will be no way to find out the process launched from another user;) In general, there is little sense without root.

mysql -u -p -h -e "SHOW PROCESSLIST;" | perl -lne 'print $1 unless !/(:\d+)/' | while read port; do netstat -tp | grep $port; done



(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 server.local:44800 192.168.1.2:mysql ESTABLISHED 7476/mysql
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 server.local:35766 192.168.1.2:mysql TIME_WAIT -


What's next?

So, the process is recognized. How to understand that he does such a bad thing?

Universal tools for analyzing the running process (Google will help with a detailed description):

• strace - what's inside?
• netstat - what kind of connections does the process have?

PS In MySQL 5.1 there is a magnificent table INFORMATION_SCHEMA.PROCESSLIST, from where you can pull out data more flexibly.