Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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... –  David Rabinowitz Dec 25 '11 at 15:05
    
Easiest way off the top of my head...setup a second slave running MySQL 5.0 –  RolandoMySQLDBA Dec 25 '11 at 18:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.