共计 4407 个字符，预计需要花费 12 分钟才能阅读完成。
这篇文章给大家介绍 mysql 中怎么利用 sniffer 捕获 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:
INTERFACE 是指网卡号，如 eth0,eth2,lo 等。
当然也有人直接 tcpdump 来捕捉的，方法如下：
tcpdump -i eth2 -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.
1、软件包
2、编译安装 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:
示例
./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-tone.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
关于 mysql 中怎么利用 sniffer 捕获 SQL 语句就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
