SQL NO_WRITE_TO_BINLOG

What is the SQL NO_WRITE_TO_BINLOG modifier?

A MySQL and MariaDB statement modifier that skips writing the statement to the server’s binary log, preventing it from being replicated.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NO_WRITE_TO_BINLOG: Supports: MySQL 4.0+, MariaDB 5.1+ Not supported: PostgreSQL, SQL Server, Oracle, SQLite

SQL NO_WRITE_TO_BINLOG Full Explanation

NO_WRITE_TO_BINLOG (synonym LOCAL) is a statement-level modifier available in MySQL and MariaDB. Placing it immediately after the leading keyword of certain administrative or maintenance statements tells the server not to record that statement in the binary log. Because replication relies on the binary log, replicas do not execute statements marked with NO_WRITE_TO_BINLOG. The modifier is often used for operations that must run only on the primary server, such as quick statistics gathering or table maintenance, and for commands that would otherwise bloat the binary log without adding value to replicas.Supported statements include (but are not limited to): FLUSH, OPTIMIZE TABLE, ANALYZE TABLE, REPAIR TABLE, CHECK TABLE, RESET MASTER, RESET SLAVE, and RESET REPLICA. DML statements like INSERT or UPDATE do not accept the modifier; to suppress logging for those you must set the session variable sql_log_bin to 0.NO_WRITE_TO_BINLOG affects only binary logging. It does not disable the general query log, slow query log, or error log, and it has no impact on the storage engine’s transactional behavior. The user executing the statement still needs the same privileges as when the modifier is not present.

SQL NO_WRITE_TO_BINLOG Syntax

<statement> NO_WRITE_TO_BINLOG ...;

SQL NO_WRITE_TO_BINLOG Parameters

Example Queries Using SQL NO_WRITE_TO_BINLOG

-- Flush all tables on the primary without replicating
FLUSH NO_WRITE_TO_BINLOG TABLES;

-- Optimize a large table locally to avoid replica lag
OPTIMIZE NO_WRITE_TO_BINLOG TABLE sales_data;

-- Analyze table statistics only on the primary
ANALYZE NO_WRITE_TO_BINLOG TABLE user_activity;

Expected Output Using SQL NO_WRITE_TO_BINLOG

The statement executes only on the current server
No corresponding event is written to the binary log, so replicas do not replay the operation
All local side-effects (table reorganization, updated statistics, etc
) occur normally

Use Cases with SQL NO_WRITE_TO_BINLOG

Running maintenance commands that are safe to perform independently on each replica
Preventing large maintenance operations from filling the binary log and network bandwidth
Quickly gathering statistics on the primary during peak traffic without delaying replicas
Resetting master or replica positions without propagating the reset downstream

Common Mistakes with SQL NO_WRITE_TO_BINLOG

Using the modifier with DML statements; most DML does not accept it
Assuming it disables all logging; it only skips the binary log
Forgetting that replicas will not receive the statement, leading to divergent metadata
Placing NO_WRITE_TO_BINLOG in the wrong position; it must come right after the statement keyword

First Introduced In

MySQL 4.0

Frequently Asked Questions

What is the difference between NO_WRITE_TO_BINLOG and LOCAL?

Both keywords are synonyms. Using either one prevents the statement from being written to the binary log.

Can I use NO_WRITE_TO_BINLOG with INSERT or UPDATE?

No. Standard DML commands do not accept the modifier. To suppress binary logging for DML, set the session variable `sql_log_bin` to 0.

Does the modifier require extra privileges?

No additional privileges are needed beyond those required for the base statement itself.

How can I confirm a statement was not logged?

Query `SHOW BINLOG EVENTS` or check the binary log on a replica. The modified statement will not appear.