抓包分析mysql

原文链接，推荐关注作者

一、查看客户端sql语句相关信息

sudo tcpdump -i any port 3306 -w w.pacp

sudo tshark -i any -d tcp.port==3306,mysql -T fields -e mysql.query 'port 3306'

sudo tshark -i any -c 50 -d tcp.port==3306,mysql -Y " ((tcp.port eq 3306 )  )" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e mysql.query

二、查看响应时间

sudo tshark -i any -Y " ((tcp.port eq 3306 ) and tcp.len>0 )" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch -e frame.time_delta_displayed -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e mysql.query

三、每隔3秒钟生成一个新文件，总共生成5个文件后（15秒后）终止抓包，然后包名也按时间规范好了

sudo tcpdump -t -s 0 tcp port 3306 -w 'dump_%Y-%m-%d_%H:%M:%S.pcap' -G 3 -W 5 -Z root

四、每隔30分钟生成一个包并压缩

nohup sudo tcpdump -i any -t -s 0 tcp and port 3306 -w 'dump_%Y-%m-%d_%H:%M:%S.pcap' -G 1800 -W 48 -Z root -z gzip  &

五、设置文件大小 1000M

nohup sudo tcpdump -i eth0 -t -s 0 tcp and port 3306 -w 'dump_' -C 1000 -W 300 -Z root -z gzip  &

六、抓取详细SQL语句, 快速确认client发过来的具体SQL内容

sudo tshark -i any -f 'port 3306' -s 0 -l -w - |strings

sudo tshark -i any -d tcp.port==3306,mysql -T fields -e mysql.query 'port 3306'

sudo tshark -i any -Y "tcp.srcport==3306" -d tcp.port==3306,mysql -T fields -e tcp.srcport -e mysql.query 'port 3006'

七、分析mysql的每个SQL响应时间

tshark -r w.pacapng -Y "mysql.query or (  tcp.srcport==3306)" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e mysql.query |sort -nk9 -nk1

tshark -r w.pacapng -Y "((tcp.srcport eq 3306 ) and tcp.len>0 )" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch -e frame.time_delta_displayed -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e tcp.analysis.ack_rtt

八、MySQL响应时间直方图

tshark -r w.pacapng -Y "mysql.query or (tcp.srcport==3306  and tcp.len>60)" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len | awk 'BEGIN {sum0=0;sum3=0;sum10=0;sum30=0;sum50=0;sum100=0;sum300=0;sum500=0;sum1000=0;sumo=0;count=0;sum=0} {rt=$8; if(rt>=0.000) sum=sum+rt; count=count+1; if(rt<=0.000) sum0=sum0+1; else if(rt<0.003) sum3=sum3+1 ; else if(rt<0.01) sum10=sum10+1; else if(rt<0.03) sum30=sum30+1; else if(rt<0.05) sum50=sum50+1; else if(rt < 0.1) sum100=sum100+1; else if(rt < 0.3) sum300=sum300+1; else if(rt < 0.5) sum500=sum500+1; else if(rt < 1) sum1000=sum1000+1; else sum=sum+1 ;} END{printf "-------------\n3ms:\t%s \n10ms:\t%s \n30ms:\t%s \n50ms:\t%s \n100ms:\t%s \n300ms:\t%s \n500ms:\t%s \n1000ms:\t%s \n>1s:\t %s\n-------------\navg: %.6f \n" , sum3,sum10,sum30,sum50,sum100,sum300,sum500,sum1000,sumo,sum/count;}'

九、按秒汇总每个http response 耗时

tshark -r w.pacapng -Y 'http.time>0 ' -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e ip.dst -e tcp.stream  -e http.request.full_uri -e http.response.code -e http.time  | awk '{ print int($2), $8 }' | awk '{ sum[$1]+=$2; count[$1]+=1 ;} END { for (key in count) {  printf  "time= %s  \t count=%s   \t avg=%.6f \n", key,  count[key], sum[key]/count[key] } }' | sort -k2n | awk '{ print strftime("%c",$2), $0 }'

十、按http response分析响应时间

tshark -nr w.pacapng -o tcp.calculate_timestamps:true  -Y "http.request or http.response" -T fields -e frame.number -e frame.time_epoch  -e tcp.time_delta  -e ip.src -e ip.dst -e tcp.stream  -e http.request.full_uri -e http.response.code -e http.response.phrase | sort -nk6 -nk1

tshark -r 2.pcacpng -Y 'http.time>0 ' -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e ip.dst -e tcp.stream  -e http.request.full_uri -e http.response_for.uri  -e http.time  | awk '{ print int($2/10), $8 }' | awk '{ sum[$1]+=$2; count[$1]+=1 ;} END { for (key in count) {  printf  "time= %s  \t count=%s   \t avg=%.6f \n", key,  count[key], sum[key]/count[key] } }' | sort -k2n | gawk '{ print strftime("%c",$2*10), $0 }'

十一、有用的命令

capinfos w.pacp #分析包的总概览等
tshark -q -n -r w.pacp -z “conv,ip” #分析流量总况
tshark -q -n -r w.pacp -z “conv,tcp” #分析每一个连接的流量、rtt、响应时间、丢包率、重传率等等
editcap -c 100000 w.pacp rsb00.cap #把大文件rsb2.cap按每个文件100000个package切成小文件

十二、分析每两个IP之间的流量

tshark -r w.pacp -q -z 'conv,ip' 

十二、分析每个会话的流量

tshark -r w.pacp -q -z 'conv,tcp'

十三、分析每个包的response time

tshark -r w.pacp -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch -e ip.src -e ip.dst -e tcp.stream -e tcp.len -e tcp.analysis.initial_rtt -e tcp.time_delta

十四、分析有问题的包、概览

tshark -r w.pacp -q -z 'expert,note'

十五、分析rtt、丢包、deplicate等

tshark -r w.pacp -q -z io,stat,1,"AVG(tcp.analysis.ack_rtt)tcp.analysis.ack_rtt","COUNT(tcp.analysis.retransmission) tcp.analysis.retransmission","COUNT(tcp.analysis.fast_retransmission) tcp.analysis.fast_retransmission","COUNT(tcp.analysis.duplicate_ack) tcp.analysis.duplicate_ack","COUNT(tcp.analysis.lost_segment) tcp.analysis.lost_segment","MIN(tcp.window_size)tcp.window_size"

十六、分析丢包、duplicate ack

tshark -r w.pacp -q -z io,stat,5,"COUNT(tcp.analysis.retransmission) tcp.analysis.retransmission","COUNT(tcp.analysis.fast_retransmission) tcp.analysis.fast_retransmission","COUNT(tcp.analysis.duplicate_ack) tcp.analysis.duplicate_ack","COUNT(tcp.analysis.lost_segment) tcp.analysis.lost_segment"

十七、分析rtt 时间

tshark -r w.pacp -q -z io,stat,1,"MIN(tcp.analysis.ack_rtt)tcp.analysis.ack_rtt","MAX(tcp.analysis.ack_rtt)tcp.analysis.ack_rtt","AVG(tcp.analysis.ack_rtt)tcp.analysis.ack_rtt"

十八、计算window size

tshark -r w.pacp -q -z io,stat,5,"COUNT(tcp.analysis.retransmission) tcp.analysis.retransmission","AVG(tcp.window_size) tcp.window_size","MAX(tcp.window_size) tcp.window_size","MIN(tcp.window_size) tcp.window_size"

十九、分析rt

tshark -r w.pacp -Y " ((tcp.srcport eq 3306 ) and tcp.len>0 )" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e tcp.analysis.ack_rtt

tshark -r w.pacp -Y "mysql.query or (  tcp.srcport==3306)" -o tcp.calculate_timestamps:true -T fields -e frame.number -e frame.time_epoch  -e frame.time_delta_displayed  -e ip.src -e tcp.srcport -e tcp.dstport -e ip.dst -e tcp.time_delta -e tcp.stream -e tcp.len -e mysql.query |sort -nk9 -nk1