mariadb-binlog

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

SKILL.md

GitHub

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

# 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.
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.
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.
# 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

# 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

<sub>This page is: Copyright © 2026 MariaDB. All rights reserved.</sub>