分类： Mysql/postgreSQL
2010-01-21 13:27:53
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、编译安装 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:
示例
./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
127.0.0.1.37968 > server: ACK
127.0.0.1.37968 > server: Handshake (new auth)
server > 127.0.0.1.37968: ACK
server > 127.0.0.1.37968: OK
127.0.0.1.37968 > server: COM_INIT_DB: webdb
server > 127.0.0.1.37968: OK
127.0.0.1.37968 > server: COM_QUERY: SET NAMES ‘gbk’
server > 127.0.0.1.37968: OK
127.0.0.1.37968 > server: COM_QUERY: SET sql_mode=”
server > 127.0.0.1.37968: OK
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
Field: webdb.zyads_users.cpcdeduction
Field: webdb.zyads_users.cpadeduction
Field: webdb.zyads_users.cpsdeduction
Field: webdb.zyads_users.cpvdeduction
Field: webdb.zyads_users.cpczlink
Field: webdb.zyads_users.cpazlink
Field: webdb.zyads_users.cpszlink
End
|| 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 ||
End
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
Field: webdb.a.url
Field: webdb.a.status
Field: webdb.a.adstype
Field: webdb.p.planid
Field: webdb.p.deduction
Field: webdb.p.plantype
Field: webdb.p.price
Field: webdb.p.priceadv
Field: webdb.p.uid
Field: webdb.p.expire
Field: webdb.p.clearing
Field: webdb.p.budget
Field: webdb.u.advmoney
Field: webdb.u.advuid
End
|| 26 | http:// | 3 | tw | 3 | 0 | cpc | 0.0000 | 0.1000 | 15 | 0000-00-00 | month | 100000 | 99563626.7179 | 15 ||
End
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
End
|| 59.60.172.88 ||
End
127.0.0.1.37968 > server: COM_QUIT
127.0.0.1.37968 > server: FIN ACK
