Who is behind the connections?

Periodically there are situations when you want to understand what kind of process on the server is guilty for a specific connection to the DBMS. For example, a lot of connections to the database and I want to know where they come from. Or, there are some "heavy" connections (according to which some heavy requests go, which inhibit everything).

Can it even get this information? It turned out that there is nothing difficult ! However, each time the hands to establish compliance is quite a chore. So why not automate the process?

Nothing is easier!

Who are you, strangers?

So, you can find out who is behind which connection in the automatic version with the following command:
')
mysql -u<user> -p<password> -h<host> -e "SHOW PROCESSLIST;" | perl -lne 'print $1 unless !/(:\d+)/' | while read port; do netstat -tp | grep $port; done

The output will be something like this:

(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 -

Yes, if you run the above command not under root, there will be some slag, as you can see above. And, of course, it will not be possible to find out the process started by another user;) In general, there’s little use without root.

What's next?

So, the process is recognized. How to understand that he is doing this bad?

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

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

PS MySQL 5.1 has a great INFORMATION_SCHEMA.PROCESSLIST table, from where you can more flexibly pull data.