View Raw MySQL Transactions Using mysqlbinlog

MySQL’s binary log (binlog) records all DDl and DML events that modify database structure or content, which is essential for replication and data recovery. This guide demonstrates how to use the mysqlbinlog command-line utility to convert raw, binary transaction logs into human-readable text, allowing you to audit database changes, debug issues, and extract specific SQL statements.

Locating Your Binary Logs

Before you can read the binary logs, you must identify their location. Log into your MySQL server and run the following command to list the active binary log files:

SHOW BINARY LOGS;

To find the directory where these files are stored on your system, check the log_bin path variable:

SHOW VARIABLES LIKE 'log_bin_basename';

Viewing Raw Log Events

Because binary logs are stored in a proprietary binary format, opening them with a standard text editor will display unreadable characters. The mysqlbinlog utility, included with your MySQL installation, parses these files.

To view the raw content of a specific log file, run the utility from your terminal, passing the path to the log file:

mysqlbinlog /var/lib/mysql/binlog.000001

This command outputs the events chronologically, showing metadata such as server IDs, timestamps, execution times, and the specific SQL statements (for statement-based logging).

Decoding Row-Based Logging (ROW Format)

If your MySQL server uses row-based logging (binlog_format=ROW), the actual data changes are written as base64-encoded binlog events. Running the basic command will show unreadable base64 strings.

To decode these events into human-readable, pseudo-SQL statements, use the verbose (-v or -vv) option:

mysqlbinlog -v /var/lib/mysql/binlog.000001

• -v: Reconstructs the rows into commented SQL statements (e.g., showing INSERT, UPDATE, DELETE operations).
• -vv: Adds comments displaying the data types and metadata of the columns involved in the transactions.

To suppress the raw base64 data entirely and only view the reconstructed SQL, add the --base64-output=DECODE-ROWS option:

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000001

Filtering Transaction Events

Binary logs can grow extremely large. You can use several filters to isolate the specific transactions you need to analyze.

Filter by Database

To view events associated with only one specific database, use the -d or --database flag:

mysqlbinlog -d my_database /var/lib/mysql/binlog.000001

Filter by Time Range

To find events that occurred within a specific timeframe, use the --start-datetime and --stop-datetime options (formatted as YYYY-MM-DD HH:MM:SS):

mysqlbinlog --start-datetime="2023-10-25 10:00:00" --stop-datetime="2023-10-25 11:00:00" -v /var/lib/mysql/binlog.000001

Filter by Log Position

If you know the exact log positions (which are displayed in the log output next to at), you can extract events between specific markers using --start-position and --stop-position:

mysqlbinlog --start-position=107 --stop-position=450 /var/lib/mysql/binlog.000001

Exporting the Output

For extensive analysis, it is often easiest to export the decoded log output into a standard text file:

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000001 > decoded_transactions.sql