Currently I am running Percona XtraDB Cluster 5.5. To switch smoothly to 5.7, I need to set up two-directions replication between one of cluster nodes and standalone PXC 5.7 node. So that I can switch production to 5.7 and switch back if some problems occur.
Config file for 5.5:
log_slave_updates = TRUE
server-id = XXXX
wsrep_auto_increment_control = off
auto-increment-offset = 1
auto-increment-increment = 10
Config file for 5.7:
binlog_checksum=NONE
gtid_mode=0
log_slave_updates = FALSE
server-id = YYYY
wsrep_auto_increment_control = off
auto-increment-offset = 3
auto-increment-increment = 10
Replication from 5.5(master) to 5.7(slave) works just fine.
Replication from 5.7(master) to 5.5(slave) produces an error
161116 14:09:27 [Note] WSREP: ready state reached
161116 14:09:27 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000082’ at position 150, relay log ‘./host55-relay-bin.000001’ position: 4
161116 14:09:27 [ERROR] Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 27, event_type: 35
161116 14:09:27 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
161116 14:09:27 [ERROR] Slave SQL: 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. Error_code: 1594
161116 14:09:27 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000083’ position 123
Output from mysqlbinlog host55-relay-bin.000004:
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
#161116 14:05:43 server id 4310 end_log_pos 107 Start: binlog v 4, server v 5.5.41-37.0-55-log created 161116 14:05:43
BINLOG ’
hz0sWA/WEAAAZwAAAGsAAAAAAAQANS41LjQxLTM3LjAtNTUtbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/!/;
at 107
#700101 3:00:00 server id 4313 end_log_pos 0 Rotate to mysql-bin.000083 pos: 4
at 150
#161116 14:06:43 server id 4313 end_log_pos 123 Start: binlog v 4, server v 5.7.14-8-57-log created 161116 14:06:43 at startup
ROLLBACK/!/;
BINLOG ’
wz0sWA/ZEAAAdwAAAHsAAAAAAAQANS43LjE0LTgtNTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADDPSxYEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AFDxxKk=
'/!/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 27, event_type: 35
ERROR: Could not read entry at offset 269: Error in log format or read error.
DELIMITER ;
End of log file
ROLLBACK /* added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
How can I fix this to make replication work in both directions? Thanks