tcpdump 学习（3）：MySQL Query

在MySQL线上环境我们一般只打开了binary log，slow log，有时我们需要查看general log呢？因为该log记录所有的请求，打开该日志肯定给磁盘造成很大压力，IO能力有所下降，所以该日志线上一般不打开的，这就到tcpdump闪亮登场了。

tcpdump用法也不算复杂，输出就比较复杂了，如果非常熟悉TCP/IP协议，那么输出对于你来说就是小kiss啦。我们这里只关心MySQL的Query，所以输出还是非常简单，就是日常的查询语句。

1.简单使用（shell结合perl过滤查询）

[root@yayun-mysql-server ~]# cat query.sh

#!/bin/bash

tcpdump -i any -s  -l -w - dst port  | strings | perl -e '

while(<>) { chomp; next if /^[^ ]+[ ]*$/;

    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)

    {

        if (defined $q) { print "$q\n"; }

        $q=$_;

    } else {

        $_ =~ s/^[ \t]+//; $q.=" $_";

    }

}'

[root@yayun-mysql-server ~]#

关于各个参数说明请阅读文章最后给的链接。
执行上面脚本，在另外一个窗口执行查询，我使用了sysbench进行压力测试，最后抓取到的结果如下：

tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size  bytes

 { @ 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      @ Hi @ <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 where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

INSERT INTO sbtest values(?,,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

INSERT INTO sbtest values(?,,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

INSERT INTO sbtest values(?,,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

其实还有更简单的方法，那就是使用tcpflow

抓取数据的命令如下：

[root@yayun-mysql-server ~]# tcpflow -c -p -i any dst port  | grep -i -E "select|insert|update|delete|replace" | sed 's%\(.*\)\([.]\{4\}\)\(.*\)%\3%'

输出结果如下：

tcpflow[]: listening on any

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

INSERT INTO sbtest values(?,,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

UPDATE sbtest set k=k+ where id=?

UPDATE sbtest set c=? where id=?

DELETE from sbtest where id=?

INSERT INTO sbtest values(?,,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')

SELECT c from sbtest where id=?

SELECT c from sbtest where id between ? and ?

SELECT SUM(K) from sbtest where id between ? and ?

SELECT c from sbtest where id between ? and ? order by c

SELECT DISTINCT c from sbtest where id between ? and ? order by c

最后说说pt-query-digest，这工具包含在percona-toolkit，在分析慢查询方面是非常的好使，具体的用法大家自己前往官网查阅。

我们通过tcpdump抓包以后，通过--type tcpdump选项来分析一下，简单的用法如下：

[root@yayun-mysql-server ~]# tcpdump -s  -x -nn -q -tttt -i any -c  port  > mysql.tcp.txt

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on any, link-type LINUX_SLL (Linux cooked), capture size  bytes

packets captured

packets received by filter

packets dropped by kernel

[root@yayun-mysql-server ~]#

然后使用pt-query-digest工具来进行分析一下，用法也比较简单

[root@yayun-mysql-server ~]# pt-query-digest --type tcpdump mysql.tcp.txt                             

# 340ms user time, 50ms system time, 24.38M rss, 205.10M vsz

# Current date: Thu Jun  ::

# Hostname: yayun-mysql-server

# Files: mysql.tcp.txt

# Overall:  total,  unique, 115.61 QPS, .02x concurrency _____________

# Time range: -- ::11.127883 to ::11.300885

# Attribute          total     min     max     avg     %  stddev  median

# ============     ======= ======= ======= ======= ======= ======= =======

# Exec time            3ms    51us   862us   171us   568us   191us    84us

# Rows affecte

# Query size

# Warning coun                                                     

# Profile

# Rank Query ID           Response time Calls R/Call V/M   Item

# ==== ================== ============= ===== ====== ===== =====

#     0x85FFF5AA78E5FF6A 0.0034 100.0%     0.0002  0.00 BEGIN

# Query : 115.61 QPS, .02x concurrency, ID 0x85FFF5AA78E5FF6A at byte

# This item is included in the report because it matches --limit.

# Scores: V/M = 0.00

# Time range: -- ::11.127883 to ::11.300885

# Attribute    pct   total     min     max     avg     %  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count

# Exec time         3ms    51us   862us   171us   568us   191us    84us

# Rows affecte

# Query size

# Warning coun

# String:

# Hosts        192.168.1.20

# Query_time distribution

#   1us

#  10us  ################################################################

# 100us  ####################################################

#   1ms

#  10ms

# 100ms

#    1s

#  10s+

BEGIN\G

[root@yayun-mysql-server ~]#

参考资料：

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 学习（3）：MySQL Query

最新文章

热门文章