mysqlbinlog

Show events from a specific binary log file

$ mysqlbinlog [path/to/binlog]

$ mysqlbinlog --database [database_name] [path/to/binlog]

$ mysqlbinlog --start-datetime='[2022-01-01 01:00:00]' --stop-datetime='[2022-02-01 01:00:00]' [path/to/binlog]

$ mysqlbinlog --start-position=[100] --stop-position=[200] [path/to/binlog]

$ mysqlbinlog --host=[hostname] [path/to/binlog]

mysqlbinlog [options] log_file ...

-d, --database=
    Lists only events for the specified database.

-f, --force-if-is-tty
    Force output even if it seems to be a TTY, useful when piping output.

-r, --result-file=
    Directs output to the specified file instead of standard output.

-s, --short-form
    Displays events in a shorter format, showing only the event data.

--start-datetime=
    Starts reading events from the specified timestamp.

--stop-datetime=
    Stops reading events at the specified timestamp.

--start-position=
    Starts reading from a specific byte offset within the log file.

--stop-position=
    Stops reading at a specific byte offset within the log file.

--read-from-remote-server
    Connects to a running MySQL server to read binary logs directly, bypassing local file access.

--base64-output={NEVER|DECODE-ROWS|AUTO}
    Controls how row-based events are displayed. DECODE-ROWS decodes them into SQL.

--include-gtids=
    Includes only events corresponding to the specified GTID set.

--exclude-gtids=
    Excludes events corresponding to the specified GTID set.

--raw
    Writes raw binary log events to one or more files, without decoding.

The mysqlbinlog command-line utility is an essential tool for working with MySQL's binary log files. These logs contain a record of all data-modifying events (SQL statements, row changes) that occur on a MySQL server, making them crucial for replication and point-in-time recovery.

mysqlbinlog processes these binary files and outputs their contents in a human-readable format, typically as SQL statements that can be re-executed. It supports various filtering options, allowing users to extract specific events based on time, position, database, or server ID. This utility is invaluable for debugging replication issues, auditing database changes, or restoring a database to a specific point in time by replaying the logged events.

When using mysqlbinlog for point-in-time recovery, ensure you have a full backup taken before the target recovery point. The output, especially with row-based replication, can be extensive and complex. Piping directly to the mysql client (e.g., mysqlbinlog binlog.000001 | mysql -u root -p) should be done with extreme caution, as it executes the decoded SQL statements immediately. Be aware of potential character set issues when restoring logs to a different server or database.

• Point-in-Time Recovery: Restoring a database to a specific moment by replaying binary log events after a full backup.
• Replication Debugging: Examining specific events to understand and resolve replication inconsistencies or failures.
• Auditing: Analyzing database changes made during a particular period, useful for security and compliance.
• Data Migration: Occasionally used to apply a subset of changes from one server to another, though more specialized tools exist for this.

The --read-from-remote-server option allows mysqlbinlog to connect to a running MySQL server and stream its binary logs directly. This is particularly useful for analyzing logs on a remote server without needing direct file system access, or for continuous log monitoring. It requires appropriate MySQL user permissions (e.g., REPLICATION CLIENT, REPLICATION SLAVE) on the remote server.

The mysqlbinlog utility has been an integral part of the MySQL ecosystem since its early versions, evolving in parallel with MySQL's replication capabilities. Its development reflects changes in binary log formats, including the transition from statement-based to row-based and mixed logging, and the introduction of Global Transaction Identifiers (GTIDs). It remains a cornerstone for database administrators managing highly available and replicated MySQL environments.

mysql(1), mysqld(8), mysqladmin(1)