MySQL binary logging is used for replication and also for point-in-time recovery of a MySQL database instance. It records any changes to database contents and may be recorded in a “statement-based” or “row-based” format. The default location to find the binary logs is /var/lib/mysql, though their location may be defined as desired through the configuration file.
As the name implies, the binary logs are written in binary format and are thus not human-readable; however, the mysqlbinlog utility can be used to view the binary logs’ contents in text format. This output can be viewed as the output following the command or sent to a specific location and file. Depending on the binary log files’ size, you may want to view only a portion of the file, especially if disk space is limited.
Mysqlbinlog Utility Commands
To view the entire contents of a binary log, binlog.000001 in this example, run the following:
shell> mysqlbinlog binlog.000001
To write the entire contents of binlog.000001 to a file, run the following:
shell> mysqlbinlog binlog.000001 > /path/to/file/binlog000001.txt
Note: If GTIDs are enabled, run the command as follows:
shell> mysqlbinlog –skip-gtids binlog.000001 > /path/to/file/binlog000001.txt
Again, ensure there’s enough disk space for a large binlog file before running either of the previous commands.
To view a portion of the binary log, you can do so based on many different options: events from one specific database, events based on the binlog position, events from one specific day or one specific hour of one day, etc.
To view binlog entries starting at a specific time — such as 5:00am on July 17, 2020 — run the following:
shell> mysqlbinlog –start-datetime=”2020-07-17 05:00:00″ binlog.000001
The output from the above commands will show header information, which can be ignored using the “-s” or “–short-form” option. This will display only the SQL queries and filter out each entry’s header information for easier viewing and smaller file sizes.
When using mysqlbinlog to playback events to the database, for example using them for point-in-time recovery coupled with a most recent backup, the utility is used similarly. For example, to play the events from a given binary log file, binlog.000004, run the following:
shell> mysqlbinlog binlog.000004 | mysq -u userhere -p
Note: If more than one binary log file is needed for the restore, run all binary log files as one command to avoid any data loss due to temporary tables being dropped between file processing:
shell> mysqlbinlog binlog.000004 binlog.000005 binlog.000006 | mysq -u userhere -p
Note that piping binary logs into the database for a restore procedure will also have all events written to the current binary log. Binary logging must be disabled during the restore of the binary logs to avoid this. This can be accomplished by using the “-D” or “–disable-log-bin” options.
Note that this blog covers some of the more common options used with the mysqlbinlog utility; however, many more exist to fine-tune your needs with viewing or replaying the binary logs.
Mysqlbinlog Utility Summary
Viewing the contents of the MySQL binary logs is done by using the mysqlbinlog utility, which is available with any standard installation of MySQL. The binary logs contain a record of all changes made to the MySQL database and are used in replication and also for point-in-time recovery. Viewing the contents of the binary logs in text format is often useful for troubleshooting replication, auditing purposes, or general reviews of changes made to the database contents. The mysqlbinlog utility is also used for piping the binary logs into MySQL for point-in-time recovery.