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.

Table of Contents

• Prerequisites
• What is MySQL?
  • Key Features of MySQL
• What is Binary Logging in MySQL?
• Types of Binary Logging
• MySQL Disable Binary Logging: Easy Steps
• Conclusion

Prerequisites

Basic knowledge of MySQL Binary Logging.

What is MySQL?

Developed in 1995, MySQL is a prevalent, open-source, Relational Database Management System (RDBMS) developed, supported, and distributed by Oracle corporation. Since MySQL is a Relational Database, it can store data in the rows and columns of a table.

MySQL leverages a basic Client-Server model for creating tables and managing Databases in the MySQL Server. MySQL uses a well-known query language called Structured Query language (SQL) that helps users efficiently perform all CRUD (Create, Read, Update and Delete) operations on Databases. In other words, SQL commands are used for accessing, modifying, and adding data to your MySQL Databases.

MySQL is supported by many Operating Systems, including Microsoft Windows, Oracle Solaris, Symbian, Aix, macOS, and Linux. Today, MySQL is part of the Modern LAMP stack, including a Linux-based Operating System, Apache Web Server, MySQL Database, and PHP for processing.

Key Features of MySQL

Here are a few reasons responsible for the immense popularity of MySQL.

• Easy to Use: MySQL supports many programming languages, including PHP, PERL, C, C++, JAVA, and more. It uses tools to automate time-consuming tasks like Reporting, Data Analysis, Server Management, and more.
• Supports Multiple Data Structures: MySQL also supports different data structures like JSON and Geospatial data, including Logical, Alphanumeric, Date, and Time data types.
• Open-Source: Since MySQL is licensed under GNU (General Public License), it is available for free. MySQL can be customized as per the requirements using Oracle’s open-source MySQL Codebase.
• High Performance: MySQL Server provides best-in-class query performance. It also consists of a memory cache, table index partitioning, and fast loading utility.
• Data Protection: The Access Privilege Systems and User Account Management System allow users to control the access to their MySQL data. There are also other methods like Host-based Verification and Password Encryption, which can be used to protect MySQL data.

What is Binary Logging in MySQL?

The Binary Logging process is used for handling data modifications of 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 the operations like 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.