What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0.

When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed those base64 statements:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111219 19:25:06 server id 395220  end_log_pos 107      Start: binlog v 4, server v 5.5.18-log created 111219 19:25:06
BINLOG '
ko/vTg/UBwYAZwAAAGsAAAAAAAQANS41LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111219 19:25:06 server id 370534  end_log_pos 171      Query   thread_id=387276        exec_time=73680 error_code=0

When I've checked the MySQL 5.0 bin-log files, I've noticed those statments were missing. My first guess was that this is a new feature in MySQL Replication. I know I should not use binlogs from MySQL 5.5 on MySQL 5.0. Therefore, the only way I can promise the rollback of the data is by running the queries as queries.

When I ran mysqlbinlog --base64-output=NEVER, I noticed that the base64 statements had disappeared. I would have expected them to be translated to plain text queries, but this was not the case.

Can someone put more light on what those base64 statements means?

Thanks,

Shahar.

From the MySQL Documentation


•--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.


■ ALWAYS displays BINLOG statements whenever possible. If the
--base64-output option is given without a value, the effect is the same as --base64-output=ALWAYS.


Note


Changes to replication in MySQL 5.6 make output generated by
this option unusable, so ALWAYS is deprecated as of MySQL 5.5.8 and
will be an invalid value in MySQL 5.6


■ 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.7.2, “mysqlbinlog Row Event
Display”.

CAVEAT

Please keep in mind that binary logs are not backward compatible.

In ServerFault. someone thought that a master's binary log was corrupt, not realizing that binary logs have different start points. I addressed by explaining the following:

An empty binary log's filesize is

• for MySQL 5.5, 107

• for MySQL 5.1, 106

• pre-MySQL 5.1, 98

As you already pointed out, Replication works from a 5.0 Master to a 5.5 Slave, not the other way around.

StackExchange Database Administrators Q#9591, answer score: 2