Thursday, January 23, 2020

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 :

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()