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.
Before you begin this process, be sure that you have:
Maintained enough disk on the Azure VM to copy binary logs remotely.
Downloaded and installed MySQL Workbench or another third-party MySQL client tool to connect to database.
Downloaded and installed the mysqlbinlog utility.
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:
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.
--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.
To display the directory contents, run the command ls -l.
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:
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.