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: “Justin Swanhart and “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:

Why do the bloggers mentioned above suggest to use --base64-output=DECODE-ROWS ? Lets try to use both options:

In this case row event was suppressed and statement is printed. Also, the resulting file cannot be used to re-apply events, because statements are commented out. This is very useful when binary log is big and you just need to investigate what it contains, not re-apply events.

This is not main purpose of this post, but you can also find information about columns metadata if specify option --verbose  twice:

Note, this is, again, the job of --verbose , not --base64-output=DECODE-ROWS

To conclude:

If you want to see statement representation of row events use option --verbose (-v)
If you want to see metadata of columns specify --verbose  twice: --verbose --verbose  or -vv
If you want to suppress output of row events specify the option --base64-output=DECODE-ROWS