How to Disable Binary Logs in MySQL

Summary: in this tutorial, you will learn how to disable binary logs using the skip-log-bin option in the MySQL configuration file.

First, open the terminal and connect to the MySQL server:

myql -u root -p

Second, check if the binary logging is enabled:

show global variables like 'log_bin';Code language: PHP (php)

Output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

The log_bin is ON, meaning that the binary logging is enabled.

Third, exit the mysql client program:

exitCode language: PHP (php)

Fourth, edit the MySQL configuration file (my.cnf) using a text editor for example nano, and place the skip-log-bin option in the [mysqld] section:

[mysqld]
# ...
# disable binary log
skip-log-binCode language: PHP (php)

Fifth, save the my.cnf file and restart the MySQL server:

sudo systemctl restart mysql.serviceCode language: CSS (css)

Note that you need to replace the mysql.service with the actual MySQL service name in your system.

Sixth, log in to the MySQL server:

mysql -u root -p

Seventh, show the variable log_bin:

show global variables like 'log_bin';Code language: PHP (php)

Output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The log_bin variable is OFF, meaning that the binary logging is disabled.

If you attempt to display the binary logs, MySQL will issue an error:

show binary logs;

Error:

ERROR 1381 (HY000): You are not using binary logging

Summary

• Use the skip-log-bin option in the MySQL configuration file to disable the binary logs in MySQL; restart MySQL to apply the change.