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
Last_IO_Error:
error
connecting
to
master
'
[email protected]
:19025'
-
retry
-
time
:
60
retries:
1
|
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: