How to Use mysql_sniffer for Real‑Time MySQL Query Capture and 8.0 Compatibility Checks

Tool Overview

mysql_sniffer is a MySQL‑protocol packet sniffer that captures server‑side requests in real time and writes each entry with timestamp, source IP and the executed SQL statement.

Purpose for MySQL 8.0 Upgrade

MySQL 8.0 introduces syntax changes and deprecates some functions. By running mysql_sniffer on the existing MySQL 5.7 or MariaDB server, DBAs can collect the actual workload, analyse the statements and verify compatibility before migration.

Typical Incompatible Statements

Examples that need adjustment for MySQL 8.0:

select NVL(id/0,'YES') from test.t1 where id = 1;

NVL is a MariaDB‑specific function. Replace with IFNULL:

select IFNULL(id/0,'YES') from test.t1 where id = 1;

select user_id, sum(amount) from test.user group by user_id DESC limit 10;

In MySQL 8.0 the GROUP BY … DESC clause is ignored. Rewrite as:

select user_id, sum(amount) from test.user group by user_id order by user_id DESC limit 10;

Compatibility Test Workflow

Run ./mysql_sniffer -p 3306 -c on the production MySQL 5.7/MariaDB host to capture queries for a few minutes (default 60 s). The output is saved to mysql_packet.sql unless redirected to console.

Copy mysql_packet.sql to a MySQL 8.0 test instance that has the same schema.

Import the file:

mysql -S /tmp/mysql_mysql8_1.sock yourDB < mysql_packet.sql > /dev/null

If the import finishes without errors, the captured workload is compatible with MySQL 8.0.

Command‑Line Parameters

usage: mysql_sniffer [-h] -p PORT [-t TABLES [TABLES ...]] [-l LOG] [-c] [-r RUNTIME] [-v]

-p PORT

: MySQL server port (default 3306). -t TABLES [TABLES ...]: Capture only the specified tables (e.g., -t t1 t2 t3). -l LOG: Path to the log file (default mysql_packet.sql). -c: Print captured SQL to the console. -r RUNTIME: Capture duration in seconds. -v: Show version and exit.

Basic Usage Example

chmod 755 mysql_sniffer
./mysql_sniffer -p 3306

The command runs for 60 seconds by default and writes the captured statements to mysql_packet.sql. Adding -c prints them to the terminal.

Supported Platforms

The binary is built for CentOS 6 and CentOS 7.

Download

GitHub repository: https://github.com/hcymysql/mysql_sniffer