Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have this setup where several applications are going through the load balancer (HAproxy) and accessing the MySQL database.

Several applications -> HAproxy -> MySQL database

I would like to know which specific application is accessing the database. When I did a show processlist on the database, it shows the IP of the HAproxy (of course). Then I tried to do lsof on haproxy and mysql database but it didn't help me (or I just don't know to interpret the results properly). And finally I checked the haproxy logs but it didn't show anything.

Thanks.

share|improve this question
    
This would be easier if you can reconfigure applications to use separate accounts to log into MySQL. –  sendmoreinfo Oct 21 '12 at 7:14
add comment

1 Answer 1

I was also looking for a way to trace haproxy connections back to specific processes on various machines. This dinky little hackery looks on the haproxy machine for a host that you give it. It then goes over to the source machine and looks for the connections through to the backend databases.

The most basic part is running the echo 'show sess' | socat stdio unix-connect:/tmp/haproxy on the haproxy server itself

#!/bin/bash
# comments here:

haproxy='<your haproxy target>'

if [ $# -ne 1 ]
then
echo ""
echo "usage: db_trace_haproxy_sessions.sh <hostname>"
echo "looks at connections from <hostname> to load balanced database and traces them back to processes"
echo ""
exit
fi

ip=`host $1 | awk '{print $4}' 2>/dev/null`
if [ "$ip" == "found:" ]
then
echo ""
echo "couldn't resolve $1"
echo ""
exit
fi

IFS="
"

for i in `ssh $haproxy "echo 'show sess' | socat stdio unix-connect:/tmp/haproxy | grep     $ip"`
do
# output example:
#0x2cd2780: proto=tcpv4 src=10.210.50.104:50791 fe=mysql be=mysql srv=db302 ts=04 age=1d11m calls=5 rq[f=909202h,l=0,an=00h,rx=7h59m,wx=,ax=] rp[f=109202h,l=0,an=00h,rx=7h59m,wx=,ax=] s0=[7,18h,fd=32,ex=] s1=[7,18h,fd=52,ex=] exp=7h47m

echo ""
db=`echo $i | awk '{print \$6}' | cut -d "=" -f2`
src=`echo $i | awk '{print \$3}' | cut -d "=" -f2`

echo "Source Connection: $src ... connected to $db"

ssh $1 "ps=\`netstat -naltp | grep $src | awk '{print \$7}' | cut -d "/" -f1\`; ps aux |     grep \" \$ps \" | grep -v grep"
done;
echo ""

Output looks like this:

db_trace_haproxy_sessions.sh appserver

Source Connection: 10.210.50.103:37114 ... connected to database319

root     19300  1.0  0.0  53944 16180 pts/0    S+   Feb19  16:02 /usr/bin/perl somePerlScript.pl 

Source Connection: 10.210.50.103:37115 ... connected to database142

root     19301  5.4  0.0  53940 16160 pts/1    D+   Feb19  81:41 someapachethread
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.