Learn MySQL: An overview of the mysqlbinlog utility

The mysqlbinlog utility is used to process the binary logs of the MySQL server. Using mysqlbinlog utility, we can perform the following tasks:

View the content of the binary logs in the text format
If you have set up replication, then you can view the content of the relay log file
Generate the backup of the binary logs
Disable the binary logs during the database restore process

In my previous article, I have explained about the binary logs. In the binary logs, MySQL Server writes changes made on the database server in the form of events. The changes are written in binary format. These contents can be viewed using the mysqlbinlog utility. The format of the binary logs and relay logs is the same; therefore, if you have set up replication, you can view the relay log file’s content using mysqlbinlog. The syntax to invoke the mysqlbinlog utility is the following:

C:\> mysqlbinlog [option] [name of the binary log]..

In the syntax,

Option: The configuration option used to process the binary logs. The list of the configuration options is listed below
Name of the binary log: The name of the binary log must be specified in this parameter

The mysqlbinlog utility provides many options to process the binary logs. Following is the list.

Option	Description
– –base64-output	Show the entries of the binary log using base-64 encoding
– –bind-address	The network interface that is used to connect to MySQL Server
– –character-sets-dir	Directory where character sets are installed
– –database	Display the binary log event of the specified database
– –default-auth	The type of authentication used to connect to the server
– –default-auth	The type of authentication used to connect to the server
– –disable-log-bin	Disable the binary logging
– –force-if-open	Display the content of binary log files even if it is open
– –help	Display the list of options
– –hexdump	Display a hex dump of the log. It is displayed in the comments
– –host	Hostname on which the MySQL Server is installed
– –offset	Skip the entries in the log. This option skips the first N entries. Here N is the number specified in the option
– –Password	Specify the Password to connect to the server
– –plugin-dir	The location of the directory where MySQL plugins are installed
– –port	TCP/IP port number for the connection
– –print-defaults	Display the default options
– –protocol	Network protocol to use to connect MySQL
– –raw	Export the events in binary format to output files
– –read-from-remote-master	Read the content of the binary log from a MySQL master
– –read-from-remote-server	Read the content of the binary log from the MySQL server
– –result-file	Save the output to the file name specified in this argument
– –server-id	Extract only the events created by the server ID specified in the argument
– –shared-memory-base-name	Shared-memory name. This name is used by shared-memory connections (Windows only)
– –short-form	Display only the statements from the binary log
– –socket	Unix socket file or Windows named pipe
– –start-datetime	Read binary log from the first event with a timestamp equal to or later than datetime argument
– –start-position	Decode binary log from the first event with a position equal to or greater than argument
– –stop-datetime	Stop reading binary log at first event with a timestamp equal to or greater than datetime argument
– –stop-position	Stop decoding binary log at first event with a position equal to or greater than argument
– –to-last-log	Read the content of all binary logs
– –user	User name to connect to the MySQL server
– –verbose	This option reconstructs the row events as SQL statements
– –verify-binlog-checksum	Verify checksums
– –version	Display the version

Example of mysqlbinlog command

First, let’s populate the list of the binary logs. To get the list of the binary logs of MySQL Server, run the below command in MySQL command line utility:

mysql> show binary logs;

To extract the content of the binary log named NISARG-PC-bin. 000001, execute the following command on command prompt (Windows) or bash (Linux).

C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog NISARG-PC-bin.000001

Output:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201222 9:33:57 server id 1 end_log_pos 124 CRC32 0xd8fa991a Start: binlog v 4, server v 8.0.19 created 201222 9:33:57 at startup
ROLLBACK/*!*/;
BINLOG ‘
LXDhXw8BAAAAeAAAAHwAAAAAAAQAOC4wLjE5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAtcOFfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgEamfrY
‘/*!*/;
# at 124
#201222 9:33:57 server id 1 end_log_pos 155 CRC32 0xc4899cd3 Previous-GTIDs
# [empty]
# at 155
#201222 9:33:59 server id 1 end_log_pos 178 CRC32 0x6005e6e6 Stop
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Extract SQL queries from the binary logs

To extract the MySQL queries from the binary logs, we must use –short-form option. For the demonstration, I have created a database named employee. I have created a table named tbldepartment in the employee database. To extract the SQL statement used to create a database and table, the command should be written as follows:

C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog – –short-form NISARG-PC-bin.000002

Output:

Note: The output of the command contains a lot of information, so I have trimmed the output

SET @@session.character_set_client=4,@@session.collation_connection=4,@@session.collation_server=255/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database employee
/*!*/;
# original_commit_timestamp=1608617926371335 (2020-12-22 11:48:46.371335 India Standard Time)
# immediate_commit_timestamp=1608617926371335 (2020-12-22 11:48:46.371335 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608617926371335*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
use employee/*!*/;
SET TIMESTAMP=1608617926/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE employee.department (
department_id INT NOT NULL AUTO_INCREMENT,
department_name VARCHAR(45) NOT NULL,
PRIMARY KEY (department_id))
/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Extract SQL queries executed between specific times

Suppose you want to extract the queries executed during a specific period; you can use – –start-datetime and – –stop-datetime options. For the demonstration, I have inserted a record in the department table by running the following query. These records were inserted between 01:13 PM to 01:15 PM on the date 22-12-2020.

Now, run the below command to get the SQL queries executed between the time specified in – –start-datetime and – –stop-datetime options.

C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog – –short-form NISARG-PC-bin.000004 – –start-datetime “2020-12-22 13:13:00” – –stop-datetime “2020-12-22 13:15:00”

The output is the following:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
# original_commit_timestamp=1608623082442942 (2020-12-22 13:14:42.442942 India Standard Time)
# immediate_commit_timestamp=1608623082442942 (2020-12-22 13:14:42.442942 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608623082442942*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608623082/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1,
@@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
SET INSERT_ID=7/*!*/;
use employee/*!*/;
SET TIMESTAMP=1608623082/*!*/;
insert into department (department_name) values (‘Accounts’)
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Extract SQL queries executed on a specific database

To extract the queries executed on a specific database, we can use the – –database option. Suppose we want to populate the queries executed on the employee database; the command should be written as follows:

C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog – –short-form NISARG-PC-bin.000004 –database employee

Output is the following:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;

WARNING: The option – –database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options – –exclude-gtids or – –include-gtids, respectively, instead

# [empty]
# original_commit_timestamp=1608622636262474 (2020-12-22 13:07:16.262474 India Standard Time)
# immediate_commit_timestamp=1608622636262474 (2020-12-22 13:07:16.262474 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608622636262474*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608622636/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
SET INSERT_ID=4/*!*/;
use employee/*!*/;
SET TIMESTAMP=1608622636/*!*/;
insert into department (department_name) values (‘IT’)
/*!*/;
COMMIT/*!*/;
# original_commit_timestamp=1608622636274467 (2020-12-22 13:07:16.274467 India Standard Time)
# immediate_commit_timestamp=1608622636274467 (2020-12-22 13:07:16.274467 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608622636274467*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608622636/*!*/;
BEGIN
/*!*/;
SET INSERT_ID=5/*!*/;
SET TIMESTAMP=1608622636/*!*/;
insert into department (department_name) values (‘Sales’)
/*!*/;
COMMIT/*!*/;
# original_commit_timestamp=1608622636284771 (2020-12-22 13:07:16.284771 India Standard Time)
# immediate_commit_timestamp=1608622636284771 (2020-12-22 13:07:16.284771 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608622636284771*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608622636/*!*/;
BEGIN
/*!*/;
SET INSERT_ID=6/*!*/;
SET TIMESTAMP=1608622636/*!*/;
insert into department (department_name) values (‘HR’)
/*!*/;
COMMIT/*!*/;
# original_commit_timestamp=1608623082442942 (2020-12-22 13:14:42.442942 India Standard Time)
# immediate_commit_timestamp=1608623082442942 (2020-12-22 13:14:42.442942 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608623082442942*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608623082/*!*/;
BEGIN
/*!*/;
SET INSERT_ID=7/*!*/;
SET TIMESTAMP=1608623082/*!*/;
insert into department (department_name) values (‘Accounts’)
/*!*/;
COMMIT/*!*/;
# original_commit_timestamp=1608623147759624 (2020-12-22 13:15:47.759624 India Standard Time)
# immediate_commit_timestamp=1608623147759624 (2020-12-22 13:15:47.759624 India Standard Time)
/*!80001 SET @@session.original_commit_timestamp=1608623147759624*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
SET TIMESTAMP=1608623147/*!*/;
truncate table department
/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Disable the binary logs while restoring the database

When you are doing a point in time recovery using the binary logs, the restore process generates a binary log. During the restoring process, if the binary logs are enabled, the database will never be restored because the database restoration process generates the binary logs. The same binary log is used to restore the database. So, while restoring the database, we must disable the binary logs. To disable the binary logs, you must remove the following lines from the my.ini file.

log-bin=”NISARG-PC-bin”
binlog_format=’STATEMENT’

Restart the MySQL Services.

Backup the binary logs using mysqlbinlog

The mysqlbinlog utility can be used to back up the binary logs. The mysqlbinlog utility can write the content of the binary logs in a different file in the binary format that allows us to create a backup file of the existing binary log files. The mysqlbinlog utility can generate the static backup and live backup of the binary logs. The static backups contain the set of all binary log files. When we enable the continuous or live backup, the mysqlbinlog remains connected to the MySQL Server. Once the backup reaches the last binary logs, it copies new events written in the last log file. The continuous backup process runs until the server is stopped or we forcibly stop the mysqlbinlog.

Following are the mysqlbinlog options that are used to back up the binary logs.

Option	Description
– –read-from-remote-server	This option is used to read the content of the binary logs from the remote server.
– –raw	This option is used to write the binary output.
– –stop-never	This option keeps the mysqlbinlog connected with the MySQL Server and reads the new events from the binary logs. This option is used to perform the live or continuous backup.
– –result-file	This option saves the backup of binary logs in a different directory.

Examples of binary log backups

The following command is used to back up the binary log named NISARG-PC-bin.000001.

C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog – –raw – –read-from-remote-server – –host=NISARG-PC – –user nisarg – –password NISARG-PC-bin.000001

To back up all binary logs, we should specify the binary logs – –to-last-log option. While using this option, we must specify the first binary log.

C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog – –raw – –read-from-remote-server – –host=NISARG-PC – –user nisarg – –password – –to-last-log NISARG-PC-bin.000001

To generate a live backup of binary logs, run the following command. The backup starts with NISARG-PC-bin.000003 and stays connected to the MySQL Server to copy new events.

C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog – –raw – –read-from-remote-server – –host=NISARG-PC – –user nisarg – –password –stop-never – –to-last-log NISARG-PC-bin.000001

To save the backup to a different directory, we can use –result-file option. The command should be written as follows to save the backup of NISARG-PC-bin.000002 to C:\BinaryLogs directory

C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog – –raw – –read-from-remote-server – –host=NISARG-PC – –user nisarg – –password NISARG-PC-bin.000002 – –result-file “C:\BinaryLogs”

Summary

In this article, we learned about mysqlbinlog utility and its usage. We have learned the following topics:

An overview of mysqlbinlog utility
Extract the content of the binary logs and display them
Generate the backup of the binary logs using mysqlbinlog utility

In the next article, I will explain how we can perform point in time recovery using a combination of mysqldump and mysqlbinlog utility.

Learn MySQL: Querying data from MySQL server using the SELECT statement

Learn MySQL: What is pagination

Learn MySQL: Sorting and Filtering data in a table

Learn MySQL: Add data in tables using the INSERT statement

Learn MySQL: Create and drop temp tables

Learn MySQL: Delete and Update Statements

Learn MySQL: The Basics of MySQL Stored Procedures

Learn MySQL: The Basics of MySQL Views

Learn MySQL: An overview of MySQL Binary Logs

Learn MySQL: An overview of the mysqlbinlog utility

Learn MySQL: Run multiple instances of MySQL Server on Windows 10

Learn MySQL: MySQL String Functions

Learn MySQL: Control Flow functions

Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive

Learn MySQL: MySQL Copy table

Author
Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022

SQLShack

Learn MySQL: An overview of the mysqlbinlog utility