Upgrading MySQL InnoDB Cluster Metadata

As on every release MySQL Shell 8.0.19 includes several bug fixes as well as new features. However, this release includes some significant changes:

  • Fixes for bugs in the schema definition:  Cascading keys in multi-primary mode.
  • Introduction of InnoDB ReplicaSet.

These changes required updates to the metadata schema definition, some of which incompatible with previous versions. InnoDB Cluster and ReplicaSet deployments that are created using MySQL Shell 8.0.19 will automatically use the new version of the metadata schema. However, to use InnoDB Cluster deployments created in older Shell versions (8.0.18 or earlier) with MySQL Shell 8.0.19 will need a Metadata Schema upgrade.

The metadata upgrade process itself is straightforward and can be performed automatically. However, to minimize downtime and maintain version compatibility, it is necessary to coordinate Router upgrades with Shell and Metadata upgrades.

Can I use MySQL Shell and MySQL Router 8.0.19 with my current deployments?

The MySQL Router 8.0.19 is backward compatible so it continues working without any issue. On the other hand, the MySQL Shell 8.0.19 will allow you to do read operations on an InnoDB Cluster, however, no cluster change operations will be allowed until the metadata is upgraded to version 2.0.0.

Cluster in Read Only Mode
Cluster in Read Only Mode

Note that MySQL Router 8.0.18 will not function with version 2 of the metadata, so you must ensure Router is upgraded first.

How To Upgrade

A successful upgrade is done when the following actions are completed:

  1. Upgrade the MySQL Router Instances to version 8.0.19.
  2. Upgrade the MySQL Shell to version 8.0.19.
  3. Upgrade the Metadata schema to version 2.0.0 (from the previous version 1.0.1).

Upgrading MySQL Router

Upgrading the MySQL Router instances is the first step. The upgrade process of the Router is the same as usual:

  1. Stop MySQL Router
  2. Upgrade/Replace MySQL Router installation with version 8.0.19
  3. Restart MySQL Router

MySQL Router 8.0.19 can work normally with the old version of the metadata. You can also bootstrap new instances of Router 8.0.19 against the old metadata. Thus, there should be no problems in case the Shell and Metadata can’t be upgraded right after the Router.

Upgrading MySQL Shell and Metadata

After the MySQL Router instances have been upgraded, the next step is to use MySQL Shell 8.0.19 and execute dba.upgradeMetadata() to perform the metadata upgrade.

This process needs to be executed as the root MySQL user (or equivalent). Cluster admin accounts created in older versions of the Shell will not work as they lack some privileges required for the upgrade.

To upgrade the metadata simply use the new dba.upgradeMetadata() function.

Non upgraded MySQL Router instance found during the metadata upgrade.
Non upgraded MySQL Router instance found during the metadata upgrade.

IMPORTANT: Once the metadata is upgraded, older versions of the Shell will NOT be able to manage the InnoDB Cluster. Older versions of the Router will no longer work either.

The image above shows two actions that take place when the upgrade is executed:

  • The MySQL Router accounts created while bootstrapping an instance are upgraded to meet the requirements of the Metadata 2.0.0
  • A check is done to ensure ALL the MySQL Router Instances have been upgraded to version 8.0.19.

NOTE: the MySQL Router 8.0.19 allows using a custom account (–account option) to bootstrap the instance, if the used account does not have the prefix mysql_router the privileges for such account will NOT be upgraded during this process. See the Upgrading custom MySQL Router accounts section below for more details.

Having all the MySQL Router instances upgraded is a pre-requisite for the upgrade to Metadata 2.0.0, if the upgrade check identifies MySQL Router instances that have NOT been upgraded, the first 10 of them will be listed here and ask for user intervention.

There are three ways to overcome this:

  • Upgrade the remaining MySQL Router instances to version 8.0.19 and select option 1 to continue with the metadata upgrade.
  • Abort the operation, upgrade the remaining MySQL Router instances to version 8.0.19 and execute upgradeMetadata again.
  • Unregister the listed MySQL Router instances ONLY if it is some leftover record, this is, the listed MySQL Router instance no longer exists.
Metadata upgrade process completed.
Metadata upgrade process completed.

After the upgrade process, the InnoDB Cluster is fully manageable by MySQL Shell 8.0.19, the WARNING indicating no change operations are allowed is gone.

Additional Tasks

Updating the cluster admin user

When an InnoDB Cluster is created it is possible to create a cluster admin account by using the clusterAdmin option. That account will have the minimal set of privileges required to manage a Cluster.

To use these pre-existing cluster admin accounts with MySQL Shell 8.0.19, they need to be updated with the new set of privileges. They can be updated by executing the following grant statements as indicated (e.g. while connected as root):

Upgrading custom MySQL Router accounts

The bootstrap process of the MySQL Router includes the creation of a MySQL account to be used to talk to the InnoDB cluster. These accounts had in common the mysql_router prefix.

Starting MySQL Router 8.0.19 it is possible to use a custom account in the bootstrap process through the –account command line argument. Using such option prevents the router from creating the standard account, allowing several Router instances to share the same account.

The upgradeMetadata process does NOT upgrade MySQL Router accounts unless they have the  mysql_router privileges, if custom accounts were used while bootstrapping a MySQL Router instance, such accounts must be upgraded by hand.

The upgradeMetadata function lists the right grants that should be given to those accounts, these are:

To upgrade such accounts simply replace <user>@<host> by the account user and hostname and execute the statements using either the cluster admin account or root users.

Resources

For details about the MySQL Shell please take a look at the MySQL Shell User Guide.

For additional details about features introduced by this version and the full list of fixed bugs take a look at the Release Notes.

Don’t forget to download it and give it a try, your feedback is very welcome!

You can reach us at #shell channel in https://mysqlcommunity.slack.com/

 

About Rene Ramirez

Juan Rene Ramirez Monarrez (a.k.a. rennox) is a Software Engineer with a master degree in Computer Science residing in Mexico. He started his career at IBM almost 20 years ago in application development, had a pass on Automation at Freescale Semiconductor (now MXP) and landed at the MySQL world 8 years ago. At MySQL he started working at the Workbench team and now is leading the efforts to make the MySQL Shell the client by excellence for different MySQL products.