mysqlbinlog
Process MySQL binary log files
TLDR
Show events from a specific binary log file
Show entries from a binary log for a specific database
Show events from a binary log between specific dates
Show events from a binary log between specific positions
Show binary log from a MySQL server on the given host
SYNOPSIS
mysqlbinlog [options] log_file ...
PARAMETERS
--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.
DESCRIPTION
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.
CAVEATS
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.
REPLICATION
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.
POINT-IN-TIME 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.
GTID-BASED REPLICATION
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.
HISTORY
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.