The server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a replica server in a replication setup because relay logs have the same format as binary logs. The binary log and relay log are discussed further in Section 7.4.4, “The Binary Log”, and Section 19.2.4, “Relay Log and Replication Metadata Repositories”.

Invoke mysqlbinlog like this:

mysqlbinlog [options] log_file ...

For example, to display the contents of the binary log file named binlog.000003, use this command:

mysqlbinlog binlog.000003

The output includes events contained in binlog.000003. For statement-based logging, event information includes the SQL statement, the ID of the server on which it was executed, the timestamp when the statement was executed, how much time it took, and so forth. For row-based logging, the event indicates a row change rather than an SQL statement. See Section 19.2.1, “Replication Formats”, for information about logging modes.

Events are preceded by header comments that provide additional information. For example:

# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

In the first line, the number following at indicates the file offset, or starting position, of the event in the binary log file.

The second line starts with a date and time indicating when the statement started on the server where the event originated. For replication, this timestamp is propagated to replica servers. server id is the server_id value of the server where the event originated. end_log_pos indicates where the next event starts (that is, it is the end position of the current event + 1). thread_id indicates which thread executed the event. exec_time is the time spent executing the event, on a replication source server. On a replica, it is the difference of the end execution time on the replica minus the beginning execution time on the source. The difference serves as an indicator of how much replication lags behind the source. error_code indicates the result from executing the event. Zero means that no error occurred.

The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to redo the statements in the log. This is useful for recovery operations after an unexpected server exit. For other usage examples, see the discussion later in this section and in Section 9.5, “Point-in-Time (Incremental) Recovery”. To execute the internal-use BINLOG statements used by mysqlbinlog, the user requires the BINLOG_ADMIN privilege (or the deprecated SUPER privilege), or the REPLICATION_APPLIER privilege plus the appropriate privileges to execute each log event.

You can use mysqlbinlog to read binary log files directly and apply them to the local MySQL server. You can also read binary logs from a remote server by using the --read-from-remote-server option. To read remote binary logs, the connection parameter options can be given to indicate how to connect to the server. These options are --host, --password, --port, --protocol, --socket, and --user.

When binary log files have been encrypted, which can be done from MySQL 8.0.14 onwards, mysqlbinlog cannot read them directly, but can read them from the server using the --read-from-remote-server option. Binary log files are encrypted when the server's binlog_encryption system variable is set to ON. The SHOW BINARY LOGS statement shows whether a particular binary log file is encrypted or unencrypted. Encrypted and unencrypted binary log files can also be distinguished using the magic number at the start of the file header for encrypted log files (0xFD62696E), which differs from that used for unencrypted log files (0xFE62696E). Note that from MySQL 8.0.14, mysqlbinlog returns a suitable error if you attempt to read an encrypted binary log file directly, but older versions of mysqlbinlog do not recognise the file as a binary log file at all. For more information on binary log encryption, see Section 19.3.2, “Encrypting Binary Log Files and Relay Log Files”.

When binary log transaction payloads have been compressed, which can be done from MySQL 8.0.20 onwards, mysqlbinlog versions from that release on automatically decompress and decode the transaction payloads, and print them as they would uncompressed events. Older versions of mysqlbinlog cannot read compressed transaction payloads. When the server's binlog_transaction_compression system variable is set to ON, transaction payloads are compressed and then written to the server's binary log file as a single event (a Transaction_payload_event). With the --verbose option, mysqlbinlog adds comments stating the compression algorithm used, the compressed payload size that was originally received, and the resulting payload size after decompression.

For more information on binary log transaction compression, see Section 7.4.4.5, “Binary Log Transaction Compression”.

When running mysqlbinlog against a large binary log, be careful that the filesystem has enough space for the resulting files. To configure the directory that mysqlbinlog uses for temporary files, use the TMPDIR environment variable.

mysqlbinlog sets the value of pseudo_replica_mode or pseudo_slave_mode to true before executing any SQL statements. This system variable affects the handling of XA transactions, the original_commit_timestamp replication delay timestamp and the original_server_version system variable, and unsupported SQL modes.

mysqlbinlog supports the following options, which can be specified on the command line or in the [mysqlbinlog] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 6.2.2.2, “Using Option Files”.