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.

The mysql server online is of version 4.1, which doesn't support row-based binary log. Nevertheless, I need the row-based binlog. Can I use the binlog generated by old mysql and import it into another mysql of higher version that supports row-base binlog to get the row-based binlog?

share|improve this question

1 Answer 1

You cannot convert a binlog to another version, but you import its SQL as long as have mysqlbinlog for version 4.1.

Simply do the following:

  • Run MySQL 4.1 version of mysqlbinlog against the MySQL 4.1 binlog, saving output to stmts.sql
  • Install MySQL 5.x with these in my.cnf
    • log-bin=mysql-bin
    • binlog-format=ROW
  • Login to MySQL 5.1 and run source stmts.sql
  • Run SHOW BINARY LOGS;

When done, the binlogs you see should be row-based.

Give it a Try !!!

Why can't you just convert ? The BINLOG Magic Number gets displaced with later versions.

I wrote about before

Every time you restart mysql, the next empty binlog is created. Each version of MySQL has a different size:

  • MySQL 5.6 : 120
  • MySQL 5.5 : 107
  • MySQL 5.1 : 106
  • MySQL 5.0 : 98

In light of this, generating binlogs from scratch is the best way to go.

UPDATE : Thanks to Michael's Comment, you have to preload the data the binlog came from into the MySQL 5.1 Instance.

share|improve this answer
    
major caveat: the instance of the newer version of MySQL would need to have the exact dataset in place that you had on the 4.1 server at the starting binlog position, before you could replay old statement logs into it and generate useful row events out of it... row-based logging can only be generated by actually inserting/updating/deleting the actual rows of data on the new server, and if they aren't identical then the process won't work at all or will generate meaningless results. –  Michael - sqlbot Jun 26 '13 at 3:04
    
@Michael-sqlbot DOH !!! I forgot to preload the data !!! –  RolandoMySQLDBA Jun 26 '13 at 3:17

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.