10

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?

3
  • Those were in the [mysqld] section? – Rick James Jan 3 '19 at 18:14
  • That's right. Normally it has log_bin=filename. So I changed it to (one of) the above and restarted the database. Then tried the next one etc. – IGGt Jan 4 '19 at 9:00
  • I'm pretty sure log_bin_basename is where the name goes (since early in 5.6). And log_bin has always been ON/OFF. – Rick James Jan 4 '19 at 22:03
6

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.

2
  • This is correct. To do it via the config file, then add skip-log-bin/skip_log_bin. – toytoy Jul 20 '20 at 16:23
  • This appears to have worked for me. I used the syntax "skip-log-bin = true" as it looks like that file is name=value pairs. – davidgo Oct 5 '20 at 0:28
1

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.

1

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)
1
  • 2
    Did not work for me with Ubuntu 20.04 and MySQL 8.0.20 – BurninLeo Jul 10 '20 at 19:32
0

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 ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.