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

This article mainly introduces the MySQL sniffer packet analysis of port communication, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Background

There are always developers and tests complaining that MySQL does not have the high-frequency statement analysis capabilities of SQL Server and

Trace function. In fact, MySQL is a lightweight database software, compared to the bloated SQL Server, in

It is even better in terms of building efficiency and hardware and software performance, not to mention the various pitfalls in the Windows domain environment. Light and high

At the same time, it is bound to give up some functions that have nothing to do with high-speed OLTP, such as data warehouse direction optimization, statements.

Tracking and other functions.

However, after the addition of the 5.7version of the sys library, you can also cooperate with the official workbench software to carry out high-frequency sentences.

The analysis of index usage, Istroke O performance and so on, although not completely comparable to the omnipotence of SSMS, is better than human flesh.

Reading sys library and iSlibrary is the difference between cloud and mud in terms of packs library.

2.MySQL sniffer

In the absence of a sys library, do not even know the password, worry about the impact of general log on performance, or on the connection situation

If the line is analyzed, the port communication can be grabbed and analyzed through MySQL sniffer.

MySQL sniffer (https://github.com/Qihoo360/mysql-sniffer)

Is a 360 open source connection layer grab packet analysis tool. 1. Installation:

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

Root# cd mysql-sniffer

Root# yum-y install glib2-devel libpcap-devel libnet-devel cmake

Root# yum-y install gcc gcc-c++

Root# mkdir sniffer

Root# make&&cmake

two。 Use

Root# cd sniffer

Root#. / mysql-sniffer-hUsage. / mysql-sniffer [- d]-I eth0-p 3306, 3307, 3307, var/log/mysql-sniffer/-e stderr

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

-d daemon mode. # backend mode

-s how often to split the log file (minute, eg. 1440). If less than 0, split log everyday # Log cutting Frequency

-I interface. Default to eth0 # Network Card Interface

-p port, default to 3306. Multiple ports should be splited by,'. Eg. 3306 this option has no effect when-f is set. # listening port

-r port range, Dont use-r and-p at the same time # port range

-l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout. # output to the port .log file under the specified directory, not to the screen

-e error log FILENAME or 'stderr'. If set to / dev/null, runtime error will not be recorded # outputs error information to the specified error file

-f filename. Use pcap file instead capturing the network interface # uses a given Pcap file for analysis instead of analyzing real-time Nic information

-w white list. Dont capture the port. Multiple ports should be splited by,'. # whitelist, do not capture these ports

-t truncation length. Truncate long query if it is longer than specified length. Less than 0 means no truncation # controls the length of SQL captured by the record

-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`

3. How to use it:

First use the ip addr command to observe your network card, then specify the port and specify the log file

For example:. / mysql-sniffer-d-I ens192-p 3306 3307-l / data/log/3306gen.log

. / mysql-sniffer-I lo-r 3306-3310

Note: the local traffic is monitored through the lo, that is, the loop port. Postscript: there is a situation that the packet cannot be grabbed on Centos7, or the packet is empty. After repeated troubleshooting, it is found that it is the libpcap version problem. Upgrading to the latest version 1.8 can capture the MySQL communication packet normally. Http://rpmfind.net/linux/fedora/linux/releases/27/Everything/x86_64/os/Packages/l/libpcap-1.8.1-6.fc27.x86_64.rpm if you can't grab any information, you might as well use tcpdump to grab the package locally, and then give it to sniffer for analysis. Check whether there is a problem with crawling or analyzing MySQL communication`yum-y install tcpdump`` tcpdump-I eth0-w tcpdump4sniffer.pcap`

`mysql-sniffer-f tcpdump4sniffer.pcap`

Read the above about the MySQL sniffer port communication packet analysis, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.