Disable binary logging in MySQL/8 for Windows

Question

I want to disable binary logging in order to diagnose some uninteresting issue. I've never used replication anyway—it's enabled only because it's the default in MySQL/8. Google shows like 20 different ways to do it and none seem to work in my set up.

The official manual says:

To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup.

It runs as Windows service, installed with the official MySQL Installer application, so I used regedit (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL80\ImagePath), but either flags, in any location, either standalone or with =1, prevent the service from starting yet log file doesn't include any error message.

I've also read you can have this in defaults file:

[mysqld]
skip-log-bin

Server starts, bug binary logging remains enabled.

Setting an MYSQLD_OPTS environment variable with --skip-log-bin=1 is ignored (perhaps it's exclusive to some Linux distro).

How do you disable binary logging in MySQL/8 on Windows?

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL80\ImagePath

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

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini:

[client]
port=3306

[mysql]
no-beep

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="FOO.log"
slow-query-log=1
slow_query_log_file="FOO-slow.log"
long_query_time=10
# One of my failed attempts:
skip-log-bin

Will B. · Accepted Answer · 2020-07-15 14:49:30Z

It appears that the issue is related to the order of operations.

If the --skip-log-bin or --disable-log-bin option is specified at startup, binary logging is disabled, with the log_bin system variable set to OFF. If either of these options is specified and --log-bin is also specified, the option specified later takes precedence. [sic]

Configuration Options

By default MySQL sets a configuration value of log-bin="FOO.bin", you would need to ensure that skip-log-bin is declared BELOW or AFTER the log-bin configuration option.

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]

#...

log-bin="FOO.bin"
skip-log-bin

After saving the configuration file, restart the MySQL service.

SELECT @@GLOBAL.log_bin, @@GLOBAL.version, @@GLOBAL.version_comment, @@GLOBAL.version_compile_os;

Result

+------------------+------------------+------------------------------+-----------------------------+
| @@GLOBAL.log_bin | @@GLOBAL.version | @@GLOBAL.version_comment     | @@GLOBAL.version_compile_os |
+------------------+------------------+------------------------------+-----------------------------+
|                0 | 8.0.21           | MySQL Community Server - GPL | Win64                       |
+------------------+------------------+------------------------------+-----------------------------+
1 row in set (0.00 sec)

Testing with skip-log-bin being declared prior to or above log-bin, even when using log-bin=0 or log-bin=OFF resulted in the log-bin declaration taking precedence - converting the specified log-bin value to a string.
Removing the log-bin declaration and declaring skip-log-bin anywhere, resulted in log_bin as OFF

Environment Variables

The environment variable MYSQLD_OPTS is specific to systemd (Linux) environments. [sic] [sic]

There does not appear to be an equivalent environment variable for Windows. [sic] Which I believe do not apply when running MySQL as a Windows service.

Adrian Mole · Accepted Answer · 2020-10-02 19:35:37Z

3

It works for me after I've declared disable_log_bin and commented the log-bin in my.cnf.

edited Oct 2, 2020 at 19:35

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered Oct 2, 2020 at 16:23

user223628

313 bronze badges

1 Comment

Jason Etheridge Over a year ago

It seems disable_log_bin is a synonym of skip_log_bin. You can use either to disable binary logs (ref).

Collectives™ on Stack Overflow

Disable binary logging in MySQL/8 for Windows

2 Answers 2

Configuration Options

Environment Variables

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Configuration Options

Environment Variables

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related