SoFunction
Updated on 2025-04-12

The problem of not generating binlog logs in MySQL is solved

To make the operations of a table in MySQL not generate binlog logs, you can do it by:

1. Temporary session-level binlog shutdown (for specific operations)

When you need to operate the table,Temporarily close the binlog record of the current session, restore after the operation is completed. This method is suitable for single or small operation scenarios:

SET sql_log_bin = 0;  -- Close the current sessionbinlogRecord
-- Perform operations on the target table（likeINSERT/UPDATE/DELETE）
INSERT INTO your_table (...) VALUES (...);
SET sql_log_bin = 1;  -- recoverbinlogRecord

Notice: This method only affects the subsequent operations of the current session and requires manual control, which is suitable for temporary needs.

2. Filtering rules through copy (master-slave scenario)

If the target table does not need to participate in master-slave replication, you can add it in the MySQL configuration fileCopy filtering rules, but be aware that this only affects the synchronization of the slave library, and the main library will still record binlog:

[mysqld]
replicate-ignore-table=your_db.your_table  -- Ignore copying of this table

This method is suitable for synchronous exclusion of specific tables in master-slave schema, but the binlog of the master library still records the operation

3. Adjust the binlog format (applicable for some scenarios)

Willbinlog_formatSet asROWorMIXED, and combine specific statements to implement some operations without recording binlog:

​UseINSERT DELAYEDorREPLACEStatement (some operations in ROW format are not recorded):

SET GLOBAL binlog_format = 'ROW';  -- RevisebinlogFormat
INSERT DELAYED INTO your_table (...) VALUES (...);

  • ByLOAD DATA INFILEImport data (no specific row changes are recorded in ROW format)

limitation: This method is only effective for specific statements and cannot override all operation types.

4. Disable binlog globally (not recommended)

If the environment in which the table is located does not require binlog (such as the test environment), you can close binlog globally:

  • Modify MySQL configuration file:
[mysqld]
skip-log-bin  -- Disabledbinlog
  • Restart MySQL service

Risk warning: This method will affect the binlog records of all tables, which may cause the data recovery and master-slave replication functions to be invalid. Use it with caution in the production environment.

Summary of suggestions

  • ​Prefer session-level temporary closure​ (Method 1), flexible and controllable risks.
  • If you need to exclude a table for a long time, you can combine itCopy filtering rules​(Method 2) and business logic design.
  • Avoid disabling binlog directly globally unless there is no data recovery or replication requirement explicitly.

Note: MySQL natively does not support table-level binlog filtering. The above solutions need to weigh the pros and cons in combination with business scenarios. For more granular control, you can consider middleware or custom audit plug-in implementation.

This is the article about making the operations of a table in MySQL not generating binlog logs. For more information about making the operations of a table in MySQL not generating binlog logs, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!