Using MySQL BinLogs: A Detailed Guide

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. The binlog MySQL feature allows for efficient tracking of database changes.

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. Let’s get started?

Working With MySQL BinLogs

Let’s have a look at the concepts one by one:

1. Enabling A MySQL BinLog & Its Verification

Understanding how to check binlog in MySQL is crucial for monitoring database changes and ensuring data integrity in replication setups.  

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 is 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;

2. 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.

3. 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

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

4. 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.

5. 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.

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

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

6. 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%"

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.

7. 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

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

8. 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;

Understanding the Significance of MySQL Binary Logs: Replication and Data Recovery

In MySQL, the changes that occur within the database are logged simultaneously. Understanding how to read MySQL bin log is essential for performing forensic analysis or auditing activities on database operations. 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.

Troubleshooting Common Problems

• BinLogs Not Being Written If BinLogs are enabled but not being written to the specified file, it may stem from various issues. Verify that the MySQL server has the necessary permissions to write to the BinLog file, and inspect the MySQL error log for any BinLog-related messages.
• ‍BinLogs Filling Disk Space Large BinLog files can lead to disk space and performance issues. You can address this by setting a retention period using the “expire_logs_days” variable or manually deleting old logs using the “PURGE BINARY LOGS” statement.
• ‍MySQL Binlog Utility Malfunction If encountering problems with the MySQL Binlog utility, confirm the correct path to the BinLog file and ensure appropriate permissions for reading it. Additionally, review the MySQL error log for any messages regarding the MySQL Binlog utility.
• ‍Replication Failure When using BinLogs for replication and encountering issues, investigate potential problems by checking the MySQL error log on both master and slave servers for replication-related messages. Utilize the “SHOW SLAVE STATUS” command to assess replication 
• Check for Driver and Software Updates Ensure all drivers and software are up-to-date, as outdated versions can lead to various problems. While most devices automatically check for updates, it’s prudent to manually verify to ensure you’re utilizing the latest versions.

Understanding and addressing common problems associated with MySQL BinLogs enables effective utilization of this feature for data management and protection.

Learn More About:

MySQL Disable Binary Logging

Conclusion

This article outlines how to master the skill of utilizing the power of MySQL BinLogs Commands 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.

Hevo is an automated no-code data pipeline that enables seamless data integration between your source and destination. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, checkout our unbeatable pricing to choose the best plan for your organization.

Frequently Asked Questions