在MySQL线上环境我们一般只打开了binary log，slow log，有时我们需要查看general log呢？因为该log记录所有的请求，打开该日志肯定给磁盘造成很大压力，IO能力有所下降，所以该日志线上一般不打开的，这就到tcpdump闪亮登场了。
tcpdump用法也不算复杂，输出就比较复杂了，如果非常熟悉TCP/IP协议，那么输出对于你来说就是小kiss啦。我们这里只关心MySQL的Query，所以输出还是非常简单，就是日常的查询语句。
1.安装
一般系统都自带了tcpdump命令，没有该命令的采用如下方式安装：
[root@yayun-mysql-server ~]# yum install tcpdump -y
2.简单使用（shell结合perl过滤查询）
[root@yayun-mysql-server ~]# cat query.sh #!/bin/bashtcpdump -i any -s 0 -l -w - dst port 3306 | 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 65535 bytes 4{ @ H{ @ H` @ ?h 'f$ ?h ;f$ ?h Of$ ?h cf$ ?h wf$ ?h" f$ ?h# f% ?h# f% H| @ Lg @ ?h+ f% 其实还有更简单的方法，那就是使用tcpflow
安装tcpflow
[root@yayun-mysql-server ~]# yum install tcpflow -y
抓取数据的命令如下：
[root@yayun-mysql-server ~]# tcpflow -c -p -i any dst port 3306 | grep -i -E "select|insert|update|delete|replace" | sed 's%\(.*\)\([.]\{4\}\)\(.*\)%\3%' 输出结果和tcpdump结果是一样的。
tcpflow[9461]: listening on anySELECT 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 cSELECT DISTINCT c from sbtest where id between ? and ? order by cUPDATE sbtest set k=k+1 where id=?UPDATE sbtest set c=? where id=?DELETE from sbtest where id=?INSERT INTO sbtest values(?,0,' ','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 cSELECT DISTINCT c from sbtest where id between ? and ? order by cUPDATE sbtest set k=k+1 where id=?UPDATE sbtest set c=? where id=?DELETE from sbtest where id=?INSERT INTO sbtest values(?,0,' ','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 cSELECT DISTINCT c from sbtest where id between ? and ? order by c
最后说说pt-query-digest，这工具包含在，在分析慢查询方面是非常的好使，具体的用法大家自己前往官网查阅。
我们通过tcpdump抓包以后，通过--type tcpdump选项来分析一下，简单的用法如下：
[root@yayun-mysql-server ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txttcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes1000 packets captured3235 packets received by filter2023 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 26 03:44:15 2014# Hostname: yayun-mysql-server# Files: mysql.tcp.txt# Overall: 20 total, 1 unique, 115.61 QPS, 0.02x concurrency _____________# Time range: 2014-06-26 03:44:11.127883 to 03:44:11.300885# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 3ms 51us 862us 171us 568us 191us 84us# Rows affecte 0 0 0 0 0 0 0# Query size 100 5 5 5 5 0 5# Warning coun 0 0 0 0 0 0 0# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== ============= ===== ====== ===== =====# 1 0x85FFF5AA78E5FF6A 0.0034 100.0% 20 0.0002 0.00 BEGIN# Query 1: 115.61 QPS, 0.02x concurrency, ID 0x85FFF5AA78E5FF6A at byte 135761# This item is included in the report because it matches --limit.# Scores: V/M = 0.00# Time range: 2014-06-26 03:44:11.127883 to 03:44:11.300885# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 100 20# Exec time 100 3ms 51us 862us 171us 568us 191us 84us# Rows affecte 0 0 0 0 0 0 0 0# Query size 100 100 5 5 5 5 0 5# Warning coun 0 0 0 0 0 0 0 0# String:# Hosts 192.168.1.20# Query_time distribution# 1us# 10us ################################################################# 100us ##################################################### 1ms# 10ms# 100ms# 1s# 10s+BEGIN\G[root@yayun-mysql-server ~]#
如果系统历史比较久，我们想知道哪些账号在连接数据库的话，可以使用下面的命令，非常简单方便哦。
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 | pt-query-digest --limit=100% --type tcpdump | perl -lne 'BEGIN{$/=""};1 while(/Hosts\s+((?:\d{1,3}\.){3}\d{1,3})[\s\S]+?Users\s+(\w+)(?{$h->{"$2\@$1"}++})/mg);END{print $_ for keys %$h}'
参考资料：