3

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.

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • thanks for your answer. How can we then have a rollback plan given that we want to move to mysql 5.5 (on a new server), but we need a rollback plan to go back to the old server which runs 5.0. We can't afford a full mysqldump and restore. Also, the binlog_format is defined as 'STATEMENT' yet we see the BINLOG rows. If it was not clear by now, I'm working with smintz... Commented Dec 25, 2011 at 15:05
  • Easiest way off the top of my head...setup a second slave running MySQL 5.0 Commented Dec 25, 2011 at 18:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.