Track MySQL Queries with mysqlsniffer on Ubuntu
Monitoring MySQL Queries Without Enabling Query Logging
When direct access to enable query logging on a production database server is unavailable, alternative methods are required to observe the queries being executed. A viable solution involves utilizing a network sniffer, modified to parse and decode MySQL packets. While this approach necessitates some compilation, the insights gained can prove invaluable.
It's important to note that capturing data from local connections may not consistently succeed with this technique, though attempts are permissible.
Prerequisites: Installing libpcap-dev
The initial step involves installing the libpcap-dev library, a development resource that empowers applications to intercept and analyze network packets. This can be achieved through the following command:
sudo apt-get install libpcap-dev
Downloading, Compiling, and Running mysqlsniffer
Next, a directory is created, the source code is downloaded, and compilation is performed. The following sequence of commands facilitates this process:
mkdir mysqlsniffer
cd mysqlsniffer
wget hackmysql.com/code/mysqlsniffer.tgz
tar xvfz mysqlsniffer.tgz
gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c
Upon completion, an executable file named mysqlsniffer will be present within the source directory. This file can be relocated to a suitable location, ideally within a directory included in the system's PATH environment variable.
To initiate mysqlsniffer, the network interface through which MySQL is accessible must be specified. For instance, if MySQL is listening on the eth0 interface, the command would be:
sudo /path/to/mysqlsniffer eth0
Filtering and Analyzing Output
The initial output stream will contain a substantial amount of data. To refine the output and focus specifically on queries, filtering can be applied. The following command demonstrates this:
$ sudo /path/to/mysqlsniffer --no-mysql-hdrs eth0 | grep COM_QUERY 192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=''
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`
This filtered output presents detailed query information without requiring a MySQL restart.
Command-Line Options
mysqlsniffer offers a range of command-line options to customize its behavior. These include:
--port N: Specifies the port number MySQL is listening on (default is 3306).--verbose: Displays additional packet information.--tcp-ctrl: Shows TCP control packets (SYN, FIN, RST, ACK).--net-hdrs: Displays major IP and TCP header values.--no-mysql-hdrs: Suppresses the display of MySQL header information (packet ID and length).--state: Shows packet state.--v40: Indicates that the MySQL server is version 4.0.--dump: Dumps all packets in hexadecimal format.--help: Displays help information.
Further details and the original source code can be found at: http://hackmysql.com/mysqlsniffer
For development environments, enabling standard query logging often presents a simpler and more straightforward solution.