Error in Master/Master replication between Mysql 8 and Mysql 5.6

All we need is an easy explanation of the problem, so here it is.

I’ve setup master to master replication Between a Mysql 5.6 and Mysql 8.0 server. I can get both slaves to run on both servers. Except when there is a change made on the Mysql 8.0 server (e.g. database/table created, or row inserted), the Mysql 5.6 slave encounters an error:

Mysql 5.6 > ‘SHOW SLAVE STATUS\G;’

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.

I wish to get replication working both ways whilst keeping the versions the same. Is there any way this can be achieved?

Mysql 8 config



[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
datadir         = /var/lib/mysql


bind-address            = 0.0.0.0

key_buffer_size         = 16M
max_allowed_packet      = 16M

myisam-recover-options  = BACKUP

max_connections        = 1000


log_error = /var/log/mysql/error.log

server-id               = 2

log_bin                 = /var/log/mysql/mysql-bin.log
binlog-format           = row
binlog_expire_logs_seconds      = 432000
relay-log               = /var/log/mysql/mysql-relay-bin.log
relay-log-index         = /var/log/mysql/mysql-relay-bin.index

replicate-ignore-db     = mysql,information_schema,performance_schema
max_binlog_size         = 100M

binlog_ignore_db        = mysql,information_schema,performance_schema
binlog_checksum         = NONE
lower_case_table_names = 1

Mysql 5.6 config

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
log_error=/var/log/mysql/mysql_error.log


wait_timeout = 900

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 1000

query_cache_limit       = 1M
query_cache_size        = 16M

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 5
relay-log               = /var/log/mysql/mysql-relay-bin.log
relay-log-index         = /var/log/mysql/mysql-relay-bin.index

replicate-ignore-db     = mysql,information_schema,performance_schema
max_binlog_size         = 100M
binlog-format           = row

binlog_ignore_db        = mysql,information_schema,performance_schema

lower_case_table_names = 1

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You cannot have a master version that is a higher version than a slave replica. The MySQL-8.0 will generate binary log messages that MySQL-5.6 won’t understand hence your error ‘Could not parse relay log event entry’.

Replication is also supported between one major version so 5.7 -> 8.0.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply