This means for example, the following scenario is supported:
5.6 master –> 5.7 slave
while these two scenarios are not supported:
5.5 master –> 5.7 slave
5.7 master –> 5.6 slave
That being said, in some contexts (e.g a MySQL upgrade) it can be valuable to be able to replicate from a master that is using a newer version of MySQL to an older version slave.
This could be used as part of a rollback strategy, and/or be needed in the case of upgrading a master-master replication topology.
The idea of the article is to provide you with instructions on how to make replication work for scenarios where the master is a higher version than the slaves.
Replicating from a 5.6 master to a 5.5 slave
The following parameters need to be present on a 5.6 master’s my.cnf to be able to replicate to a lower version slave:
slave_sql_verify_checksum = OFF binlog_checksum = NONE log_bin_use_v1_row_events=1 binlog_row_image=FULL gtid_mode=OFF
NOTE: remember to bounce the server after any needed parameter changes
The temporal datatypes (TIME/DATETIME/TIMESTAMP) support fractional values starting from MySQL 5.6.4. Hence the storage requirement and encoding differ in comparison to pre-MySQL 5.6.4 temporal datatypes.
For this reason, writing to tables with columns using any of (TIME/DATETIME/TIMESTAMP) created AFTER the upgrade to 5.6 will break replication to 5.5, unless the binlog format is set to STATEMENT.
Replicating back temporal columns using the new format in ROW based format will not work.
If using MIXED binlog format, unsafe statements are logged using ROW format, so it may or may not work depending on the presence of said unsafe statements.
Replicating from a 5.7 master to a 5.6 slave
There is no need for any special my.cnf parameters on this case. However, there is a bug with ignorable events in older MySQL 5.6 versions that prevents replication from a 5.7 master.
This is fixed on 5.6.24, so if you want to replicate from 5.7 master to 5.6, make sure the slave is using MySQL >=5.6.24.
Finally, avoid using SET PASSWORD statements (those are deprecated anyway), as they will lock you out of your slave in a mixed 5.7/5.6 topology.
You can use ALTER USER user IDENTIFIED BY ‘auth_string’; instead. There is a bug reported about this as well (closed as Won’t fix).
While not a supported configuration, replicating from a higher to lower MySQL version is still possible. Keep in mind that there may be some edge cases where you still encounter problems.
I look forward to hearing about your experiences in the comments section below.
Interested in working with Ivan? Schedule a tech call.