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..@.@.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:

  1. 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 ?)

  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.

Michel Gokan Khan
  • 183
  • 1
  • 13
  • 1
    Save yourself the trouble of reinventing the wheel and take a look at [pt-query-digest](https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html) in [Percona Toolkit](https://www.percona.com/doc/percona-toolkit/2.2/index.html). –  Jul 28 '15 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 '15 at 22:45
  • The first link shows you how to use the tool with tcpdump, including the tcpdump options to capture the data. –  Jul 29 '15 at 00: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 '15 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. https://dev.mysql.com/doc/internals/en/client-server-protocol.html – Brian Jul 29 '15 at 21:00
  • @yoonix Okay it seems its really a pain in the 'ace' to 'reinvent the wheel' as you said! I asked another related question here: http://serverfault.com/questions/709512/is-it-possible-to-run-2-concurent-tcpdump-with-different-options – Michel Gokan Khan Jul 29 '15 at 21:16

0 Answers0