mysql to lower_Replicating from a higher to lower MySQL version

As we know, replication is only supported

officially between consecutive majorMySQL 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.

注意：本文来自Pythian。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。