Disable MySQL binary logging with log_bin variable

Question

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

Answer 1

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here.

The key part of the question is:

I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf

You can do this from an included option file using the option skip-log-bin. E.g. you might create /etc/mysql/conf.d/disable_binary_log.cnf with the following contents:

[mysqld]
skip-log-bin

You can add skip- as a prefix to other options to disable them in this way too.

Related Q & A: To clear currently accumulated logs, see this answer.

Answer 2

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 binary logging, 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.

Answer 3

From the Manual for MariaDB (https://mariadb.com/kb/en/library/activating-the-binary-log/)

If you don't provide a name (which can, optionally, include an absolute path), the default will be datadir/log-basename-bin, datadir/mysql-bin or datadir/mariadb-bin

However,

Clients with the SUPER privilege can disable and re-enable the binary log for the current session by setting the sql_log_bin variable.

SET sql_log_bin = 0;

SET sql_log_bin = 1;

However, if you say it was enabled by default during installation, you must disable by removing the line from your my.cnf file and then restart mysqld. You can also purge the binary data (https://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html)

PURGE BINARY LOGS

Finally, as the other poster's answer mentions, in MySQL 8 there is an option to explicitly turn off binary logging from the configuration file using the --skip-log-bin or --disable-log-bin options in the [mysqld] section of the config file.

To disable binary logging, 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.

Answer 4

If you are not replicating, you can disable binlogging by changing your my.ini or my.cnf file. Open your my.ini or /etc/my.cnf (/etc/mysql/my.cnf), enter:

# vi /etc/my.cnf

Find a line that reads "log_bin" and remove or comment it as follows:

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

You also need to remove or comment following lines:

#expire_logs_days = 10

#max_binlog_size = 100M

Close and save the file. Finally, restart mysql server:

# service mysql restart

Answer 5

You can see it like this, log_bin should not accept parameters, either you put:

log_bin

or nothing at all, as it is a boolean parameter.

However, if a value is set, logs are enabled and the value interpreted as the binlog basename.

Log options have a history of not being too intuitive, and changing its format from version to version (I am looking at you, slow log).

Edit: In recent versions, the binary log is now enabled by default. To disable it, one has to specify: --skip-log-bin or --disable-log-bin on command line (or the same parameters on the option file). log-bin keeps working to explicitly enable the binary log as before.

Answer 6

As I know after updating a package, the config file should be the old one, so after update you have the same config files. Otherwise you can create a second config file in path $MYSQL_HOME/my.cnf. This one is for server specific options and as I know will overwrite the settings in global /etc/my.cnf