0

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?

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

0

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

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.

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.