Understanding and Analyzing MySQL Binlog for SQL History Retrieval

When business data shows abnormal changes and the source of the operation is unclear, analyzing MySQL binary logs (binlog) can reveal the historical SQL statements that were executed, helping to pinpoint bugs or accidental operations.

1. Binlog Overview

Binlog is a binary log file recorded by the MySQL server layer that logs data‑modifying statements (INSERT, UPDATE, DELETE, etc.). Queries that do not modify data, such as SELECT or SHOW , are not recorded.

In a ROW format environment ( binlog_format = ROW), you can retrieve historical SQL execution records only if the binlog_rows_query_log_events parameter is enabled (it is off by default). This parameter records the original SQL in Rows_query_event events; without it, only row data can be obtained.

2. Binlog Parsing

Because binlog is a binary file, it cannot be opened directly with a text editor; a parsing tool is required.

2.1 show binlog events

The show binlog events command can parse a specific binlog file but is slow for large volumes and is therefore not recommended for bulk extraction.

2.2 mysqlbinlog

mysqlbinlog

is the native MySQL binary log parser. It is fast, supports pipe commands for filtering, and is suitable for processing large binlog files.

On Windows, pipe commands are less convenient, so the author copies the binlog to a Linux machine for analysis.

Typical Linux command used by the author:

mysqlbinlog /data/mysql_data/bin.000008 --database EpointFrame --base64-output=decode-rows -vv --skip-gtids=true | grep -C 1 -i "delete from Audit_Orga_Specialtype" > /opt/sql.log

/data/mysql_data/bin.000008 – the binlog file to parse.

--database – limits output to rows from the specified database (cannot filter Rows_query_event).

--base64-output=decode-rows -vv – shows the actual SQL statements.

--skip-gtids=true – hides GTID information.

grep -C 1 -i "delete from dataex_trigger_record" – filters the desired DELETE statements and their timestamps.

/opt/sql.log – saves the filtered result for easy viewing.

Sample result is shown in the accompanying screenshots.

Tips:

If the SQL format is uncertain or extra spaces prevent matching, chain multiple grep commands, e.g.,

grep -C 1 -i "Rows_query" | grep -C 1 -i "Audit_Orga_Specialtype" | grep -C 1 -i "delete"

to isolate the target DELETE operations.

SQL executed by triggers is not recorded in Rows_query_event ; only the row data is logged.

The --database option cannot filter Rows_query_event ; it only filters row data.

3. Comparison of Parsing Methods

Common relational databases such as SQL Server, Oracle, and MySQL all maintain logs to record historical SQL, but the log formats and parsing techniques differ, as illustrated in the comparison diagram.