Using MySQL BinLogs: A Detailed Guide

on Data Integration, Tutorials • June 19th, 2020 • Write for Hevo

The availability of data is a crucial aspect of every business or organization. Organizations practice various methods to ensure that data is available for replication & recovery. MySQL BinLogs play a significant role in carrying out these processes. This article is aimed at educating you with in-depth knowledge about the various concepts associated with MySQL BinLogs. Follow our easy step-by-step guide to implement these concepts and understand your MySQL binary logs better.

Table Of Contents

Introduction To MySQL BinLogs

mysql binlog

In MySQL, the changes that occur within the database are logged simultaneously. The MySQL BinLog (Binary Logs) is responsible for handling these updates and hence provide a description of such events to indicate the changes made to the database being used. These can be the changes made to the data or an operation like new table creation, etc. They even provide information on statements that could have lead to a potential change.

The MySQL BinLogs serve two important purposes:

  • Replication: When working on a master server, the binary logs contain a record of the changes that have occurred. These records are sent to the slave servers to help them execute those events & make the same data changes that were made on the master server. For more information on implementing replication, you can look into the replication manual.
  • Data Recovery: Some recovery operations require using binary logs. Once the backup is restored, the recorded events are re-executed and this brings the database up to date from the time of backup.

An Easier Approach To Work With MySQL: Hevo Data

A fully managed, No-code Data Pipeline platform like Hevo Data, helps you export log data from MySQL (among 100+ Sources) to your desired destination in real-time & in an effortless manner. It allows the users to seamlessly export log data in a fully automated and convenient manner rather than requiring them to write the code repeatedly to access BinLogs hence Hevo performs the same operation automatically in a completely secure and reliable way.

Get started with hevo for free

Check out What makes Hevo amazing:

  • Reliable Data Migration: Hevo’s robust infrastructure ensures that there is no data loss and the data transfer is highly reliable.
  • Secure: End-to-end encryption and two-factor authentication ensure that your data is secured.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
Sign up here for a 14-day free trial!

Prerequisites

  • Working knowledge of MySQL.
  • Working knowledge of MySQL Shell.
  • Using command line.
  • MySQL installed on host workstation.

Working With MySQL BinLogs

Let’s have a look at the concepts you will come across here:

Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.

Enabling A MySQL BinLog & Its Verification

Enabling a MySQL BinLog is a must before being able to access the binary log. This can be done using MySQL’s my.ini file.

First, stop the currently running MySQL service using the following command:

 # service mysqld stop

Now make changes in the MySQL configuration files (/etc/my.cnf) and append the following lines of code in it:

log-bin=mysql-bin
expire_logs_days = 2
binlog_format=mixed # Recommended binary logging format – mixed

There’s another way to assign a value to the log-bin parameter:

log-bin = /var/lib/mysql/<application-name>-mysql-bin.log

Restart the service, to bring the changes into effect:

# service mysqld start

This is how you can enable a binary log in MySQL.

It always a good practice to verify whether the process to enable binary logs succeeded or not. This can be done using the following command:

mysql> show variables like '%bin%';

This code will result in an output similar to this:

+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 38152                |
| binlog_format                   | MIXED                |
| innodb_locks_unsafe_for_binlog  | OFF                  |
| log_bin                         | ON                   |
| log_bin_trust_function_creators | OFF                  |
| log_bin_trust_routine_creators  | OFF                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 419430400            |
| sql_log_bin                     | ON                   |
| sync_binlog                     | 0                    |
+---------------------------------+----------------------+
10 rows in set (0.00 sec)

The parameter to check here is the log_bin. If the value of the log_bin is ON it indicates a successful process however, if it’s OFF you can turn it on as follows:

mysql>SET GLOBAL log_bin = ON;

Determining The Current BinLogs

To retrieve a list of all the BinLogs present in your system, make use of the following command:

mysql> SHOW BINARY LOGS;

This command will display a list of all binary logs present in the system only when the binary log is enabled otherwise, it gives an error.

Current MySQL binlog

Retrieving Contents Of A MySQL BinLog

The content stored in the binary log can be viewed in a human-friendly format using the mysqlbinlog command. To use this command from the terminal, you need to first add the path of the MySQL bin directory saved on your system as a path variable.

The command can be used in the following way:

mysqlbinlog log_file_name

Example query: Here the name of the log file being used is mysql-bin.000001.

mysqlbinlog mysql-bin.000001
mysql binlog: Extracting all events.

This command returns a description of all events that have occurred for every database in the system.

Using BinLogs To Get Entries Of A Particular Database

The mysqlbinlog command displays the log file contents for all databases that are a part of the system. This command can be modified to display the events that have occurred only for a particular database using -d or -database option. These options are followed by the database name for the which the logs are required.

mysqlbinlog -d mdata mysqld-bin.000001 > crm-event_log.txt
mysqlbinlog --database mdata mysqld-bin.000001 > crm-event_log.txt

Both these commands are valid and retrieve the events log for a database called mdata and stores it in a file called event_log.txt.

Disabling MySQL BinLogs For Recovery

Creation of a MySQL BinLog during a database recovery is highly undesirable as it creates an unending loop that will keep on restoring data infinitely and each restore process will further create a binary log.

Disabling the BinLogs is, therefore a must and this can be done using the -D option in the mysqlbinlog command.

mysqlbinlog -D mysqld-bin.000001

Using -D option results in an extra line in the output, which says SQL_LOG_BIN=0.

Disabling MySQL Binlogs.

You can also use the -disable-log-bin as follows:

mysqlbinlog --disable-log-bin mysqld-bin.000001 

Setting Up The Binary Log Format

There are three possible formats in which the MySQL BinLogs can exist. These are statement, row, and mixed. The format of the BinLog can be set up using the –binlog-format option as follows:

--binlog-format=type_name

It is possible to determine the format being used by the binary log file using the following command:

show variables like "%binlog_format%"
mysql binlog: Binlog Format.

The type being used by the BinLogs can be modified using the SET GLOBAL command as follows:

SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'MIXED';		
SET GLOBAL binlog_format = 'ROW';

This will modify the BinLog type as per the users choice.

Specific Entry Extraction In BinLogs

Reading an entire binary log can be a gruelling task. It is possible to read-only the specific portions of the log file using an offset value in the mysqlbinlog command. This can be done using the -O option as follows:

mysqlbinlog -o 5 mysqld-bin.000001

This command will skip the first 5 rows of the binary log.

It is possible to extract data from a particular position in the binary log, you can specify the position you want to read data from, using the -j option as follows:

mysqlbinlog -j 123 mysqld-bin.000002 > from-123.txt
mysql binlog: Extracting Data from a Position.

It is also possible to specify a position up to where you want the data to be extracted, this is called the stop position. You can use the -stop option as follows:

mysqlbinlog --stop-position=219 mysqld-bin.000001 > upto-219.txt

MySQL BinLog Retention

The period for which a binary log exists can be set using the expire_logs_days variable. You can fix some days as a value for it. This will ensure that the logs are retained and get deleted only afterwards.

To check the current value of the expire_logs_days variable, use the following command. If you have never changed it, it will have a value of 0 by default.

show variables like "expire_logs_days";

To set a new value for this variable you can use the following command:

SET GLOBAL expire_logs_days = number_of_days;

For further information on MySQL BinLogs, you can look into the binary log documentation.

Conclusion

This article outlines how to master the skill of utilizing the power of MySQL BinLogs to monitor the changes that have happened in the database & act upon them practically in real-time with no hassle. With data growing at an exponential rate, in real-life situations handling such humongous amounts of data can be gruelling and this is where Hevo comes into the picture offering its users a fully automated, No-code solution that helps monitor data quite easily with minimal supervision.

visit our website to explore hevo

Give Hevo an opportunity to amaze you with its seamless experience! sign up for a 14-day free trial!

Let us know about your experience of working with MySQL binary logs! Share your thoughts in the comment section below.

No-code Data Pipeline Solution For MySQL