Disable Binary Logs in MySQL 8.0

Question

How can I disable binary logging on MySQL 8.0?

I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in the my.cnf and restarted the database.

But with MySQL 8.0 I have tried various settings, but everytime I log onto the database, and run:

SELECT @@global.log_bin;

and it keeps returning 1

I have tried:

log_bin = 0
log_bin = OFF
#log_bin = 1

Is it possible to disable to the binary logs?

Answer 1

In addition to specifying the corresponding command line argument, you can also add an option to my.cnf:

1. Edit my.cnf and add skip-log-bin in the [mysqld] section.

2. Restart mysqld

3. Enjoy your lack of binlogs

Note that this will remain in effect until you remove the option from the configuration file.

Answer 2

Use set-environment command:

systemctl set-environment MYSQLD_OPTS="--skip-log-bin=1"

Next, reload configuration files and restarted the server using the following commands:

systemctl daemon-reload
systemctl restart mysqld

Next check the value of log_bin global variable:

mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('log_bin');
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| log_bin       | OFF            |
+---------------+----------------+
1 row in set (0.00 sec)

Answer 3

From the MySQL 8.0 Reference manual, section 5.4.4:

The following discussion describes some of the server options and variables that affect the operation of binary logging. For a complete list, see Section 17.1.6.4, “Binary Logging Options and Variables”.

Binary logging is enabled by default (the log_bin system variable is set to ON). The exception is if you use mysqld to initialize the data directory manually by invoking it with the --initialize or --initialize-insecure option, when binary logging is disabled by default, but can be enabled by specifying the --log-bin option.

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.

Answer 4

There is some conflicting info floating around. Apparently, MySQL 8.0+ enables binary logs by default so a lot of server admins are discovering this in the middle of the night due to crashed servers!

Generally speaking MySQL uses dashes - for inline command flags, and uses underscores _ for permanent config option names... this is common for many apps, actually. However, for whatever reason this is becoming less true, and various formats are now acceptable (more below)...

If you want to disable binary logs for your current session only, then use either:

• --disable-log-bin
• --skip-log-bin

However if you want a permanent solution, edit your my.cnf configuration file and add either:

• disable-log-bin
• skip-log-bin
• disable_log_bin
• skip_log_bin

I have tested all of these variations successfully in our SlickStack boilerplate. (Whichever one you choose is fine, just be sure not to include multiple versions.)

Note that the syntax shared by OP does NOT work: log_bin = OFF

You may be wondering why MySQL has made things so complicated, but answering that is above my pay grade and I'd venture to guess, they just want to confuse us as much as possible ;)

Answer 5

Some additional information that might help windows users in particular.

TLDR Summary - Check you have the correct config file, add one of the disable/skip options and comment out/remove any reference to setting a bin log filename.

The service entry had a none standard defaults file specified, in my case

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Which did not appear on the mist of options when I ran mysql.exe --help

Default options are read from the following files in the given order: C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf

I tried all of these

• disable-log-bin
• skip-log-bin
• disable_log_bin
• skip_log_bin

And none worked, I knew I was restarting the correct service because if I stopped it I could not connect. Turns out I had a filename specified

log-bin="hostname-bin"

Commenting that out means the config options like disable-log-bin now work.