Shulou(Shulou.com)05/31 Report--

This article introduces how to use sniffer to capture SQL statements in mysql, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Before MySQL5.1, general log cannot be enabled or disabled at runtime. Sometimes it is troublesome to capture SQL to find problems. Accidentally, I found a very good gadget: mysqlsniffer, which can be used to capture SQL statements. The help is as follows:

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 refers to the network card number, such as eth0,eth2,lo, etc.

Of course, there are also people who directly tcpdump to capture, the method is as follows:

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";}

$qcommodity _

} 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. Software package

2. Compile and install mysqlsniffer] # gcc-O2-lpcap-o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

If the following prompt appears

Please install the libpcap-devel package and compile it again with gcc

Mysqlsniffer.c:26:18: error: pcap.h: there is no such file or directory

[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) # # designated port

-verbose Show extra packet information # # displays the extended information of the package

-tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK) # # displays the status of the tcp package

-net-hdrs Show major IP and TCP header values # # displays header information for ip and TCP

-no-mysql-hdrs Do not show MySQL header (packet ID and length) # # does not display header information for ip and TCP

-state Show state # # display status

-v40 MySQL server is version 4.0 # # add this parameter if the MySQL server version is 4.0

-dump Dump all packets in hex # # dump input into hex file format

-help Print this

Original source code and more information at:

Example

. / 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 f8 7'14 10 00 1D b6 c0 00 00 00 05 00 00 00 1a 00 00 00 06 03 s t d 04 1c 00 1c 00 1c 00 w e b d b _ 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 00 9-1 1-0 8'A N D z o n e i d ='3 25'A N D a d s i d ='18 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 | 0 | 1 | 1 | 1 | |

End

127.0.0.1.37968 > server: COM_QUERY: SELECT a.adsidrea.url As advmoney,u.uid AS advuid FROM zyads_ads AS a.status zyads_users As zyads_users As p.planidrepartion P.plantype p. PriceAdstype P.priceAdstyle p.

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 0pm 1

Server > 127.0.0.1.37968: 15 Fields

Field: webdb.a.adsid

Field: webdb.a.url

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.