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:

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

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Vivek Agarwal

Hello Sveta, Thanks for the above information but the confusion of re-applying the binlog events incase of failure is not clear. What options should we use to get the statements in uncommented format so that it can be re-applied.

0
Sveta Smirnova

There is no such an option. If you need to re-apply events you should not use –base64-output=DECODE-ROWS: binary events will be applied. SQL events exist for information purpose only. For example, when you are trying to find which statement caused replication failure.

0
jimmi1prajapati

Loved the article and want to suggest a tool for base64 decode http://codebeautify.org/base64-decode

0
Chetan Chaphekar

Very nice article. Use very good online tool OnlineUtility,in to convert string to Base64 format.

0
Mukesh P

Hello Sveta, I am stuck with a issue where my binary logs from AWS RDS mariadb 10.0.35 are grouped together. To elaborate more, all the # at ‘offset number’ are grouped one after another, all the event date time rows are grouped together, all SQL statements are grouped, etc. I used mysqlbinlog to read a particular event
eg:- “mysqlbinlog mysql-bin-changelog.640132 –start-position=9319572 –stop-position=9321524 –base64-output=DECODE-ROWS -v”
but it returns empty value within the delimiter section as given below:

DELIMITER /*!*/;
# at 9319572
# at 9320552
DELIMITER ;
# End of log file

On checking mysql docs, I found this is a property of binlog event grouping. https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

How can this be fixed. How should I know which position affects which rows. I tried converting them to different format like txt .log but nothing helped.(mysqlbinlog Ver 3.3 for Linux at x86_64)

0
Back to Blog