How to turn off binary log in mysql

Two methods to turn off the binary log: 1. Open the "my.ini" configuration file, find and delete the "log-bin" option, and restart the MySQL service after deleting it, so that the binary log can be turned off permanently. 2. Log in to the MySQL server and execute the "SET SQL_LOG_BIN=0;" command to temporarily close the binary log.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The binary log (Binary Log), also called the change log (Update Log), is a very important log in MySQL. It is mainly used to record changes in the database, that is, DDL and DML statements of SQL statements, and does not include data record query operations.

If the MySQL database stops unexpectedly, you can use the binary log file to view what operations the user has performed and what modifications have been made to the database server file, and then restore the database server based on the records in the binary log file.

Mysql method to close binary log

Method 1: Permanently close binary log

In In MySQL, open the my.ini configuration file, find and delete the log-bin option, and restart the MySQL service after deleting it,

log-bin=C:log\ Mylog means generating a binary log file named mylog in the C:log folder.

After restarting the MySQL service, you can see the mylog.000001 file and mylog.index file in the C:log folder. .

You can check whether the binary log is enabled by using the following command:

SHOW VARIABLES LIKE 'log_bin';

As can be seen from the results, the binary log is closed.

Method 2: Temporarily turn off the binary log

After deleting the log-bin option, you can stop the binary log function. If you need to start this function again, you need to add the log_bin option again. . Since this is troublesome, MySQL provides a statement to temporarily stop the binary log function.

If users do not want certain SQL statements they execute to be recorded in the binary log, they can pause the binary log function before executing these SQL statements.

Log in to the MySQL server and use the SET statement to pause/enable the binary log function. The command is as follows:

SET SQL_LOG_BIN=0/1;

In the above command, 0 means to pause the binary log function, and 1 means to enable the binary log function.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to turn off binary log in mysql. For more information, please follow other related articles on the PHP Chinese website!