Tcpdump Learning (3): MySQL Query

In MySQL online environment we generally only open binary Log,slow log, and sometimes we need to see the general log? Since the log records all requests, opening the logs will definitely put a lot of pressure on the disk, and the IO capability is reduced, so the log line is generally not open, and that's when tcpdump shines.

Tcpdump usage is not complicated, the output is more complex, if you are very familiar with the TCP/IP protocol, then the output for you is a small kiss. We only care about MySQL query, so the output is very simple, is the daily query statement.

1. Simple to use (shell combined with Perl filter query)

[Email protected] ~]#CatQuery.SH #!/bin/Bashtcpdump-I. any-s0-L-W-DST Port3306| Strings |Perl-E' while(<>) {chomp; nextif/^[^ ]+[ ]*$/; if(/^ (select| update| delete| Insert| Set| Commit| rollback| create| drop| alter| Call)/i) {if(defined $q) {Print"$q \ n"; } $q=$_; } Else {        $_ =~ s/^[\t]+//; $q. = "$_";    }}'[Email protected] ~]#

For each parameter description please read the link at the end of the article.
Executing the above script, executing the query in another window, I used the sysbench for the stress test, and finally crawled the following results:

Tcpdump:listening on any, link-type linux_sll (LINUX cooked), capture size65535bytes4{@ h{@ h ' @? h'f$ H f$ H of$ h cf$ h wf$? h "f$? h# f%? h# f% h| @ Lg @ H + f% <x _ h/f%? h/f% Ha @ #d +/#d?/#d s/#d G /#d {/Hh @ H} @ <e _ l| @ Hb @? h? f&? HM F'? HN F'? HZ f (#d3/h @ <x _ <x _ h~ @ Hc @ H} @ S 5 R,) r @) R T) r H) R v) r! ) h~ f+ r+) N9 O f+#: f+#: #dM/lj @ Hd @ #d_/show TABLE STATUS like'Sbtest'SELECT C from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?INSERT into sbtest values (?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') SELECT c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?INSERT into sbtest values (?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') SELECT c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?INSERT into sbtest values (?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') SELECT c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?

In fact, there is a simpler way to use Tcpflow

The command to fetch the data is as follows:

3306 grep " Select|insert|update|delete|replace " sed ' s%\ (. *\) \ ([.] \{4\}\) \ (. *\)%\3%'  

The output results are as follows:

tcpflow[9461]: Listening on anyselect c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?INSERT into sbtest values (?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') SELECT c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and?ORDER by Cupdate Sbtest set K=k+1whereID=?UPDATE Sbtest Set C=? whereID=?DELETE from Sbtest whereID=?INSERT into sbtest values (?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy') SELECT c from Sbtest whereID=?SELECT C from Sbtest whereIDBetween? and?SELECT SUM (K) from Sbtest whereIDBetween? and?SELECT C from Sbtest whereIDBetween? and?ORDER by Cselect DISTINCT C from Sbtest whereIDBetween? and? ORDER BY C

Finally say pt-query-digest, this tool is included in the Percona-toolkit, in the analysis of slow query is very good, the specific usage of everyone to visit the official website.

After we grab the bag through tcpdump, we analyze it through the--type tcpdump option, the simple usage is as follows:

65535  + 3306 >  for full protocol decodelistening on any, link65535  Bytespackets Capturedpackets received by filterpackets dropped by Kernel[[email protected]-mysql-server ~]#

Then using the Pt-query-digest tool for analysis, the usage is relatively simple

[Email protected] ~]# Pt-query-digest--type tcpdump mysql.tcp.txt # 340ms User Time, 50ms system Time,24.38MRss205.10Mvsz# CurrentDate: Thu June - Geneva: -: the  the# Hostname:yayun-mysql-server# files:mysql.tcp.txt# Overall: -Total1Unique115.61QPS,0. 02x Concurrency _____________# time range: the- .- - Geneva: -:11.127883ToGeneva: -:11.300885# Attribute Total min Max avg the%StdDev median#============     ======= ======= ======= ======= ======= ======= =======# Exec Time3ms 51us 862us 171us 568us 191us 84us# Rows affecte0       0       0       0       0       0       0# Query Size -       5       5       5       5       0       5# Warning Coun0       0       0       0       0       0       0# profile# Rank Query ID Response TimeCalls R/call v/M item#==== ================== ============= ===== ====== ===== =====#    1 0x85fff5aa78e5ff6a 0.0034 100.0% - 0.0002  0.00begin# Query1:115.61QPS,0.02x concurrency, ID0x85fff5aa78e5ff6aAtbyte 135761# This item is includedinchThe report because it matches--limit.# scores:v/M =0.00# time range: the- .- - Geneva: -:11.127883ToGeneva: -:11.300885# Attribute pct Total min max avg the%StdDev median#============ === ======= ======= ======= ======= ======= ======= =======# Count -       -# Exec Time     -3ms 51us 862us 171us 568us 191us 84us# Rows affecte0       0       0       0       0       0       0       0# Query Size -      -       5       5       5       5       0       5# Warning Coun0       0       0       0       0       0       0       0# string:# Hosts192.168.1.20# query_time distribution# 1us# 10us ################################################################# 100us # # # # # # ################################################# 1ms# 10ms# 100ms# 1s# 10s+Begin\g[[email protected]-mysql-server ~]#

Resources:

http://www.megalinux.net/using-tcpdump-for-mysql-query-logging/

Http://www.xfocus.net/articles/200105/172.html

http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/

Http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

Tcpdump Learning (3): MySQL Query