MySQL master-master upgrade from 5.7 to 8.0

MySQL & MariaDB
1

Hello

I have master-master Percona MySQL replica. I’m planning to upgrade the replica from 5.7 to 8.0. I have designed the following steps to follow for upgrade testing, please let me know if this works?
Datacenter1- Master1 -salve1, slave2
Datacenter2 -Master2-slave3, slave4
Apps are designed such a way that they always connect to the local master

  1. Start MySQL upgrade with DC1, meanwhile all apps are connected to DC2, Master2
  2. Upgrade slave1,slave2 and validate
  3. Upgrade Master1 and validate
  4. At this point DC1 will be on the new version 8.0 and DC2 will be on old version 5.7, app is still connected to DC2
  5. Now I would point the app to new version DC1 (master-master replication will be running in mixed version state 8.0->5.7)
  6. As per our environment design we cannot upgrade two datacenters at once, so in general we upgrade one dc and give a bake-in time for a week and then upgrade the second dc
  7. So the master-master Percona cluster will be in mixed version state before upgrading the second datacenter
  8. Start MySQL upgrade with DC2, meanwhile alls apps are connected to DC1, Master1 with MySQL version 8.0
  9. Upgrade slave3,slave4 and validate
  10. Upgrade Master2 and validate

Since master-master both are in different versions for few days, I will make sure no DDL (create or alter)or schema changes are done until upgrade is completed. Anything else I need to be cautious about ?
Please let me know if there is a better way to achieve the upgrade which works for my environment ? Basically I’m worried about the replication issues between 5.7->8.0 and 8.0->>5.7 , how to make sure replication works in the mixed version state ?

2

In addition to the DDLs, don’t execute any ALTER USER, or CREATE USER either, as this will probably break replication between 5.7 and 8.0.

I would ensure every table in 5.7 is utf8mb4 first, before doing any upgrades. This will help prevent any charset issues in replication.

Be sure to run the mysql upgrade checker on your 5.7 before you attempt any upgrades. Fix all errors.

When mysql 8 starts for the first time on top of 5.7 datadir, it will take some time to process all .frm files into the new dictionary. If this fails for any reason, the upgrade is now stuck and unrecoverable. You will not be able to complete upgrade to 8 nor will you be able to rollback to 5.7. The only option is to restore a recent 5.7 backup. Pay close attention to mysql’s error logs during the upgrade. Failure messages will be printed there.

3

Thanks.

I understand it is recommended to alter every table in 5.7 to utf8mb4 characterset, but since it is a warning and considering the time constraint for upgrade in my environment can I proceed with upgrade by using parameters mentioned in below article to help replication between 8.0 ->5.7 ?

master my.cnf

[client]
default-character-set=utf8

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

4

Yes, they are warnings. Yes, you can set those parameters like in the blog.

5

I have two test servers configured with master-master replication, now I have stopped the service on the master1 and upgraded it from 5.7 to 8.0 (I did run the mysql upgrade checker utility and addressed the errors and ignored the warnings). After the upgrade was completed, I have made changes to the configuration file as below in master1 and started the service.

[client]
default-character-set=utf8

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

The mysql --version command showed the 8.0.39-30 Percona Server, but I was unable to login to mysql prompt, I gave it sometime for the existing data on disk to be updated to the new version, after sometime I was able to login to the mysql prompt using admin credentials but I see the error as below.

Master1 (version 8.0) , show slave status output

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1451
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at source log mysql-bin.000005, end_log_pos 74218. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Last_SQL_Errno: 1451
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at source log mysql-bin.000005, end_log_pos 74218. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any

Master2 (version 5.7) , show slave status output

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 22
Last_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/local/xyz/share/charsets/Index.xml’ file’ on query. Default database: ‘’. Query: ‘BEGIN’
Last_SQL_Errno: 22
Last_SQL_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/local/xyz/share/charsets/Index.xml’ file’ on query. Default database: ‘’. Query: ‘BEGIN’

The error seems due to data not consistent between master1 and master2, let me know why did I end up in this scenario and how can I fix it ?

Thanks

6

You cannot replicate with 8.0 as master and 5.7 as replica. Replication only flows from lower to higher versions.

Check the table as mentioned in the error for info on why replication broke on the 8.0 master.