Nisarg Upadhyay
extract SQL Queries from binary logs using utility

Learn MySQL: An overview of the mysqlbinlog utility

March 2, 2021 by

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.

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;

View 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*/;

Output of mysqlbinlog

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 from binary logs using utility

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 during specific time specific from binary logs using mysqlbinlog utility

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*/;

extract SQL Queries executed in specific database from binary logs using mysqlbinlog

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:

  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

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

Nisarg Upadhyay
MySQL

About 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

217 Views