Replicating from MySQL 8.0 to MySQL 5.7
source link: https://www.tuicool.com/articles/hit/yeYRzqR
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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:
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 collation on the master server. Otherwise the replication will run into 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 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):
# master my.cnf [client] default-character-set=utf8 [mysqld] character-set-server=utf8 collation-server=utf8_unicode_ci
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:
Last_IO_Errno: 2059 Last_IO_Error: error connecting to master '[email protected]:19025' - retry-time: 60 retries: 1
To create a user 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 proceed as usual to build the replication:
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;
Checking 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)
Executing a quick test to check if the replication is working:
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)
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:
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
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:
- Migrating database charsets to utf8mb4: a story from the trenches
- This webinar might also have some useful pointers Troubleshooting issues with MySQL character sets
The post Replicating from MySQL 8.0 to MySQL 5.7 appeared first on Percona Database Performance Blog .
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK