Using /usr/sbin/tcpdump -i eth1 -c 100000 -s0 -x -nn -q -tttt ‘port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2’ | /usr/bin/mk-query-digest –type=tcpdump :

100000 packets captured
195794 packets received by filter
95664 packets dropped by kernel

And now using /usr/sbin/tcpdump -i eth1 -c 100000 -s0 -w /tmp/snoopfile ‘port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2’; /usr/sbin/tcpdump -r /tmp/snoopfile -s0 -x -nn -q -tttt | /usr/bin/mk-query-digest –type=tcpdump :

100000 packets captured
100286 packets received by filter
220 packets dropped by kernel

207815 packets captured
214152 packets received by filter
6324 packets dropped by kernel

In some cases, I’m sure that host filtering would work fine, eg when you want to watch a specific host, but in some situations I’ve faced it does not work well. Honestly, I’m not sure why the packet drops were so high when filtering just a few hosts, perhaps the CPU overhead is higher when applying a host IP filter versus a port bitmask filter?

So one could use instead “dst port 3306” to match mysql server and do some portfiltering on tcp[0,1] I would prefer hosts.

See http://www.tcpdump.org/tcpdump_man.html for more in-depth info on TCP header structure.

tcp[1] & 7 == 2 and tcp[3] & 7 == 2

erkules, “dst port 3306″ won’t work because you must also capture the reply from mysqld, where the source port is 3306 and the dest port is unknown (matches & 7 == 2).

In some cases, I’m sure that host filtering would work fine, eg when you want to watch a specific host, but in some situations I’ve faced it does not work well. Honestly, I’m not sure why the packet drops were so high when filtering just a few hosts, perhaps the CPU overhead is higher when applying a host IP filter versus a port bitmask filter?

Ahh of course no effect is wrong. At least of the ports got to be 3306 (port=3306) the AND check for that dst/src-port is fulfilled anyway (3306) and the other one got to fullfill &7 == 2.

So one could use instead “dst port 3306″ to match mysql server and do some portfiltering on tcp[0,1] I would prefer hosts.

On this example it has no effect, as port 3306 is more precise. tcp[0,1] sourceport and tcp[2,3] dstport (rfc 793).

tcp[x] indicates the x’th octet in the TCP header, starting from 0. The first two octets are the source port, second two octets are the destination port. So my understanding is that this expression says: the lower octet of each source and dest port, when AND’ed with the bitmask ’111′ result in a value of ’010′, or the number 2. Note that “3306 & 7 == 2″ is a true statement.

See http://www.tcpdump.org/tcpdump_man.html for more in-depth info on TCP header structure.

I’m not very fresh on my TCP headers and I don’t remember what’s in various positions. What is the net effect of this expression?

tcp[1] & 7 == 2 and tcp[3] & 7 == 2

Devananda .. right I missed that.

Another approach, if you have the hardware: enable port mirroring on your switch, and capture the traffic on an otherwise idle host with enough disk bandwidth to do so.

I just used this trick, worked like a charm.

207815 packets captured
214152 packets received by filter
6324 packets dropped by kernel