Shulou(Shulou.com)06/01 Report--

In the past two days, I have seen an open source software in the group, which is a tool for MYSQL to capture packages in real time. Immediately let me think of some requirements for fault location before, for connection to the database connection information for audit positioning, done MYSQL's own audit function on (there are many holes, careful use), but also through tcpdump to do some analysis (directly read the analysis log is more difficult to understand, use when the database connection abort value is high). For real-time connection monitoring and analysis, we generally use the mysqladmin/show full processlist command to view it, and we can also query the processlist table directly. But for a long time to monitor the connection to the database application IP, operation records, there is no particularly good tool, this tool lit up in front of my eyes, I immediately had the idea to try it, its name is: MySQL Sniffer.

Introduction to MySQL Sniffer: (readme from open source tools)

MySQL Sniffer is a packet grabbing tool based on MySQL protocol, which grabs MySQLServer or Client requests in real time and formats the output. The output includes access time, access user, source IP, access Database, command time, number of data rows returned, execution statements, and so on. There are batch crawling multiple ports, background operation, log segmentation and other ways of use, easy to operate, friendly output. At the same time, it is also applicable to the request to grab the Atlas end. Atlas is an open source data middle-tier project based on MySQL protocol by Qihu. The project address: [https://github.com/Qihoo360/Atlas](https://github.com/Qihoo360/Atlas). Similar tools also include vc-mysql-sniffer, and the-e mysql.query parameter of tshark to parse the MySQL protocol.

MySQL Sniffer installation uses:

(1) installation environment

It is recommended to compile and install on centos6.2 and above, and run it with root

(2) dependency package

Glib2.0 、 libpcap 、 libnet

(3) download address

Https://github.com/Qihoo360/mysql-sniffer

Git clone git@github.com:Qihoo360/mysql-sniffer.git

(4) installation process

[root@virtual01 mysql_sniff] # unzip mysql-sniffer-master.zip

[root@virtual01 mysql_sniff] # cd mysql-sniffer-master

[root@virtual01 mysql-sniffer-master] # cmake. /

[root@virtual01 mysql-sniffer-master] # make-j 8

[root@virtual01 mysql-sniffer-master] # cd.. /

[root@virtual01 mysql_sniff] # mv mysql-sniffer-master / usr/local/mysql_sniffer

[root@virtual01 mysql_sniff] # ln-s / usr/local/mysql_sniffer/bin/mysql-sniffer / usr/bin/

(5) Command parameters

[root@cyou_virtual01] # mysql-sniffer-h

Usage mysql-sniffer [- d]-I eth0-p 3306 var/log/mysql-sniffer/ 3307 stderr 3308-l / var/log/mysql-sniffer/-e

[- d]-I eth0-r 3000-4000

-d daemon mode. (running in background)

-s how often to split the log file (minute, eg. 1440). If less than 0, split log everyday (how often logs are separated in minutes, if less than 0, by day)

-I interface. Default to eth0 (specify the interface of the monitoring network card)

-p port, default to 3306. Multiple ports should be splited by,'. Eg. 3306 and 3307 (specify the port to listen on, with multiple ports separated by commas)

This option has no effect when-f is set.

-r port range, Don't use-r and-p at the same time (specified port range, for example-r 2000-5000)

-l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout. (specify the directory of the output log, file nominal port name, such as 3306.log, and output to the log when the listening process terminates)

-e error log FILENAME or 'stderr'. If set to / dev/null, runtime error will not be recorded (specify error log output path)

-f filename. Use pcap file instead capturing the network interface (use pcap instead of listening)

-w white list. Dont capture the port. Multiple ports should be splited by','. (whitelist, ports not monitored)

-t truncation length. Truncate long query if it's longer than specified length. Less than 0 means no truncation (exceeds query length, truncation)

-n keeping tcp stream count, if not set, default is 65536. If active tcp count is larger than the specified count, mysql-sniffer will remove the oldest one (number of tcp streams, default is 65536)

(6) use cases

1) grab a port information in real time and print it to the screen

The output format is: time, access user, source IP, access Database, command time-consuming, return data rows, execute statements.

[root@virtual01] # mysql-sniffer-I eth0-p 3306

2017-03-02 10:25:28 root 192.168.110.29 NULL 0ms 1 select @ @ version_comment limit 1

2017-03-02 10:25:45 root 192.168.110.29 NULL 2ms 1 select sysdate ()

2017-03-02 10:26:05 root 192.168.110.29 NULL 0ms 0 commit

2017-03-02 10:26:21 root 192.168.110.29 NULL 0ms 8 show variables like'% char%'

2) grab a port information in real time and print it to a file

-l specifies the log output path, and the log file will be named port.log. (log contents will not be displayed until the monitoring process is stopped)

[root@virtual01] # mysql-sniffer-I eth0-p 3306-l / tmp

[root@virtual01 ~] # ls-l / tmp/3306.log

-rw-r--r-- 1 root root 304 Mar 2 10:32 / tmp/3306.log

3) capture multiple port information in real time and print it to a file

-l specifies the log output path, and-p specifies the comma division of the list of ports to be crawled. The log files will be named after their respective port.log.

Mysql-sniffer-I eth0-p 3306 tmp 3307

4) grab multiple continuously increasing ports in real time and print them to a file

-l specifies the log output path,-r specifies the port range, and the log files will be named after their respective port.log

Mysql-sniffer-I eth0-r 3306-3310-l / tmp

5) grab multiple continuously increasing ports in real time, filter several ports at the same time, and print to a file.

-l specifies the log output path,-r specifies the port range,-w specifies the comma division of the filter port list, and the log files will be named after their respective port.log.

Mysql-sniffer-I eth0-r 3306-3310-w 3308 eth0 3309-l / tmp

6) grab a port to run in daemon mode and print to a file

-l specifies the log output path,-p specifies the port,-n specifies the number of packets, and the log files will be named after their respective port.log.

Mysql-sniffer-I eth0-p 3306-l / tmp-d

7) grab a port and intercept the SQL of a specified length

-p specifies the port, and-t specifies the SQL length, which outputs the first n characters of the SQL (for scenarios where the SQL is too long).

Mysql-sniffer-I eth0-p 3306-t 100

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.