Mariadb Binlog

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

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

• Canonical references on mariadb.com/docs:
  • https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog
  • https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog/mariadb-binlog-options
  • https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog/annotate_rows_log_event
  • https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/flashback

This page is: Copyright © 2026 MariaDB. All rights reserved.