In this article, we will discuss how to set up replication from MySQL 8.0 to MySQL 5.7. In some cases, using this configuration can be helpful. For example, in the case of MySQL upgrades, it would be useful to use a newer version of MySQL master server as a rollback plan for older versions of slave servers. Another is in the case of upgrading master replication.

MySQL replication is only supported between successive master versions, and only between master low and high.

Master 5.7 - > slave 8.0 support Master 8.0 - > slave 5.7 does not support

In this article, I'll show you how to overcome this problem and initialize it to set up replication in this scenario. If you use MySQL 8's new features, I will also show you some errors that can pause replication.

slave > select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.17-log |
+---------------+
1 row in set (0.00 sec)

master > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

First, before executing the CHANGE MASTER command, you need to modify the sorting rules on the primary server. Otherwise, the replication will encounter this error:

slave > show slave status\G
Last_Errno: 22
Last_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/opt/percona_server/5.7.17/share/charsets/Index.xml' file' on query. Default database: 'mysql8_1'. Query: 'create database mysql8_1'

This is because the default character_set and sorting rules on MySQL 8 have changed. According to the document:

The default values of character_set_server and character_set_database system variables have been changed from latin1 to utf8mb4.
The default values of collat_ion_server and collat_ion_database system variables have changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

Let's change the collation and character set to utf8 in MySQL 8 (you can use any options that exist in both versions):

# master my.cnf
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

Restart MySQL 8 to take effect. Next, after restarting, you must create a replicated user using mysql_native_password. This is because MySQL 8 changed the default Authentication Plugin to maching_sha2_password, which is not supported by MySQL 5.7. If you try to execute the CHANGE MASTER command using the caching_sha2_password plug-in, you will receive the following error message:

Last_IO_Errno: 2059
Last_IO_Error: error connecting to master 'root@127.0.0.1:19025' - retry-time: 60 retries: 1

Create users using mysql_native_password:

master> CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'repli$cat';
master> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';

Finally, we can continue to build replicates as usual:

master > show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

slave > CHANGE MASTER TO MASTER_HOST='127.0.0.1', 
	MASTER_USER='replica_user', 
	MASTER_PASSWORD='repli$cat',
	MASTER_PORT=19025, 
	MASTER_LOG_FILE='mysql-bin.000007', 
	MASTER_LOG_POS=155; start slave;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

# This procedure works with GTIDs too
slave > CHANGE MASTER TO MASTER_HOST='127.0.0.1', 
	MASTER_USER='replica_user', 
	MASTER_PASSWORD='repli$cat',
	MASTER_PORT=19025,
	MASTER_AUTO_POSITION = 1 ; start slave; 

Check the replication status:

master > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replica_user
Master_Port: 19025
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 155
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 155
Relay_Log_Space: 524
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 00019025-1111-1111-1111-111111111111
Master_Info_File: /home/vinicius.grippa/sandboxes/rsandbox_5_7_17/master/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)

Perform a quick test to check that the replication is working properly:

master > create database vinnie;
Query OK, 1 row affected (0.06 sec)

slave > show databases like 'vinnie';
+-------------------+
| Database (vinnie) |
+-------------------+
| vinnie |
+-------------------+
1 row in set (0.00 sec)

Matters needing attention

Any attempt to use new features in MySQL 8 (such as rule, invisible index or caching_sha2_password) will stop replication and display errors:

master > alter user replica_user identified with caching_sha2_password by 'sekret';
Query OK, 0 rows affected (0.01 sec)

slave > show slave status\G
       Last_SQL_Errno: 1396
       Last_SQL_Error: Error 'Operation ALTER USER failed for 'replica_user'@'%'' on query. Default database: ''. Query: 'ALTER USER 'replica_user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$H	MEDi\"gQ
                wR{/I/VjlgBIUB08h1jIk4fBzV8kU1J2RTqeqMq8Q2aox0''

summary

You can copy from MySQL 8 to MySQL 5.7. In some cases (especially upgrades), this may be helpful, but it is not recommended for use in heterogeneous topologies because it is error-prone and incompatible in some cases.

Links to the original text:

http://10.186.18.11/confluence/pages/viewpage.action?pageId=14432226