I need to capture all mysql requests/responses using tcpdump and after that need to find out which response related to which request in order to caculate the response time of each request.
I used the following tcpdump code:
tcpdump -ixenbr0 -s 400 -n -A 'port 3306'
And got following result for a single SQL query (I copy pasted just one sample, there are more captured packets):
01:05:57.010702 IP 192.168.87.242.41775 > 192.168.87.243.3306: Flags [P.], seq 1881250501:1881250506, ack 259893378, win 1444, options [nop,nop,TS val 12664899 ecr 12667456], length 5
E..9..@.@.P...W...W../..p!...}......1b.....
..@C..J@.....
01:05:57.010907 IP 192.168.87.243.3306 > 192.168.87.242.41775: Flags [P.], seq 1:12, ack 5, win 1771, options [nop,nop,TS val 12667525 ecr 12664899], length 11
E..?.1@.@..I..W...W..../.}..p!......1h.....
..J...@C...........
01:05:57.010974 IP 192.168.87.242.41775 > 192.168.87.243.3306: Flags [.], ack 12, win 1444, options [nop,nop,TS val 12664899 ecr 12667525], length 0
E..4..@.@.P...W...W../..p!...}......1].....
..@C..J.
01:05:57.011028 IP 192.168.87.242.41775 > 192.168.87.243.3306: Flags [P.], seq 5:235, ack 12, win 1444, options [nop,nop,TS val 12664899 ecr 12667525], length 230
E.....@.@.P...W...W../..p!...}......2C.....
..@C..J......select se.socialeventid,se.title From SOCIALEVENT as se,PERSON_SOCIALEVENT as ps where se.socialeventid=ps.socialeventid and se.eventtimestamp>=CURRENT_TIMESTAMP and ps.username='ew4bp7yd2ie' ORDER BY se.eventdate ASC limit 3
01:05:57.011483 IP 192.168.87.243.3306 > 192.168.87.242.41775: Flags [P.], seq 12:242, ack 235, win 1771, options [nop,nop,TS val 12667525 ecr 12664899], length 230
E....2@.@..m..W...W..../.}..p!......2C.....
..J...@C.....A....def.olio.se.SOCIALEVENT^Msocialeventid^Msocialeventid.?...........1....def.olio.se.SOCIALEVENT.title.title...d.......................629.rrt yllpxmtluc cqwz .....979.ekgvvvxsbdei j n .....2590.bekmkggqmd apboxhxi .........
So imagine I have a very busy server with lots of request responses involved. So I have some questions:
How can I find out which one of these packets is a request ? (it should start with what ? what are these
E..9..@.@.P...W...W../..p!...}......1b.....
characters ?)How can I find out which one of these packets is a response ?
How can I find out which request packet belongs to which response packet ?
Any help would be appreciated.