MySQL replication with 5.7 master and 5.5 slave

Question

I'm trying to setup a MySQL replica with the master on 5.7 and the slave on 5.5.

On the master I have disabled the binlog_checksum.

After import a dump of the database, change the master and start the slave, if I make a change on the master, the slave get this error:

Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

And if I run mysqlbinlog to the relay log, I get this error:

# mysqlbinlog /var/lib/mysql/mysql-relay-bin.000002

...

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 61, event_type: 34
ERROR: Could not read entry at offset 270: Error in log format or read error.
DELIMITER ;
# End of log file

...

There's any chance to get this replication working? The upgrade process can be problematic for my client and I'm trying to avoid that.

Thanks

Answer 1

From the source files, sql/log_event.h, event 34:

ANONYMOUS_GTID_LOG_EVENT= 34,

GTID wasn't introduced until 5.6, so there's no possible way a 5.5 server can understand this event (nor can the the version of mysqlbinlog that shipped with 5.5 understand it -- hence the error; you will probably be able to read this relay log if you use the version of mysqlbinlog that ships with 5.7).

It might seem like you could disable gtid_mode on the master, but there are two problems with this:

• Even if that seems to work, it's completely unsupported, and only a matter of time until something else breaks. The 5.7 master can emit events the pre-5.7 slave can't handle.

  when upgrading to MySQL 5.7, the slaves must be MySQL 5.7 before you can upgrade the master to 5.7. — https://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html

• Any events written to the master's binlog since the upgrade are lost, from the perspective of a pre-5.7 slave, since such a server can't replicate them, so changing gtid_mode now is the equivalent of closing the barn door after the horses have escaped. This slave's data is no longer useful, unless it is upgrade to understand the master's events and can continue replicating where it stopped.

You may still be able to salvage the slave if you upgrade it to 5.7 but I see no other meaningful alternatives.

Answer 2

I have another reason why replication can never fully work in this scenario.

The format of binary logs can shift when the format of data changes. One crazy example I have written about is the DATETIME data type. MySQL 5.6 introduced microseconds into DATETIME. This changed the width of the DATETIME value when the binlog_format used was ROW. This will instantly break replication when a slave before MySQL 5.6 tries to interpret a DATETIME value from a master whose version is MySQL 5.6+.

I wrote about this phenomenon twice

• Jan 02, 2015 : mysql replication master 5.5 slave 5.1 error on create database
• Jun 18, 2015 : mysql master slave replication scenario

While Michael - sqlbot's answer is much closer to the answer you are looking for, my answer is just giving you an additional reason why replication must always break in your particular case.

Perhaps if both Master and Slave used binlog_format=STATEMENT, you might get away with replication for a limited time, provided the internal binlog format does not unpack into another ENUM value that cannot be understand by the 5.5 slave.