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.
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:
- Upgrade the MySQL Router Instances to version 8.0.19.
- Upgrade the MySQL Shell to version 8.0.19.
- 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:
- Stop MySQL Router
- Upgrade/Replace MySQL Router installation with version 8.0.19
- 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.
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.
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.
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):
GRANT CLONE_ADMIN, EXECUTE, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, SYSTEM_VARIABLES_ADMIN ON *.* TO <em>'admin'@'%'</em> WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO <em>'admin'@'%'</em> WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO <em>'admin'@'%'</em> WITH GRANT OPTION;
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
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:
GRANT EXECUTE, SELECT ON mysql_innodb_cluster_metadata.* TO <user>@<host>
GRANT DELETE, INSERT, UPDATE ON mysql_innodb_cluster_metadata.routers TO <user>@<host>
GRANT DELETE, INSERT, UPDATE ON mysql_innodb_cluster_metadata.v2_routers TO <user>@<host>
GRANT SELECT ON performance_schema.global_variables TO <user>@<host>
GRANT SELECT ON performance_schema.replication_group_member_stats TO <user>@<host>
GRANT SELECT ON performance_schema.replication_group_members TO <user>@<host>
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.
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/