--- name: mariadb-binlog description: "MariaDB-specific behavior of the `mariadb-binlog` utility — that decoded row events (`-v`/`-vv`) are commented pseudo-SQL riding alongside the still-executable base64 `BINLOG` blob (not a replacement for it), that `ANNOTATE_ROWS` original-SQL comments are a MariaDB-only event printed by default, that `--flashback` generates MariaDB-specific reverse/undo SQL for DML only, `--disable-log-bin` to avoid re-logging on replay, and that replay means piping into the `mariadb` client, never running the tool itself. Use when writing, generating, or reviewing commands that read, filter, or replay MariaDB binary logs." --- # mariadb-binlog *Last updated: 2026-07-20* `mariadb-binlog` reads binary log (or relay log) files and turns their events into SQL/text — the tool behind replication debugging, crash recovery, and point-in-time restores. The binary is `mariadb-binlog`; `mysqlbinlog` is a compatibility symlink to the same executable. This skill covers its MariaDB-specific defaults and the traps that bite generated commands. > **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 features carry one. ## What LLMs Often Miss | If the agent writes / suggests… | …prefer the MariaDB form | |---|---| | Pipes output into `mysql` to replay a binlog | Pipe into the **`mariadb` client**: `mariadb-binlog binlog.000001 \| mariadb -u root -p`. `mariadb-binlog` only decodes/filters — it never executes anything itself | | Treats the `-v`/`-vv` decoded row output (`### INSERT INTO … SET …`) as the statement that gets executed on replay | Those `###`-prefixed lines are **comments**, generated by `--verbose` purely for human readability. The actually-replayed statement is still the base64-encoded `BINLOG '…'` blob printed alongside them (default `--base64-output=auto`) — don't strip the blob thinking the pseudo-SQL replaces it | | Uses `--base64-output=decode-rows` to get replayable SQL from row events | `decode-rows` **omits the raw `BINLOG` blob entirely** — output is debugging-only pseudo-SQL, not re-executable. For an actually replayable stream, leave `--base64-output` at its default (`auto`) | | Expects the original query text for a row-based event to be unavailable, or to need special external tooling to recover | MariaDB writes a MariaDB-specific `ANNOTATE_ROWS_EVENT` carrying the original SQL before each row event (governed by session var `binlog_annotate_row_events`, default **ON**), and `mariadb-binlog` **prints it by default**. Suppress with `--skip-annotate-row-events` if the noise isn't wanted | | Looks for a separate "flashback"/undo tool, or assumes MariaDB has none | `mariadb-binlog --flashback` (`-B`) is MariaDB-specific: it rewrites row events into their inverse (`INSERT`↔`DELETE` swapped, `UPDATE` before/after images swapped) to build an undo script. **DML only** (`INSERT`/`DELETE`/`UPDATE` — not DDL), requires `binlog_row_image=FULL`, and is **incompatible with `--raw`**. Don't combine it with `-v`/`-vv` — the docs explicitly warn this can corrupt the flashback output | | Replays output back into the same server (or uses `--to-last-log`) without guarding against re-logging | Add `-D`/`--disable-log-bin` — it emits `SET SQL_LOG_BIN=0` in the output so the replayed statements aren't written back to the binary log (avoids an infinite loop, and duplicate-statement risk after crash recovery). Requires the `SUPER` privilege on the importing connection | | Assumes `-d`/`--database` reliably filters events when reading from a remote server, or that it behaves the same for statement- vs row-based logging | `--database` is documented **local-log-only**, and is **ignored entirely in `--raw` mode**. Behavior also differs by format: for statement-based logging it matches the *default* database (`USE`), but for row-based logging it matches any table update in that database regardless of the current default database | | Uses `--position=N` to seek into a binlog | That flag was **removed** — use `--start-position`/`--stop-position` instead. Both also accept a GTID, or *(since 10.8)* a comma-separated multi-domain GTID list, instead of a byte offset | | Assumes reading from a running server needs no special flag | Local files are read by path with no extra flags; pulling from a live server needs `-R`/`--read-from-remote-server` plus `--host`/`--port`/`--user`/`--password` — every other connection flag is silently ignored unless `-R` is given. `--to-last-log`/`-t` and `--stop-never` both *require* `-R` | ## Reading Local Files vs. a Remote Server ```bash # Local file(s), by path — no server connection involved: mariadb-binlog mariadb-bin.000152 # From a running server (requires -R): mariadb-binlog -R --host=db1 --user=root --password mariadb-bin.000152 # Save the raw binary log data itself (not decoded text) — requires -R: mariadb-binlog -R --raw --result-file=/backup/ mariadb-bin.000152 ``` `--raw` requires `-R`, cannot be combined with `--flashback`, and disables `--database`, `--stop-datetime`, and `--stop-position` filtering (all ignored in raw mode). ## Position, Time, and GTID Filtering - `--start-position`/`-j` and `--stop-position` accept a positive integer byte offset **or**, *(since 10.8)*, a GTID (or comma-separated multi-domain GTID list). The default start position is `4` — right after the binlog file's 4-byte magic number, i.e. the first real event. - `--start-datetime`/`--stop-datetime` filter by wall-clock time instead of position — useful for point-in-time recovery when you don't know the exact offset. - `--do-server-ids`/`--ignore-server-ids` and, *(since 10.9)*, `--do-domain-ids`/`--ignore-domain-ids` filter events by originating server ID or GTID domain ID (whitelist/blacklist pairs — the "do" and "ignore" forms of each pair are mutually exclusive). - `--gtid-strict-mode` (default **ON**, disable with `--skip-gtid-strict-mode`) *(since 10.8)* enforces that GTID sequence numbers within a domain are monotonically increasing and that start < stop. ```bash mariadb-binlog --start-datetime="2026-07-20 09:00:00" \ --stop-datetime="2026-07-20 09:15:00" \ mariadb-bin.000152 > incident-window.sql ``` ## Row Event Decoding (`--base64-output`, `--verbose`) Row-based events are logged as raw row images, not SQL text. By default (`--base64-output=auto`), `mariadb-binlog` prints them as executable base64-encoded `BINLOG '...'` statements — this is what actually gets replayed. - `-v`/`--verbose` adds commented pseudo-SQL (`### INSERT INTO … SET … / ### UPDATE … WHERE …`) below each `BINLOG` blob, reconstructed from the row image, for human review. `-vv` also adds column-type comments. These `###` lines are never executed — the `BINLOG` statement above them is. - `--base64-output=decode-rows` (combined with `-v`) drops the raw `BINLOG` blob and prints only the decoded pseudo-SQL — good for skimming a large binlog without wading through base64, but the result is no longer replayable. - `--base64-output=never` refuses row-based events outright (errors out if one is found) — only useful for binlogs you know are purely statement-based. ## Annotate Rows: Original SQL for Row Events (MariaDB-Specific) MariaDB's `ANNOTATE_ROWS_EVENT` is not a standard binlog event type — it's a MariaDB addition that records the original SQL statement immediately before the row events it produced. It's controlled by two server-side settings and one client-side flag: - `binlog_annotate_row_events` (session var, default **ON**) — whether the primary writes these events at all (only meaningful under row/mixed logging). - `replicate_annotate_row_events` — whether a replica re-emits received `Annotate_rows` events into its own binary log (only useful with `log_slave_updates`). - `--skip-annotate-row-events` on `mariadb-binlog` — by default the tool **prints** `Annotate_rows` events if the log contains them; this flag suppresses them. ```bash mariadb-binlog -vv mariadb-bin.000152 | grep -A2 "Annotate_rows" ``` ## Flashback: Generating Undo SQL (MariaDB-Specific) `--flashback`/`-B` is a MariaDB-only capability with no upstream equivalent: it walks row events and emits their logical inverse — `INSERT` becomes `DELETE`, `DELETE` becomes `INSERT`, and `UPDATE` swaps its before/after row images — building a script that undoes the changes. Constraints to respect: - **DML only** — `INSERT`/`DELETE`/`UPDATE`. DDL (`DROP`/`TRUNCATE`/`ALTER`) is not reversible this way. - Requires the source binlog to have been written with `binlog_row_image=FULL` (and `binlog_format=ROW`, which the `mariadbd` `--flashback` startup option sets for you). - **Incompatible with `--raw`** — the tool errors out if both are given. - **Don't combine with `-v`/`-vv`** — the docs explicitly call out that verbose output can interfere with flashback's own review output. - Flashback events are buffered in memory before being emitted in reverse order, so a very large flashback range needs commensurate RAM. ```bash # Find the position/time to revert to, then generate the undo script: mariadb-binlog -d test -T mytable \ --start-datetime="2026-07-20 09:05:00" \ --flashback mariadb-bin.000152 > flashback.sql # Review, then apply: mariadb test < flashback.sql ``` ## Replaying Output Safely ```bash # Review before applying — never pipe straight from an unfamiliar binlog into a live server: mariadb-binlog -r review.sql mariadb-bin.000152 # ...edit review.sql to drop anything unwanted (e.g. an accidental DROP DATABASE)... mariadb -u root -p --binary-mode < review.sql # Multiple files in one connection (needed if any file has CREATE TEMPORARY TABLE # referenced by a later file — separate connections lose the temp table): mariadb-binlog mariadb-bin.000001 mariadb-bin.000002 | mariadb -u root -p --binary-mode # Replaying onto the log's own server (crash recovery) — avoid re-logging the replayed statements: mariadb-binlog --disable-log-bin mariadb-bin.000152 | mariadb -u root -p ``` `--disable-log-bin`/`-D` needs the `SUPER` privilege on the connection that runs the resulting SQL, and is especially important with `--to-last-log`/`--stop-never` (both require `-R`) when streaming straight back into the source server, to avoid an endless replay loop. ## See Also - Canonical references on `mariadb.com/docs`: - - - - _This page is: Copyright © 2026 MariaDB. All rights reserved._