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.