Replicating from MySQL 8.0 to MySQL 5.7

Replicating from MySQL 8.0 to MySQL 5.7

PREVIOUS POST
NEXT POST

In this blog post, we’ll discuss how to set a replication from MySQL 8.0 to MySQL 5.7. There are some situations that having this configuration might help. For example, in the case of a MySQL upgrade, it can be useful to have a master that is using a newer version of MySQL to an older version slave as a rollback plan. Another example is in the case of upgrading a master x master replication topology.

Officially, replication is only supported between consecutive major MySQL versions, and only from a lower version master to a higher version slave. Here is an example of a supported scenario:

5.7 master –> 8.0 slave

while the opposite is not supported:

8.0 master –> 5.7 slave

In this blog post, I’ll walk through how to overcome the initial problems to set a replication working in this scenario. I’ll also show some errors that can halt the replication if a new feature from MySQL 8 is used.

Here is the initial set up that will be used to build the topology:

First, before executing the CHANGE MASTER command, you need to modify the collation on the master server. Otherwise the replication will run into this error:

This is because the default character_set and the collation has changed on MySQL 8. According to the documentation:

The default value of the character_set_server and character_set_database system variables has changed from latin1 to utf8mb4.

The default value of the collation_server and collation_database system variables has changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

Let’s change the collation and the character set to utf8 on MySQL 8 (it is possible to use any option that exists in both versions):

You need to restart MySQL 8 to apply the changes. Next, after the restart, you have to create a replication user using mysql_native_password.This is because MySQL 8 changed the default Authentication Plugin to caching_sha2_password which is not supported by MySQL 5.7. If you try to execute the CHANGE MASTER command with a user using caching_sha2_password plugin, you will receive the error message below:

To create a user using mysql_native_password :

Finally, we can proceed as usual to build the replication:

Checking the replication status:

Executing a quick test to check if the replication is working:

Caveats

Any tentative attempts to use a new feature from MySQL 8 like roles, invisible indexes or caching_sha2_password will make the replication stop with an error:

Summary

Replicating from MySQL 8 to MySQL 5.7 is possible. In some scenarios (especially upgrades), this might be helpful, but it is not advisable to have a heterogeneous topology because it will be prone to errors and incompatibilities under some cases.

You might also like:

 

PREVIOUS POST
NEXT POST

Share this post

Comments (2)

  • Gauravkumar Mishra Reply

    Hi Vinicius,
    I have been trying to do this for past few days. I then bumped into your post , but still seems to be an issue for me.
    I am trying replication only for one particular database.
    On slave which is 5.7 has replicate-do-db=”databasename” set in my.cnf
    The replication works for almost a day and then we get this error as below:
    Last_SQL_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml’ file’ on query. Default database: ‘mysql’. Query: ‘BEGIN’

    even though I am using the parameter replicate-do-db it is still throwing an error for query on database ‘mysql’.
    I am wondering why? Let me know what do you think of this error.

    I will try your solution and will update here.
    Wish me good luck!!

    September 24, 2018 at 9:17 am
    • Vinicius Grippa Reply

      Hi Gauravkumar,

      Thanks for reaching! I hope this post help you solve your issue. Analyzing the error that you sent this seems to be a problem of the collation of the MySQL 5.7 that is trying to use one that does not exist. You need to compare both servers and use one collation that exist on MySQL 5.7. To check you can use:

      mysql > show global variables like ‘%collation%’;

      Compare the master and the slave and check if they are using the same.

      September 24, 2018 at 8:43 pm

Leave a Reply