How to Use MySQL Sniffer for Real-Time MySQL Traffic Capture

Introduction

MySQL Sniffer is a MySQL‑protocol‑based packet capture tool that captures requests from a MySQL server in real time and formats the output. The output includes timestamp, accessing user, source IP, accessed database, command latency, returned row count, and the executed SQL statement. It supports batch capture of multiple ports, daemon mode, log rotation and other convenient usage modes.

It can also capture requests from Atlas, an open‑source MySQL‑protocol data‑middleware project by Qihoo 360 ( https://github.com/Qihoo360/Atlas ).

Similar tools include vc‑mysql‑sniffer and the -e mysql.query option of tshark .

Usage

Download and install from GitHub: https://github.com/Qihoo360/mysql-sniffer . It is recommended to compile and install on CentOS 6.2 or later and run as root.

Tool Parameters

<ol><li><p><code>./mysql-sniffer -h</code></p></li><li><p><code>Usage: mysql-sniffer [-d] -i eth0 -p 3306,3307,3308 -l /var/log/mysql-sniffer/ -e stderr</code></p></li><li><p><code>-r 3000-4000</code></p></li><li><p><code>-d daemon mode</code></p></li><li><p><code>-s <em>split interval in minutes</em> (e.g., 1440). If less than 0, split log daily.</code></p></li><li><p><code>-i <em>interface</em> (default eth0)</code></p></li><li><p><code>-p <em>port list</em> (default 3306). Multiple ports are separated by commas, e.g., 3306,3307.</code></p></li><li><p><code>this option has no effect when -f is set</code></p></li><li><p><code>-r port range (do not use -r and -p together)</code></p></li><li><p><code>-l query log DIRECTORY (default to stdout)</code></p></li><li><p><code>-e error log FILENAME or 'stderr' (use /dev/null to discard)</code></p></li><li><p><code>-f filename (use pcap file instead of live capture)</code></p></li><li><p><code>-w white list (comma‑separated ports to exclude from capture)</code></p></li><li><p><code>-t truncation length (truncate long queries; 0 means no truncation)</code></p></li><li><p><code>-n keeping tcp stream count (default 65536; older streams are dropped when limit is exceeded)</code></p></li></ol>

Examples

1. Capture a single port and print to screen

Output format: time, user, source IP, database, latency, row count, SQL.

<ol><li><p><code>mysql-sniffer -i eth0 -p 3306</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    1    select @version_comment limit 1</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    1    select USER()</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    13    show databases</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    1    SELECT DATABASE()</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    0    use mysql</code></p></li><li><p><code>2017-02-23 14:47:45    testuser    10.xx.xx.xx    NULL    0ms    0    set autocommit=1</code></p></li></ol>

2. Capture a single port and write to file

Use -l to specify the log directory; the log file will be named port.log .

<code>mysql-sniffer -i eth0 -p 3306 -l /tmp</code>

3. Capture multiple ports and write to files

Specify a comma‑separated port list with -p ; each port gets its own port.log .

<code>mysql-sniffer -i eth0 -p 3306,3307,3310 -l /tmp</code>

4. Capture a range of consecutive ports and write to files

Use -r to define the port range.

<code>// Capture ports 3306‑3310
mysql-sniffer -i eth0 -r 3306-3310 -l /tmp</code>

5. Capture a range of ports while filtering out specific ports

Use -w to whitelist ports to exclude.

<code>// Capture ports 3306‑3310 except 3308 and 3309
mysql-sniffer -i eth0 -r 3306-3310 -w 3308,3309 -l /tmp</code>

6. Run a single port in daemon mode and write to file

<code>mysql-sniffer -i eth0 -p 3306 -l /tmp -d</code>

7. Capture a port and truncate SQL statements

Use -t to limit the length of captured SQL.

<code>mysql-sniffer -i eth0 -p 3306 -t 100</code>

More

For additional usage patterns of MySQL Sniffer and related technologies such as Atlas, stay tuned for future posts.