In MySQL, what is the binary log, and how do the ROW, STATEMENT and MIXED binlog formats differ?
answer
- logical record of committed changes
- read by replicas and recovery tools
- SQL text versus changed row images
- MIXED switches on unsafe statements
- 8.4 default, and a deprecated variable
basics
~20 sMySQL's binary log records committed data and schema changes for replicas and point-in-time recovery. ROW, the 8.4 default, logs changed row images; STATEMENT logs the SQL text; MIXED logs statements but switches to rows for unsafe ones.
solid answer
~50 sThe binary log is a series of numbered files (`binlog.000001` and on) that the server writes as transactions commit. It is separate from InnoDB's redo log, which repairs pages after a crash: replicas pull the binary log to repeat the changes, and `mysqlbinlog` replays it for point-in-time recovery. In MySQL 8.4 it is on by default with `binlog_format=ROW`, so each event carries the images of every changed row and the replica reaches the same result even for nondeterministic statements, at the price of a much bigger log for mass updates. `STATEMENT` logs the SQL text and is compact, but an `UPDATE ... LIMIT` without `ORDER BY` or a call to `UUID()` can change different rows on the replica. `MIXED` logs statements and switches to row events for unsafe ones. DDL is logged as a statement in every format. `binlog_format` is deprecated, so new setups should stay on ROW.
code
sql · 3 linesSELECT @@log_bin, @@binlog_format, @@binlog_row_image;
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;go deeper
Recall that the binary log feeds replicas and point-in-time recovery, that ROW is the default, and one concrete reason STATEMENT can diverge, such as LIMIT without ORDER BY.
Explain how MIXED decides between statement and row events, why READ COMMITTED forces row logging, and why DDL is logged as statements even under ROW.
Show you can judge the operational cost of ROW: binlog growth from mass updates, replica apply time, and decoding row events with mysqlbinlog during an incident.
Take a position on retiring STATEMENT and MIXED across a fleet now that binlog_format is deprecated, and what batch jobs must change before that happens.
## What the binary log is MySQL's **binary log** (binlog) is a server-level, logical record of the changes the server committed: inserts, updates and deletes, plus schema changes such as `CREATE TABLE` and `ALTER TABLE`. It is written as a sequence of numbered files, `binlog.000001`, `binlog.000002` and so on, with an index file listing them. Two consumers depend on it: - **Replication.** A replica's receiver thread connects to the source, copies binlog events into its own **relay log**, and an applier repeats them. - **Point-in-time recovery.** After restoring a backup, `mysqlbinlog` turns the binlog files written since that backup into statements you can replay up to a chosen moment. The binlog is not InnoDB's **redo log**. The redo log is a physical, engine-level record that repairs data pages after a crash; the binlog lives above the storage engines and describes changes logically, so a replica can apply them to its own data files. In MySQL 8.4 binary logging is **on by default** (`log_bin` is `ON`) and is turned off only by starting the server with `--skip-log-bin` or `--disable-log-bin`. Old files are removed automatically after `binlog_expire_logs_seconds`, which defaults to 2592000 seconds, 30 days. ## The three formats The `binlog_format` system variable decides how a data change is written: | Format | What an event holds | Strength | Weakness | |---|---|---|---| | `ROW` (default) | before and after images of each changed row | the replica reaches the same rows whatever the statement did | a mass update logs every row it touched | | `STATEMENT` | the SQL text that ran | compact, readable | nondeterministic statements can diverge | | `MIXED` | statements, switching to rows when a statement is unsafe | compact for safe statements | two behaviours to reason about | Whatever the format, **DDL is logged as a statement**: under `ROW`, `CREATE TABLE`, `ALTER TABLE` and `DROP TABLE` still appear as SQL text. ## Why statement-based logging can diverge With `STATEMENT`, the replica re-executes the same SQL against its own copy of the data, and some SQL does not give the same answer twice. MySQL marks such statements **unsafe** and warns *Statement may not be safe to log in statement format*. Examples the manual lists: 1. `UPDATE`, `DELETE` or `INSERT ... SELECT` with `LIMIT` and no `ORDER BY`: which rows qualify first is not defined. 2. Calls to `UUID()` and to loadable functions. 3. `INSERT ... ON DUPLICATE KEY UPDATE` on a table with more than one primary or unique key, because the order in which keys are checked decides the row that gets updated. With `MIXED`, the server logs statements but switches to row events for these unsafe cases, so the compact form is kept where it is safe. Some combinations force the choice: with InnoDB at `READ COMMITTED` only row-based logging works, and under `MIXED` the server switches to rows on its own. ## What ROW costs and how to read it Row-based logging trades size for determinism. One `UPDATE orders SET status = 'archived' WHERE created_at < '2025-01-01'` that touches two million rows writes two million row images, which grows the binlog, the network traffic to replicas and their apply work. The `binlog_row_image` variable (default `full`) controls whether every column is logged or only the ones needed. Row events are stored in binary form. To read them, decode them with `mysqlbinlog --base64-output=DECODE-ROWS --verbose`, which prints each row change as a commented pseudo-SQL statement instead of an opaque `BINLOG` block. Checking the current settings takes one query: ```sql SELECT @@log_bin, @@binlog_format, @@binlog_row_image; ``` ## Where MySQL 8.4 stands - **ROW is the default and the direction of travel.** `binlog_format` is deprecated: the manual says changing the logging format was deprecated in MySQL 8.0 and that only row-based logging should be used for new replication setups. - **Group Replication requires ROW.** It needs row images to detect conflicting writes, and adds `REQUIRE_ROW_FORMAT` to its own channels. - **The format is set per server.** Changing it on the source does not change a replica's own format, and a replica cannot turn received row events back into statements, so it must use ROW or MIXED whenever its source does. A junior engineer who remembers three things is in good shape: the binlog is the logical change stream replicas and recovery read, ROW is the safe default, and STATEMENT breaks on nondeterministic SQL.
- Why can a single UPDATE make a MySQL replica fall behind under ROW format but not under STATEMENT?Under `ROW` the source logs one row image per changed row, so an `UPDATE` touching millions of rows becomes millions of row events the replica must receive and apply, each locating its row by key. Under `STATEMENT` the replica runs the same single statement, which costs about what it cost on the source. Batching big updates into smaller transactions keeps row-based replicas from stalling.
- With binlog_format=MIXED, when does MySQL write a row event instead of the statement?When it cannot guarantee that the statement gives the same result on the replica: calls to `UUID()` or loadable functions, `UPDATE` or `DELETE` with `LIMIT` but no `ORDER BY`, and similar unsafe statements. It also logs rows whenever the InnoDB isolation level is `READ COMMITTED`, because only row-based logging is supported there. Everything else stays in statement form.
- The binary log is just InnoDB's redo log under another name.
- STATEMENT format is always safe because the replica runs identical SQL.
- MIXED format writes every change twice, as a statement and as rows.
- Under ROW format, ALTER TABLE cannot replicate because DDL changes no rows.
- Binary logging is off by default in MySQL 8.4 and must be enabled first.
- Changing binlog_format on the source makes every replica switch to match.
Assumes MySQL 8.4 LTS: binary logging is on by default in ROW format, and binlog_format is deprecated, so ROW is the only format to plan around.