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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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:
1 2 3 | slave > show slave statusG 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):
1 2 3 4 5 6 7 | # 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:
1 2 | Last_IO_Errno: 2059 |
To create a user using mysql_native_password :
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | master > show master statusG *************************** 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | master > show slave statusG *************************** 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:
1 2 | master > create database vinnie; Query OK, 1 row affected (0.06 sec) |
1 2 3 4 5 6 7 | 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:
1 2 | master > alter user replica_user identified with caching_sha2_password by 'sekret'; Query OK, 0 rows affected (0.01 sec) |
1 2 3 4 | slave > show slave statusG 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 May Also Like
Read our solution brief to learn how to setup MySQL Amazon RDS instances. The ease of initial setup makes Amazon RDS capable of accommodating rapid deployment, as well as application development. Our brief also includes failover and basic business continuity components.
For a proven MySQL database architecture that is similarly easy to set up, check out our solution brief: Get Up and Running with Percona Server for MySQL. This solution is ideal for startup organizations and small businesses.
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!!
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.
I realize it has been awhile; but I am getting the same #255 error with replication from 8.0.13-4 to 5.7.
[ERROR] Slave SQL for channel ”: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/percona-server/charsets/Index.xml’ file’ on query.
The character sets and collations are the same and using UTF8.
master 8.0> show global variables like ‘%character%’;
+————————–+————————————-+
| Variable_name | Value |
+————————–+————————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/percona-server/charsets/ |
master 8.0> show global variables like ‘%collation%’;
+——————————-+——————–+
| Variable_name | Value |
+——————————-+——————–+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+——————————-+——————–+
slave 5.7> show global variables like ‘%character%’;
+————————–+————————————-+
| Variable_name | Value |
+————————–+————————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/percona-server/charsets/ |
+————————–+————————————-+
slave 5.7> show global variables like ‘%collation%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+———————-+—————–+
New database created on master 8.0:
master 8.0> select * from information_schema.schemata where schema_name = ‘steve_test’;
+————–+————-+—————————-+————————+———-+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+————–+————-+—————————-+————————+———-+
| def | steve_test | utf8 | utf8_unicode_ci | NULL |
+————–+————-+—————————-+————————+———-+
This is an upgrade on a replicated server from 5.7.25-28 to 8.0.13-4 so all users were in place and using the correct plugin.
Any ideas why the replicated statement from 8.0 generates an error in replication to 5.7?
Update:
Using SET NAMES utf8;
Before running statements successfully replicates to 5,7 from 8.0.
The default-character-set=utf8 in [client] works but you have to make sure it is in all instances of my.cnf that client might look at. Adding it to local my.cnf file fixed the issue.
Glad that you could figure out a workaround. Another option is setting the utf8 on the init-connect parameter.
The init-connect option was plausible; but it does not work for users with the super privilege,