Read and safely replay MariaDB binary logs with mysqlbinlog

mysqlbinlog turns a MariaDB binary log into readable SQL you can check before deciding whether to replay it. You will narrow the output to a time or position range and save a reviewable SQL stream. The installed client is MariaDB 10.11.14 from the mariadb-client package. Allow about fifteen minutes for inspection; replaying data needs a separate change window and a verified backup.

This guide uses mysqlbinlog because that is the name in the supplied manual. On this machine it is a symlink to mariadb-binlog. Both names invoke the same installed program.

1. Check the binary and its version

Start with ordinary, read-only checks. No elevated privileges are needed if you can read the log file and write to your working directory.

$ command -v mysqlbinlog
/usr/bin/mysqlbinlog
$ readlink -f /usr/bin/mysqlbinlog
/usr/bin/mariadb-binlog
$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1
$ mysqlbinlog --version
mariadb-binlog Ver 3.5 for debian-linux-gnu at x86_64

Checkpoint: confirm that your log path is a file you intend to inspect, not a destination you might overwrite. Binary logs can contain sensitive SQL, credentials in statements, and personal data. Treat any generated output as sensitive.

2. Print a local log without changing it

Give the command one or more local log files. It writes decoded text to standard output and leaves the binary log untouched.

$ mysqlbinlog /var/lib/mysql/mariadb-bin.000123 | less

The output includes event headers. A line beginning with # at shows the event's starting position, while end_log_pos identifies the position at which the next event starts. Statement-based events show SQL. Row-based events describe row changes and may include base64-encoded BINLOG statements instead of ordinary SQL.

Use the installed command's own help when checking options on another MariaDB release:

$ mysqlbinlog --help | sed -n '1,35p'

Do not assume that a connection option reads a local file. Options such as --host, --user and --port matter only with --read-from-remote-server.

3. Save a range for review

Redirect output to a new file when you need to search or edit it before replay. The position values come from the event headers or from the server's binlog status, so replace them with values from your incident or backup record.

$ mysqlbinlog \
    --start-position=123456 \
    --stop-position=234567 \
    /var/lib/mysql/mariadb-bin.000123 \
    > /tmp/binlog-review.sql
$ test -s /tmp/binlog-review.sql && echo 'review file is non-empty'
review file is non-empty

For a time window, use the documented date-time options instead:

$ mysqlbinlog \
    --start-datetime='2026-09-25 09:00:00' \
    --stop-datetime='2026-09-25 09:30:00' \
    /var/lib/mysql/mariadb-bin.000123 \
    > /tmp/binlog-window.sql
$ rg -n 'DROP|TRUNCATE|DELETE|ALTER' /tmp/binlog-window.sql

Inspect the file before using it. Shell redirection truncates an existing destination immediately, so choose a new name or use a temporary file followed by a deliberate rename. Remove an unwanted temporary file with rm -- /tmp/binlog-window.sql only after checking its path.

4. Decode row events for diagnosis

The default base64 mode, AUTO, is the safe mode when output might be replayed because it preserves events that need BINLOG statements. For human inspection, combine --base64-output=DECODE-ROWS with --verbose:

$ mysqlbinlog \
    --base64-output=DECODE-ROWS \
    --verbose \
    /var/lib/mysql/mariadb-bin.000123 \
    > /tmp/binlog-decoded.sql

The row changes shown in this mode are commented SQL intended to explain an event, not a safe substitute for the original executable stream. Do not use --base64-output=NEVER for a mixed or row-based log when you need complete replay: the command can stop when it encounters a row event that requires a BINLOG statement.

5. Filter by database with the logging format in mind

--database=NAME filters local logs, but it does not mean "all SQL that mentions this database". With statement-based logging, it follows the active USE database. With row-based logging, it selects row changes in tables belonging to the named database. Mixed logging and cross-database statements can therefore produce surprising results.

$ mysqlbinlog --database=appdb \
    --start-datetime='2026-09-25 09:00:00' \
    /var/lib/mysql/mariadb-bin.000123 \
    > /tmp/appdb-events.sql

Checkpoint: verify the server's logging format before trusting the filter:

$ mariadb -u ADMIN_USER -p -e "SHOW VARIABLES LIKE 'binlog_format';"

This command prompts for the password. Do not put a real password in the command line, shell history, process listing or this article's placeholders.

6. Read a remote log, then stop

Remote reading requires --read-from-remote-server and a MariaDB account permitted to read binary logs. The remote server must be running, and this mode does not read relay logs.

$ mysqlbinlog \
    --read-from-remote-server \
    --host=DB_HOST \
    --user=BINLOG_READER \
    --password \
    --raw \
    --result-file=/tmp/binlog-copy- \
    mariadb-bin.000123

--raw requires remote mode and saves raw binary data rather than SQL. The password option with no value prompts securely. The files written under /tmp are a copy, not an undo mechanism; delete them after checking the exact names and sensitivity of their contents.

7. Replay only after a deliberate review

Warning: Feeding binlog output to a database executes the recorded changes. It can alter or delete data, recreate schema changes, or repeat work already applied. Take a tested backup, confirm the target server, compare the event range with the recovery plan, and obtain the required change approval first. This is the point at which elevated database privileges may be required; sudo does not make a MariaDB account authorised.

$ mysqlbinlog \
    --start-position=123456 \
    --stop-position=234567 \
    /var/lib/mysql/mariadb-bin.000123 \
    > /tmp/replay-approved.sql
$ sed -n '1,80p' /tmp/replay-approved.sql
$ mariadb -u RECOVERY_USER -p --binary-mode < /tmp/replay-approved.sql

Use --disable-log-bin only when its consequences are part of the recovery plan. It adds SET sql_log_bin = 0 to the output and requires the MariaDB SUPER privilege on this installed version. That changes replication and audit behaviour, so do not add it as a generic fix for a replay error. If replay fails, stop, preserve the error and the output file, and restore from the approved backup or recovery procedure rather than blindly rerunning it.

Common traps and recovery

An empty filtered file can mean that the database, time range or position range matched no events. Re-run without the filter against a copy and compare headers.
Unreadable local logs usually need file access, not root. Check ls -l and the service account's permissions first. Use elevated access only under your host's approved procedure.
Do not use --stop-never casually. It waits for more remote data instead of stopping at the last log, so a terminal that appears hung may be doing exactly what was requested. Interrupt it with Ctrl-C and remove only the deliberately created output.
When a generated SQL file is wrong, keep the original binary log and discard the generated file. The binary log is the source; the SQL is a derived artefact.

Done means

The installed command and MariaDB client version were checked, including the mysqlbinlog symlink.
The source log was read without modifying it, and any output was written to a deliberately named file.
Time, position and database filters were matched to the logging format and recovery record.
Row-event decoding was kept separate from executable replay output.
Any replay was reviewed, authorised, backed up and directed at the confirmed target server.