How to disable mysql binary log with log_bin variable?

1.1K Asked by DipikaAgarwal in SQL Server , Asked on Mar 15, 2023

Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled:

log_bin = /var/log/mysql/mysql-bin.log

When I want to disable binary logging on such installation, comment out the line in my.cnf works of course, but I wonder if there is a way to disable binary logging by setting explicitly log_bin to OFF, in a debian style, I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf, so default my.cnf is not changed and can be easily updated by apt, if necessary.

I tried "log_bin = 0", "log_bin = OFF" or "log_bin =" but none works…

Answered by Dipika Agarwal

In MySQL 8, it is the option disable_log_bin which has to be provided without any parameter in the my.cnf file in the [mysqld] section:

[mysqld]

disable_log_bin

some-other-option = any-value

From the manual entry Binary Logging Options and Variables:

To disable mysql binary log, you can specify the --skip-log-bin or --disable-log-bin option at startup. If either of these options is specified and --log-bin is also specified, the option specified later takes precedence. When binary logging is disabled, the log_bin system variable is set to OFF.

To retain only a specific minimal amount of data, you can also use binlog_expire_logs_seconds from MySQL 8: Sets the binary log expiration period in seconds. After their expiration period ends, binary log files can be automatically removed. Possible removals happen at startup and when the binary log is flushed.

Your Answer

Answer (1)

Ranjana

To disable the MySQL binary log, you need to modify the MySQL server configuration. The binary log is controlled by the log_bin system variable. Disabling it involves editing the MySQL configuration file (my.cnf or my.ini, depending on your operating system) and ensuring the log_bin variable is not set or is commented out. Here's a step-by-step guide to do this:

Steps to Disable MySQL Binary Log

Locate the MySQL Configuration File:

On Linux/Unix: /etc/mysql/my.cnf or /etc/my.cnf

On Windows: C:ProgramDataMySQLMySQL Server X.Ymy.ini

Edit the Configuration File:

Open the configuration file in a text editor with appropriate permissions (e.g., using sudo on Linux).

  sudo nano /etc/mysql/my.cnf

Modify the log_bin Setting:

Search for the log_bin setting in the configuration file. If it is set, either comment it out or remove it entirely. Commenting out a line is done by adding a # at the beginning of the line.

Modify the log_bin Setting:

Search for the log_bin setting in the configuration file. If it is set, either comment it out or remove it entirely. Commenting out a line is done by adding a # at the beginning of the line.

# Comment out or remove these lines to disable binary logging

# log_bin

# log_bin_basename

# log_bin_index

Ensure there are no active log_bin settings. The section of the configuration file might look like this after modification:

[mysqld]

# Binary logging is disabled

# log_bin = /var/log/mysql/mysql-bin.log

Restart the MySQL Server:

After modifying the configuration file, restart the MySQL server to apply the changes.

5 Months