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

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

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.