Upgrade MySQL InnoDB Cluster 8.0.18 to 8.0.19 (With MySQL InnoDB Cluster MetaData V2 Change)

MySQL InnoDB Cluster in 8.0.19 has new version of MySQL InnoDB Cluster MetaData.
MySQL Shell 8.0.19 full function has to be running with V2 metadata.

This is the tutorial for an sample upgrade of MySQL 8.0.18 to MySQL 8.0.19

Reference :
https://mysqlserverteam.com/upgrading-mysql-innodb-cluster-metadata/


The following InnoDB Cluster is running with MySQL 8.0.18.


 Running MySQL Shell 8.0.19 with MySQL InnoDB Cluster 8.0.18 (which the metadata version is @1.0.1) with command dba.getCluster() results in Warning :

WARNING: No cluster change operations can be executed because the installed metadata version 1.0.1 is lower than the version required by Shell which is version 2.0.0. Upgrade the metadata to remove this restriction. See \? dba.upgradeMetadata for additional details.





<clustter>.listRouters() lists the Router registered with the InnoDB Cluster.


To illustrate the Upgrade MySQL InnoDB Cluster from 8.0.18 to 8.0.19, here is the steps
1. Upgrade ALL Routers to New Version (8.0.19)
2. Upgrade the MySQL InnoDB Cluster Metadata to V2
3. Upgrade individual Server from 8.0.18 to 8.0.19

What would happen if Upgrading the MySQL InnoDB Metadata without upgrading Router -
1. Connect with the Cluster Admin User using MySQL Shell
( where the user was possibly created using dba.configureInstance("<server URL>", {clusterAdmin:'<admin user>', clusterAdminPassword:'<the password>'})

2. Execute the dba.upgradeMetadata {dryRun:true}

The User created with MySQL 8.0.18 might not have enough privileges with MySQL Shell 8.0.19.  

Login with super user (e.g. root) via normal mysql client (or mysql shell) to the Primary Node (e.g. primary:3310), and execute the GRANT statements as shown on the report notes.


Re-run the  dba.upgradeMetadata {dryRun:true}


This shows the list of ROUTERs which is to be upgraded before the metadata to be upgraded.


Upgrading MySQL Router 8.0.18 to 8.0.19
For the tutorial purpose, the 'upgrade' MySQL Router to 8.0.19 is simply to do with New boostraping process using MySQL Router 8.0.19.  This recreates the configuration and creating NEW router account.

Once all routers are upgraded, the metadata can be upgraded.  Running with dryRun mode shows :

The privileges for MySQL Router account are missing.  This is because the bootstrap of MySQL Router 8.0.19 creating new user.  The OLD router account is still valid.



With MySQL Router 8.0.19, there is new option(s) --account to define what user to be reused.   So all routers can share the same account without individual account being created.


UPGRADE Metadata to V2
Using MySQL Shell 8.0.19, perform :

MySQL [primary ssl] JS> dba.upgradeMetadata()



The metadata has been changed to V2.


Upgrade MySQL Server from 8.0.18 to MySQL 8.0.19
1. The upgrade should start with Secondary Server and the Primary Server should be upgrade as the Last Server.
2. Upgrade to MySQL 8.0.19 is simply to start it with MySQL 8.0.19 (mysqld).   The mysql_upgrade has been deprecated since MySQL 8.0.16.   "mysqld" when it is started with OLD database version automatically upgrade the database to its version.
3. By default, MySQL Server when it is configured as member node with MySQL Shell (<cluster>.addInstance...)  is started with group_replication_start_on_boot=true.  This tells the server to join InnoDB Cluster when it is started.    During the upgrade process, it might worth to change this setting (group_replication_start_on_boot) to false so that we can validate the UPGRADE before it rejoins the InnoDB Cluster.

Connect to Secondary Server1
mysql> mylect * from performance_schema.persisted_variables where variable_name like 'group_replication_start_on_boot%';


Change the setting to FALSE
mysql> set persist group_replication_start_on_boot=false;




Shutdown the Server

Change the Configuration to use New basedir to MySQL 8.0.19 folder (if needed)
e.g. basedir=/usr/local/mysql8019



Startup MySQL Server with New MySQL 8.0.19 binary (mysqld)

Check the errorlog for any failure and double check the server if it is running with 8.0.19
mysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 8.0.19-commercial |
+-------------------+
1 row in set (0.00 sec)

Persist the group_replication_start_on_boot=true so that the server with New Version 8.0.19 can rejoin on next startup.  The InnoDB Cluster will therefore be a mix version of 8.0.18 and 8.0.19.   The higher version of MySQL Server can only be the Secondary Server where there is a mix versions in the InnoDB Cluster.




Upon the restart of the MySQL Server, showing the status of the MySQL InnoDB Cluster as follows :



Repeat the Process for the next Secondary Server and finally the Primary Server.

When the Primary Server is shutdown, the new Primary Server is elected where the 2 Servers have the server version of MySQL 8.0.19.    When the Server is upgraded and rejoined, it has the member role as Secondary.



Finally, the status goes back to ALL online servers with MySQL 8.0.19.

Done!!!