Capture SQL statements using mysqlsniffer

Before MySQL5.1, general log cannot be enabled or disabled at run time. Sometimes it is very troublesome to capture SQL to find the problem. By chance, I found a good tool: 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 mysqlon 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:
Http://hackmysql.com/mysqlsniffer

An INTERFACE is a network card number, such as eth0, eth1, and lo.

Of course, some people directly capture tcpdump, the method is as follows:

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. Download package mysql.com/code/mysqlsniffer.tgz "> http://hackmysql.com/code/mysqlsniffer.tgz

 

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

 

If the following prompt appears:

Install the libpcap-devel package and then use gcc to compile it again.

Mysqlsniffer. c: 26: 18: Error: pcap. h: No 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) # specify the port

-Verbose Show extra packet information # display package Extension information

-Tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK) # display the status of the tcp packet

-Net-hdrs Show major IP and TCP header values # display ip and TCP header Information

-No-mysql-hdrs Do not show MySQL header (packet ID and length) # Do not Display ip and TCP header Information

-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 # Dumping input into hex file format

-Help Print this

 

Original source code and more information:

Http://hackmysql.com/mysqlsniffer

 

 

 

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 00 f8 7 '14 10 00 1d b6 c0 00 00 00 00 00 00 00 1a 00 00 00 @ 00 01 00 00 00 00 00 00 00 06 03 s t 04 1c 00 1c 00 w e B d B 00 U P d A T E z y a D s _ s t 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 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.000068> server: SYN

Server> 127.0.0.1.000068: SYN ACK

127.0.0.1.000068> server: ACK

Server> 127.0.0.1.000068: Handshake <proto 10 ver 5.1.38-Linuxtone. Org-log thd 12629534>

127.0.0.1.000068> server: ACK

127.0.0.1.000068> server: Handshake (new auth) <user 0252_liufei db (null) max pkt 1073741824>

Server> 127.0.0.1.000068: ACK

Server> 127.0.0.1.000068: OK <fields 0 affected rows 0 insert id 0 warnings 0>

127.0.0.1.000068> server: COM_INIT_DB: webdb

Server> 127.0.0.1.000068: OK <fields 0 affected rows 0 insert id 0 warnings 0>

127.0.0.1.000068> server: COM_QUERY: set names 'gbk'

Server> 127.0.0.1.000068: OK <fields 0 affected rows 0 insert id 0 warnings 0>

127.0.0.1.000068> server: COM_QUERY: SET SQL _mode ="

Server> 127.0.0.1.000068: OK <fields 0 affected rows 0 insert id 0 warnings 0>

127.0.0.1.000068> server: COM_QUERY: SELECT cpmdeduction, cpcdeduction, cpadeduction, cpsdeduction, cpvdeduction, cpczlink, cpazlink, cpszlink FROM zyads_users

WHERE uid = 770 AND status = 2 limit

Server> 127.0.0.1.000068: 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 |

End <warnings 0>

127.0.0.1.000068> server: COM_QUERY: SELECT. adsid,. url,. 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. adsid = 26 AND. planid = p. planid AND p. uid = u. uid AND p. status = 1 AND. status = 3 AND u. status = 2 limit 0, 1

Server> 127.0.0.1.000068: 15 Fields

Field: webdb. a. adsid <type int24 (777) size 9>

Field: webdb. a. url <type var string (509) size 510>