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

1. View the content of the binary logs in the text format
2. If you have set up replication, then you can view the content of the relay log file
3. Generate the backup of the binary logs
4. 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,

1. Option: The configuration option used to process the binary logs. The list of the configuration options is listed below
2. 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.

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.

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:

1. An overview of mysqlbinlog utility
2. Extract the content of the binary logs and display them
3. 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.

Table of contents

• 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)

• 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
• Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022