0

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..@[email protected]../..p!...}......1b.....
[email protected]@.....
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@[email protected]..../.}..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..@[email protected]../..p!...}......1].....
[email protected].
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.....@[email protected]../..p!...}......2C.....
[email protected] 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@[email protected]..../.}..p!......2C.....
[email protected]^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:

  1. How can I find out which one of these packets is a request ? (it should start with what ? what are these E..9..@[email protected]../..p!...}......1b..... characters ?)

  2. How can I find out which one of these packets is a response ?

  3. How can I find out which request packet belongs to which response packet ?

Any help would be appreciated.

Improve this question
6
  • 1
    Save yourself the trouble of reinventing the wheel and take a look at pt-query-digest in Percona Toolkit.
    – user143703
    Jul 28, 2015 at 22:31
  • @yoonix the reason im using tcpdump is to have MAXIMUM performance possible. Very high performanve really matters in the application we are using. Also we already did most of our coding from other results of tcpdump so we have to continue with it as much as possible.
    – Michel Gokan Khan
    Jul 28, 2015 at 22:45
  • The first link shows you how to use the tool with tcpdump, including the tcpdump options to capture the data.
    – user143703
    Jul 29, 2015 at 0:12
  • @yoonix I tried it but its not really what I'm looking for. I have to implement it with my current tcpdump and just want to see how can I find out which request belongs to which response.
    – Michel Gokan Khan
    Jul 29, 2015 at 20:08
  • The client/server protocol is documented but is is also versioned (changes over time) so anything examining the raw packets would need to be kept updated with client protocol revisions. dev.mysql.com/doc/internals/en/client-server-protocol.html
    – Brian
    Jul 29, 2015 at 21:00

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .