Replicating from a higher to lower MySQL version

by Ivan Groenewold on Jul 26, 2017 12:00:00 AM

As we know, replication is only supported officially between consecutive major MySQL versions, and only from a lower version master to a higher version slave. 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).

Conclusion

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.

No Comments Yet

Let us know what you think

See all

Share this