MySQL Disable Binary Logging: The Complete Guide

It becomes essential to notice the changes in the Databases while working with any Database Server like MySQL, MongoDB, MariaDB, and more. In the MySQL Server, you can track the changes to a Database by using the concept of Binary Logging. This article is all about MySQL Disable Binary Logging.

Binary Logging stores the Database changes in files called Binary Logs. These Binary Logs can help you to replicate and recover your data in MySQL. But replicating data might require more storage space on the MySQL Database Server. Therefore, to optimize for storage space, you can implement MySQL Disable Binary Logging and stop replicating MySQL data, saving a lot of storage space on the MYSQL Server. Let’s discuss how to disable Binary Logging in the MySQL Server.

Prerequisites

Basic knowledge of MySQL Binary Logging.

What is Binary Logging in MySQL?

The Binary Logging process handles data modifications in the MySQL databases. A Binary Log (binlog) file contains information about data changes made to the MySQL Databases. These data changes are nothing but the events that occur in the MySQL Databases due to operations like Create, Update, Insert, and Delete.

Binary Logging provides 2 important benefits.

• Replication: MySQL Server consists of a Master-Slave replication process, containing 2 or more servers replicating data on multiple slaves. The Binary Logs in MySQL are used on the Master Replication Servers to send records of the statements to the Slave Servers for replication processes.
  The Binary Log format stores the details that are specific to replication. Master Servers send events in their binary log to Slave Servers, which execute those events to make the same changes that were made on the Master Servers. Therefore, Slave Servers stores events from Master Servers into a Relay Log till they are executed.
• Recovery: The data recovery operations generally use binary logs. When the file is restored after the backup, the events recorded in the Binary Log are executed. As a result, these events create a new Database from the backup point.

Types of Binary Logging

Let’s take a look at the 3 important types of Binary Logging.

• Statement-based Binary Logging: Statement-based Binary Logging contains the SQL statements, which produce events like Insert, Update and Delete.
• Row-based Binary Logging: Row-based Binary Logging consists of events that change individual rows.
• Mixed-based Binary Logging: By default, Mixed-based Binary Logging uses statement-based Binary Logging, then switches to Row-based Binary Logging automatically when needed.

Now that you’re familiar with the concept of Binary Logging, let’s dive straight into MySQL Disable BinLog.

MySQL Disable Binary Logging: Easy Steps

Binary Logs contain information about the DDL (Data Definition Language) operations changes on the MySQL tables. Binary Logging is used for replication and restoration operations for point-in-time recovery in the MySQL Database.

Follow the below-mentioned commands/steps for MySQL Disable Binary Logging.

• Step 1: Check if the binlog is enabled or disabled using the below command.

mysql> show variables like 'log_bin';

Output:

• Step 2: Check the list of the binary files created.

mysql> SHOW BINARY LOGS;

Output:

• Step 3: Enable the Binary Log in MySQL. Edit the my.ini file and enter the following parameter.

log-bin="RAC1-bin"

Use the below command to check the status of the Binary Log.

mysql> show variables like 'log_bin';

Output:

• Step 4: MySQL Disable Binary Logging, you can do so by editing the my.ini file by commenting on the log-bin parameter.

#log-bin="RAC1-bin"

Enter the below command in the my.ini file and save it. Restart the MySQL.

skip-log-bin

Enter the below command to check the Binary Log files.

mysql> show binary logs;

Output:

This is because you have already implemented MySQL Disable Binary Logging in the earlier commands.

• Step 5: Use the below command to check the status of the Binary Log.

mysql> show variables like 'log_bin';

Output:

• Step 6: The Binary Logging format can be started by using the MySQL Server with the binlog_format=type. You can change the parameter value of the binlog_format parameter by using the below command.

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

To see the current value in the binlog_format, use the following command.

mysql> show variables like 'binlog_format';

Output:

• Step 7: The expire_logs_days is used to ensure that the logs are copied for backup before deleting. You can check the expire_log_days parameter using the below command.

mysql> show variables like 'expire_logs_days';

Output:

• Step 8: You can display the contents of the binary log files using the mysqlbinlog utility. For example, the below command consists of the binary log file named binlog.000003.

mysqlbinlog binlog.0000003

The output consists of events in the binlog.0000003 log file. If it is the statement-based logging, the event information will include the SQL statement, the ID of the server on which it was executed, how much time it took for execution, and more. If it is row-based logging, the event indicates a row change instead of an SQL statement.

In the versions before MySQL 8.0, Binary Logging was enabled using the --log-bin option and disabled by default. But from MySQL 8.0, Binary Logging is enabled by default. If you use the mysqld for initializing the data directory manually by invoking it with the --initialize or -initialize-insecure option, when the Binary Logging is disabled by default, you can enable Binary Logging by using the --log-bin option.

The log bin variable shows the status of the Binary Logging on the server by turning it ON when Binary Logging is enabled. To disable the MySQL Binary Logging, you can use the --skip-log-bin or --disable-log-bin option at startup. If one of the two options is used with –log-bin, the option that has been used recently gets precedence. The –log-bin variable is turned OFF when the Binary Logging is disabled.

GTID (Global Transaction Identifier) is a unique identifier created and associated with each transaction committed on the server. This identifier remains unique not only to the server on which it originated but also to the other servers in the given replication setup. When these GTIDs are used in the MySQL Server, and if you disable Binary Logging while restarting the server after an abnormal shutdown, some GTIDs will be lost, which might cause replication to fail.

In the normal shutdown, some GTIDs from the current binary log are saved to the mysql.gtid_executed table. But, in an abnormal shutdown, this process does not take place. If the MySQL Disable Binary Logging is ON, the server cannot access the binary log file to recover GTIDs. As a result, the replication process does not execute.

MySQL 5.7 Server would not start if the server_id is not specified when Binary Logging is enabled. In the later version of MySQL 8.0, the server_id is set to 1 by default. With this default server_id, the server gets started when the Binary Logging is enabled.

Conclusion

This article helped you work around MySQL Disable Binary Logging and also focused on the need for Binary Logging in MySQL Server along with its features. Binary Logging in MySQL is used for maintaining the information about the data changes that occurred on the MySQL instance. It enables replication, where data can be sent from one or more masters to one or more slaves based on the content of the Binary Log files.

visit our website to explore hevo

However, if you’re looking to move complex sets of data in and out of MySQL, you can check out Hevo and its salient features. Hevo Data with its strong integration with 100+ Sources & BI tools such as MySQL, allows you to not only export data from multiple sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with MySQL Disable Binary Logging in the comments section below.

Frequently Asked Questions