使用mysqlsniffer捕獲SQL語句

MySQL5.1之前general log不能在運行時啟用或禁用,有時想捕捉SQL來查找問題就很麻煩,偶然間發現一個很不錯的小工具:mysqlsniffer,可以用來捕捉SQL語句,使用幫助如下:


mysqlsniffer –help
mysqlsniffer v1.2 – Watch MySQL traffic on a TCP/IP network


Usage: mysqlsniffer [OPTIONS] INTERFACE


OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this


Original source code and more information at:
http://hackmysql.com/mysqlsniffer


INTERFACE是指網卡號,如eth0,eth1,lo等。


當然也有人直接tcpdump來捕捉的,方法如下:


tcpdump -i eth1 -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)/i) {
if (defined $q) { print “$qn”; }
$q=$_;
} else {
$_ =~ s/^[ t]+//; $q.=” $_”;
}
}
mysqlsniffer is a tcpdump clone specifically for dumping/sniffing/watching MySQL network protocol traffic over TCP/IP networks. mysqlsniffer is coded in C using the pcap library and works with MySQL version 4.0 and newer. mysqlsniffer is the only MySQL-specific network sniffer.


mk-query-digest also understands the MySQL protocol. It’s not a sniffer, though. It reads packet dumps from tcpdump like a slowlog. If you want to analyze queries from the wire (i.e. from network traffic), mk-query-digest is what you want.


For more information about the MySQL protocol read MySQL Internals ClientServer Protocol.


 


 


 


http://hackmysql.com/mysqlsniffer


 



1、下載軟件包 http://hackmysql.com/code/mysqlsniffer.tgz


 



2、編譯安裝 root@real1 mysqlsniffer]# gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c


 



如果出現如下提示


請安裝libpcap-devel 包,再重新用gcc來編譯


mysqlsniffer.c:26:18: 錯誤:pcap.h:沒有那個文件或目錄


 



[root@real1 mysqlsniffer]# ./mysqlsniffer –help


mysqlsniffer v1.2 – Watch MySQL traffic on a TCP/IP network


 



Usage: mysqlsniffer [OPTIONS] INTERFACE


 



OPTIONS:


–port N        Listen for MySQL on port number N (default 3306)  ##指定端口


–verbose       Show extra packet information                                       ## 顯示包的擴展信息


–tcp-ctrl      Show TCP control packets (SYN, FIN, RST, ACK)     ## 顯示tcp包的狀態


–net-hdrs      Show major IP and TCP header values                       ##顯示ip和TCP的頭信息


–no-mysql-hdrs Do not show MySQL header (packet ID and length)   ##不顯示ip和TCP的頭信息


–state         Show state                                        ## 顯示狀態


–v40           MySQL server is version 4.0   ##如果MySQL服務器版本是4.0 加上此參數


–dump          Dump all packets in hex   ##把輸入dump成hex文件格式


–help          Print this


 



Original source code and more information at:


http://hackmysql.com/mysqlsniffer


 


 


 


示例


./mysqlsniffer eth0 –port 3306 –tcp-ctrl –no-mysql-hdrs


 



server > 127.0.0.1.24266: Waiting for server to finish response… ::DUMP:: 00 89 b0 f6 J 02 01 00 00 00 aa 00 00 00 f8 7 ‘ 14 10 00 1d b6 c0 00 00 00 00 00 05 00 00 1a 00 00 00 @ 00 00 01 00 00 00 00 00 00 00 00 06 03 s t d 04 1c 00 1c 00 1c 00 w e b d b 00 U P D A T E   z y a d s _ s t a t s     S E T   v i e w s   =   v i e w s   +   6 0   W H E R E   d a y   =   ‘ 2 0 0 9 – 1 1 – 0 8 ‘     A N D   z o n e i d   =   ‘ 3 2 5 ‘     A N D   a d s i d   =   ‘ 1 8 2 ‘ ::DUMP::


127.0.0.1.24266 > server: ACK


127.0.0.1.37968 > server: SYN


server > 127.0.0.1.37968: SYN ACK


127.0.0.1.37968 > server: ACK


server > 127.0.0.1.37968: Handshake <proto 10 ver 5.1.38-Linuxtone.Org-log thd 12629534>


127.0.0.1.37968 > server: ACK


127.0.0.1.37968 > server: Handshake (new auth) <user 0252_liufei db (null) max pkt 1073741824>


server > 127.0.0.1.37968: ACK


server > 127.0.0.1.37968: OK <fields 0 affected rows 0 insert id 0 warnings 0>


127.0.0.1.37968 > server: COM_INIT_DB: webdb


server > 127.0.0.1.37968: OK <fields 0 affected rows 0 insert id 0 warnings 0>


127.0.0.1.37968 > server: COM_QUERY: SET NAMES ‘gbk’


server > 127.0.0.1.37968: OK <fields 0 affected rows 0 insert id 0 warnings 0>


127.0.0.1.37968 > server: COM_QUERY: SET sql_mode=”


server > 127.0.0.1.37968: OK <fields 0 affected rows 0 insert id 0 warnings 0>


127.0.0.1.37968 > server: COM_QUERY: SELECT cpmdeduction,cpcdeduction,cpadeduction,cpsdeduction,cpvdeduction,cpczlink,cpazlink,cpszlink FROM zyads_users


WHERE uid=770 AND status=2 limit 0,1


server > 127.0.0.1.37968: 8 Fields


Field: webdb.zyads_users.cpmdeduction <type tiny int (257) size 3>


Field: webdb.zyads_users.cpcdeduction <type tiny int (257) size 3>


Field: webdb.zyads_users.cpadeduction <type tiny int (257) size 3>


Field: webdb.zyads_users.cpsdeduction <type tiny int (257) size 3>


Field: webdb.zyads_users.cpvdeduction <type tiny int (257) size 3>


Field: webdb.zyads_users.cpczlink <type tiny int (257) size 1>


Field: webdb.zyads_users.cpazlink <type tiny int (257) size 1>


Field: webdb.zyads_users.cpszlink <type tiny int (257) size 1>


End <warnings 0>


|| 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 ||


End <warnings 0>


127.0.0.1.37968 > server: COM_QUERY: SELECT a.adsid,a.url,a.status,adstype,p.planid,p.deduction,p.plantype,p.price,p.priceadv,p.uid,p.expire,p.clearing,p.budget,u.money As advmoney,u.uid AS advuid FROM zyads_ads AS a ,zyads_plan As p ,zyads_users As u


WHERE a.adsid=26 AND a.planid=p.planid AND p.uid=u.uid AND  p.status = 1 AND  a.status = 3 AND u.status=2 limit 0,1


server > 127.0.0.1.37968: 15 Fields


Field: webdb.a.adsid <type int24 (777) size 9>


Field: webdb.a.url <type var string (509) size 510>


<P

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *