bitsCN.com
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