Why base64-output=DECODE-ROWS does not print row events in MySQL binary logs

Lately, I saw many cases when users specified the option --base64-output=DECODE-ROWS  to print out a statement representation of row events in MySQL binary logs just to get nothing. Reason for this is obvious: option --base64-output=DECODE-ROWS  does not convert row events into its string representation, this is the job of the option -- verbose. But why users mix these two options so often? This blog post is the result of my investigations.

MySQL binary logs

There are already two great blog posts about printing row events on the Percona blog: “Debugging problems with row based replication” by Justin Swanhart and “Identifying useful info from MySQL row-based binary logs” by Alok Pathak.

Both authors run mysqlbinlog  with options  –base64-output=decode-rows -vv  and demonstrate how a combination of them can produce human-readable output of row events. However, one thing which is not clear yet is what the differences are between these options. I want to underline the differences in this post.

Let’s check the user manual first.

–base64-output=value

This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case sensitive):

    AUTO (“automatic”) or UNSPEC (“unspecified”) displays BINLOG statements automatically when necessary (that is, for format description events and row events). If no –base64-output option is given, the effect is the same as –base64-output=AUTO.
    Note

    Automatic BINLOG display is the only safe behavior if you intend to use the output of mysqlbinlog to re-execute binary log file contents. The other option values are intended only for debugging or testing purposes because they may produce output that does not include all events in executable form.

    NEVER causes BINLOG statements not to be displayed. mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.

    DECODE-ROWS specifies to mysqlbinlog that you intend for row events to be decoded and displayed as commented SQL statements by also specifying the –verbose option. Like NEVER, DECODE-ROWS suppresses display of BINLOG statements, but unlike NEVER, it does not exit with an error if a row event is found.

For examples that show the effect of –base64-output and –verbose on row event output, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.

Literally --base64-output=DECODE-ROWS  just suppresses BINLOG  statement and does not print anything.

To test its effect I run command

on an InnoDB table while binary log uses ROW format. As expected if I specify no option I will receive unreadable output:

The INSERT  is here:

But this string is not for humans.

What will happen if I add option --base64-output=DECODE-ROWS ?

Row event was just suppressed!

Lets now check option verbose:

–verbose, -v

Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata.

For examples that show the effect of –base64-output and –verbose on row event output, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.

Surprisingly --base64-output=DECODE-ROWS  is not needed!:

INSERT statement successfully restored as: