When Does MySQL Skip Writing to the Binlog? A Deep Dive into ROW‑Mode Logic and Controls

Background

MySQL can write slow‑query logs to a file or to the mysql.slow_log table, controlled by log_output. The investigation examined whether entries in mysql.slow_log are replicated, and uncovered several mechanisms that prevent operations from being written to the binary log.

ROW‑format binlog decision logic

In ROW format the function binlog_log_row determines whether a row change is logged. It calls check_table_binlog_row_based; if true it selects the appropriate event class ( Write_rows_log_event, Update_rows_log_event, Delete_rows_log_event) and invokes the log function.

int binlog_log_row(TABLE *table, const uchar *before_record,
                    const uchar *after_record, Log_func *log_func) {
    bool error = false;
    THD *const thd = table->in_use;
    if (check_table_binlog_row_based(thd, table)) {
        if (likely(!(error = write_locked_table_maps(thd)))) {
            bool const has_trans = thd->lex->sql_command == SQLCOM_CREATE_TABLE ||
                                  table->file->has_transactions();
            error = (*log_func)(thd, table, has_trans, before_record, after_record);
        }
    }
    return error ? HA_ERR_RBR_LOGGING_FAILED : 0;
}

check_table_binlog_row_based

returns false when any of the following holds:

Current statement cannot be logged in ROW format (e.g., DDL).

The table’s cached_row_logging_check flag is false.

The thread’s OPTION_BIN_LOG flag is cleared.

The binary log is not open.

When cached_row_logging_check is false

The flag is computed once per table:

if (table->s->cached_row_logging_check == -1) {
    int const check = (table->s->tmp_table == NO_TMP_TABLE &&
                      !table->no_replicate &&
                      binlog_filter->db_ok(table->s->db.str));
    table->s->cached_row_logging_check = check;
}

It becomes false if any of the following is true:

The table is a temporary table ( tmp_table != NO_TMP_TABLE).

The database name does not satisfy replication filter rules ( --replicate-do-db / --replicate-ignore-db).

The table is marked no_replicate, set in open_table_from_share() based on table category or storage‑engine capabilities.

Tables with categories TABLE_CATEGORY_LOG, TABLE_CATEGORY_RPL_INFO, or TABLE_CATEGORY_GTID (e.g., mysql.general_log, mysql.slow_log, mysql.slave_relay_log_info, mysql.gtid_executed) are excluded. Storage engines lacking the flags HA_BINLOG_STMT_CAPABLE or HA_BINLOG_ROW_CAPABLE —currently only perfschema and temptable —also do not write to the binlog.

if ((share->table_category == TABLE_CATEGORY_LOG) ||
    (share->table_category == TABLE_CATEGORY_RPL_INFO) ||
    (share->table_category == TABLE_CATEGORY_GTID)) {
    outparam->no_replicate = true;
} else if (outparam->file) {
    const handler::Table_flags flags = outparam->file->ha_table_flags();
    outparam->no_replicate = !(flags & (HA_BINLOG_STMT_CAPABLE | HA_BINLOG_ROW_CAPABLE)) ||
                             (flags & HA_HAS_OWN_BINLOGGING);
} else {
    outparam->no_replicate = false;
}

When OPTION_BIN_LOG is false

The thread‑level bitmap option_bits contains OPTION_BIN_LOG. The flag is cleared in several situations:

Explicitly disabling the session binlog: SET SESSION sql_log_bin = 0;. The callback fix_sql_log_bin_after_update updates option_bits.

Running as a replica without log_replica_updates; the replica SQL thread clears the flag.

Temporarily suppressing binlogging with the RAII class Disable_binlog_guard.

static bool fix_sql_log_bin_after_update(sys_var *, THD *thd,
                                          enum_var_type type) {
    assert(type == OPT_SESSION);
    if (thd->variables.sql_log_bin)
        thd->variables.option_bits |= OPTION_BIN_LOG;
    else
        thd->variables.option_bits &= ~OPTION_BIN_LOG;
    return false;
}

class Disable_binlog_guard {
public:
    explicit Disable_binlog_guard(THD *thd)
        : m_thd(thd),
          m_binlog_disabled(thd->variables.option_bits & OPTION_BIN_LOG) {
        thd->variables.option_bits &= ~OPTION_BIN_LOG;
    }
    ~Disable_binlog_guard() {
        if (m_binlog_disabled) m_thd->variables.option_bits |= OPTION_BIN_LOG;
    }
private:
    THD *const m_thd;
    const bool m_binlog_disabled;
};

Typical code paths that instantiate this guard include server initialization, upgrade, and internal DDL/DML operations such as CREATE SERVER, INSTALL COMPONENT, and maintenance tasks.

When NO_WRITE_TO_BINLOG is true

The lexical flag thd->lex->no_write_to_binlog forces the current statement not to be logged. It is set for commands like SHUTDOWN, RESTART, RESET MASTER, RESET SLAVE, and for statements that explicitly specify NO_WRITE_TO_BINLOG or LOCAL (e.g., OPTIMIZE NO_WRITE_TO_BINLOG TABLE t1;).

OPTIMIZE NO_WRITE_TO_BINLOG TABLE t1;
ANALYZE LOCAL TABLE t1;
REPAIR NO_WRITE_TO_BINLOG TABLE t1;
FLUSH LOCAL PRIVILEGES;

Certain FLUSH commands never generate binlog entries, including FLUSH LOGS, FLUSH BINARY LOGS, and FLUSH TABLES WITH READ LOCK.

Summary

Internal operations such as instance initialization, upgrade, writes to mysql.slow_log, and updates to performance_schema tables typically do not produce binlog records. User‑initiated DML on regular tables (excluding the special categories listed above) is logged, and most DDL statements (e.g., TRUNCATE) are also logged. Operations on performance_schema tables require elevated privileges and, when allowed, usually skip binlogging.