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 ...

--start-position=#
    Start reading the binary log from the specified position.

--stop-position=#
    Stop reading the binary log at the specified position.

--start-datetime=datetime
    Start reading the binary log from the specified datetime.

--stop-datetime=datetime
    Stop reading the binary log at the specified datetime.

--database=db_name
    Only show events from the specified database.

--result-file=file_name
    Write the output to the specified file instead of standard output.

--verbose
    Print more information about each event.

--raw
    Output raw data without decoding.

--base64-output=[AUTO|DEFAULT|DECODE-ROWS|IGNORE]
    Control how base64-encoded events are displayed.

--rewrite-db=db1->db2
    Rewrite database names from db1 to db2

--short-form
    Displays only short form comments.

--read-from-remote-server
    Connect to the remote server and fetch the binary logs.

--host=host_name
    Connect to the specified host.

--user=user_name
    Username for connecting to the server.

--password[=password]
    Password for connecting to the server.

--port=#
    Port number for connecting to the server.

--socket=path
    Socket file for connecting to the server.

--server-id=#
    Only extract events from binlogs with the given server ID.

The `mysqlbinlog` command is a utility for reading, parsing, and processing binary log files generated by a MySQL server. These binary logs contain a record of all data modifications and database structural changes made on the server. This allows you to replay the changes for recovery purposes, auditing, or replicating data to another MySQL server. `mysqlbinlog` can display events in a human-readable format, filter events based on various criteria (date, position, database), and execute the events against a database. It is a crucial tool for MySQL administrators to manage and maintain database integrity and replication. The output of `mysqlbinlog` can be used as input for the MySQL client, allowing you to apply changes to a database directly from the binary log.

When using `--read-from-remote-server`, ensure that the MySQL server is configured to allow remote connections from the machine running `mysqlbinlog`. Also, the user specified with `--user` needs the `REPLICATION CLIENT` privilege on the MySQL server to access the binary logs.

Binary logs are crucial for setting up replication between MySQL servers. `mysqlbinlog` can be used to copy the binary log from a master server to a slave server, which can then be applied to the slave database to keep it synchronized with the master. Replication is essential for high availability and disaster recovery.

If a database suffers data loss or corruption, `mysqlbinlog` can be used to replay the events in the binary logs up to a specific point in time, restoring the database to its state before the incident.
This process typically involves identifying the last known good backup and then applying the binary log events that occurred after that backup was taken.

With the introduction of GTIDs (Global Transaction Identifiers), `mysqlbinlog` supports extracting GTID-based events for more reliable replication. GTIDs ensure that transactions are applied exactly once and in the correct order, even in complex replication topologies.

The `mysqlbinlog` command has been a core part of the MySQL distribution since the early days of MySQL. It evolved alongside the binary log format, adding support for new features and event types introduced in various MySQL versions. Its primary use has always been for point-in-time recovery and replication setup, but it also finds use in auditing and debugging database operations. The command has seen improvements in performance and filtering capabilities over time.

mysql(1), mysqld(8)