Accessing binary logs from Azure Database for MySQL – Flexible Server
Published Mar 22 2022 12:15 PM 3,421 Views
Microsoft

Binary logs, or binlogs, are files that contain details about data modifications made to a MySQL server instance. It includes information about all events that reflect database changes, for example creating a database or table, or other operations that change existing table data. Binlogs also contain other useful data such as error codes and metadata.

 

Binlogs are responsible for logging data changes or events from the source MySQL database server. The replica server reads the binlogs from the source server and stores them in relay logs until they’re replayed on replica.

 

Binlogs are also used for point-in-time-recovery (PITR) operations, after data has been restored from a backup file. In addition, binlogs can be useful in troubleshooting or helping to identify specific transactions in any associated database.

 

Change data capture (CDC) tools, such as are Maxwell and Debezium, also use binlogs. These tools track data changes in real-time, though at times there can be a lag in reporting. This information can be used for copying data to other systems or for processing events.

 

To process the binlogs on a MySQL server, use the mysqlbinlog utility to perform the following tasks:

  • View the content of the binary logs in text format.
  • View the content of the relay log file (if you have replication set up).
  • Generate a backup of the binary logs.

In this post, you'll learn how to:

  • Read binary logs live from Azure Database for MySQL – Flexible Server.
  • Copy binary logs from Azure Database for MySQL – Flexible Server.
  • Read binary files copied using the mysqlbinlog tool.

 

Prerequisites

Before you begin this process, be sure that you have:

The mysqlbinlog utility is packaged as part of the mysql-server or mysql-server-core package. It is also available in archives available for download. For example, to install the mysqlbinlog utility on an Azure VM (Ubuntu), run the following commands:

apt-get update
apt-get install mysql-server-core-8.0

 

How to read the events from binary logs live from the service

To read events from any binlogs, perform the following steps:

  1. Connect to the Azure Database for MySQL flexible server using the mysql client or MySQL Workbench.
  2. Run the following command:

    show binlog events in <binlog file name> limit 10;

 

Picture1.jpg

 

Notice that the detail displays the Event Type, the starting and ending positions in the binlog file, and some information on the event type that was run against the database.

 

How to copy binlogs from a MySQL flexible server

  1. Connect to the Azure Database for MySQL flexible server using the mysql client or MySQL Workbench.
  2. To list the binary logs available, run the following command:

    show binary logs

The output should appear similar to the following:

 

Picture2.png

 

  1. Identify the oldest binlog file in the list, and then, on the Azure VM running mysqlbinlog, run the following command:

    #  mysqlbinlog --verify-binlog-checksum --host=<servername>. mysql.database.azure.com --port=3306 --user=<User> --raw --read-from-remote-server --stop-never --connection-server-id=101 --password=<Password> <Oldest binary file name>

Note: If you want to run this as a background process, use “&” at the end of the command or use the Linux screen utility to let it run continuously.

 

This command includes the following parameters:

  • --verify-binlog-checksum - This verifies the checksums in the binary log.
  • --connection-server-id - This passes a unique numerical value i.e., server-id reported by mysqlbinlog utility when it connects to source server.

The mysqlbinlog utility connects to your MySQL flexible server as replica i.e., it opens a mysql replication thread identified as “Binlog Dump” and each replica should have unique server-id.

 

Important: While the --stop-never-slave-server-id parameter has been deprecated in MySQL 8.0 and later versions, in earlier versions you can use it in place of the --connection-server-id parameter.

 

Note: You can run the show processlist command to confirm that the mysqlbinlog is continuously copying the binlogs generated to the local machine.

Picture3.png

  

  • --raw = This option tells mysqlbinlog to write events in their original binary format. Because the files are requested from remote server and mysqlbinlog utility writes one output file for each file read from your Flexible Mysql server.
  • --stop-never = This indicates to continuously run and keeps utility connected to server.

After running this command, binlogs will be copied to the current directory.

 

  1. To display the directory contents, run the command ls -l.

Picture4.png

 

How to read binlogs using the mysqlbinlog utility

To get more information on the data in the downloaded binlog, use the mysqlbinlog utility and redirect the output to file by running the following command:

# mysqlbinlog --verbose mysql-bin.000040 > file_bin_000040.txt

The --verbose switch reconstructs row events as SQL statements.

 

After running this command, you can verify the binlog events by opening the text file. Look for specific events starting in the binlog, which will appear in the format shown below.

 

Note: The following example shows us the update query for table test_binlog.sbtest1 and the values that were changed, as well as the accompanying timestamp.

 

Picture5.png

 

To read the events in the binary logs for a specific database, run the following command:

# mysqlbinlog --verbose --database=<Database Name> <Binlog File Name> > file_bin_name.txt

 

Picture6.png

 

Conclusion

Using the information provided above, you should now be able to access binlogs from Azure Database for MySQL – Flexible Server. Use the mysqlbinlog utility to download and read these binlog files, or to help set up your Change Data Capture (CDC) pipelines.

 

As mentioned previously, binlogs contain information about data changes that occur on a MySQL instance. Binlogs include all SQL statements that update the data (INSERT, UPDATE DELETE), and the DDL statements (Create database object, drop database objects, truncate table) within the database. The information in the binlogs can help you debug or troubleshoot based on specific events.

 

If you have any feedback or questions, please leave a comment below or email our team at AskAzureDBforMySQL@service.microsoft.com.

 

Thank you!

Co-Authors
Version history
Last update:
‎May 25 2022 11:22 PM
Updated by: