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>
Field: webdb.a.status <type tiny int (2305) size 1>
Field: webdb.a.adstype <type var string (2557) size 20>
Field: webdb.p.planid <type int24 (777) size 8>
Field: webdb.p.deduction <type tiny int (1) size 3>
Field: webdb.p.plantype <type string (2558) size 6>
Field: webdb.p.price <type double (261) size 10>
Field: webdb.p.priceadv <type double (5) size 10>
Field: webdb.p.uid <type int24 (10505) size 8>
Field: webdb.p.expire <type date (33034) size 10>
Field: webdb.p.clearing <type var string (509) size 20>
Field: webdb.p.budget <type long int (259) size 11>
Field: webdb.u.advmoney <type double (5) size 12>
Field: webdb.u.advuid <type int24 (8969) size 8>
End <warnings 0>
|| 26 | http:// | 3 | tw | 3 | 0 | cpc | 0.0000 | 0.1000 | 15 | 0000-00-00 | month | 100000 | 99563626.7179 | 15 ||
End <warnings 0>
127.0.0.1.37968 > server: COM_QUERY: SELECT ip FROM zyads_tempip WHERE ip=’59.60.172.88′ AND planid=3 limit 0,1
server > 127.0.0.1.37968: 1 Fields
Field: webdb.zyads_tempip.ip <type string (2558) size 30>
End <warnings 0>
|| 59.60.172.88 ||
End <warnings 0>
127.0.0.1.37968 > server: COM_QUIT
127.0.0.1.37968 > server: FIN ACK
转自：网管小王的博客：http://www.cnking.org
