Utilizing information how to interpret MySQL protocol from Understanding MySQL Internals, I developed the bash script below that can tell you when a client logs in and when they log out.
It's tested on low connection volume servers, doesn't require installation of any special packages, and can be adapted or extended to provide more logging capabilities.
It's tested on low connection volume servers, doesn't require installation of any special packages, and can be adapted or extended to provide more logging capabilities.
# tested against tcpdump 3.9.8
if [[ $1 ]] ; then
port=$1
else
port=3306
fi
echo "Monitoring port $port"
#The following tcpdump options are critical for the correct parsing by awk
/usr/sbin/tcpdump -Xttttln -s0 "tcp[13]== 24 and tcp[((tcp[12]>>4)*4)+13:4]==0 and tcp[((tcp[12]>>4)*4)+17:4]==0 and tcp[((tcp[12]>>4)*4)+21:4]==0 and tcp[((tcp[12]>>4)*4)+25:4]==0 and tcp[((tcp[12]>>4)*4)+29:4]==0 and tcp[((tcp[12]>>4)*4)+33:2]==0 and tcp[((tcp[12]>>4)*4)+35:1]==0 and dst port $port or (tcp[tcpflags] & (tcp-syn|tcp-fin)!=0 and dst port $port)" |
awk '\
BEGIN { print "Starting\n"; \
login_flag="off"; \
last_client=""; \
} \
{ \
#print ">>>.....",$0;
if ( $7 == "S" ) { \
delete login_name[last_client]; \
print $1, $2, "\t", $4, " connect"; \
client[$4]=$7; \
data_buf_flag[last_client]="off"; \
last_client=$4; \
} else { \
if ( $7 == "P" || $7 == ".") { \
client[$4]=client[$4] $7; \
data_buf_flag[last_client]="off"; \
last_client=$4; \
} else { \
if ( $7 == "F" ) { #a FIN packet \
print $1, $2, "\t", $4, " closed:", client[$4]; \
data_buf_flag[last_client]=="off"; \
delete client[$4]; \
last_client=$4; \
} else { #it must be raw data in hex \
if(data_buf_flag[last_client]=="off") { \
data_buf_flag[last_client]="on"; \
data_buf[last_client]=substr($0,10,40); \
} else { \
data_buf[last_client]=data_buf[last_client] substr($0,10,40); \
} \
# print "databuf: ", data_buf[last_client]; \
if(login_name[last_client]=="") {
pos=index(data_buf[last_client],"00 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000"); \
if ( pos || \
index(data_buf[last_client],"0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 00") > 0) { \
login_flag="on"; \
if ( pos) { \
login_name[last_client]=substr( \
data_buf[last_client],pos+57, \
index(substr(data_buf[last_client],pos+57),"00")-1 \
); \
} \
if (login_name[last_client]!="") { \
gsub(/ /,"",login_name[last_client]); \
printf( " %s: login name = ",last_client); \
loglen=length(login_name[last_client]); \
for(i=1;i<loglen;i+=2) \
printf("%c",strtonum("0x" substr(login_name[last_client],i,2))); \
print ""; \
} \
} \
} else { \
#print "login_name[last_client=]",login_name[last_client]; \
}
} \
} \
} \
}\
END { print "- done-";} \
'
No comments:
Post a Comment