MySQL Shell AdminAPI

Preamble

​​

The MySQL development team is pleased to announce the release of a new 8.0 version to support MySQL Shell AdminAPI – 8.0.20!

After the previous exciting release, which introduced InnoDB ReplicaSet, the next one focuses on improving ReplicaSets management and, most importantly, the InnoDB cluster.

Here you will learn and see how to update MySQL Shell to the new version!

MySQL Shell AdminAPI

This release focuses on management improvements for AdminAPI, namely: Simplification and improvement of administrator account processing: new commands to configure MySQL InnoDB, ReplicaSet, and Router cluster administrator accounts.

MySQL Shell integration with InnoDB ReplicaSet: Similar to the InnoDB Cluster, Shell itself has received extensions that make it easy to manage ReplicaSets.

Creation of mutually exclusive ReplicaSet operations: make sure that ReplicaSet operations are locked in order to avoid different operations to make changes to the same ReplicaSet.
And as always, the quality has been greatly improved with many bug fixes!

Administrator account management

Deploying an InnoDB or ReplicaSet cluster involves setting up administrator accounts. This is necessary for:

• Administer the cluster/replicaSet via AdminAPI Shell.
• MySQL Router loader in cluster/replication.
• Allow MySQL Router to work in cluster/replication.

Even if the database administrator is free to use the root account for such operations, it is recommended to create accounts with a minimal set of privileges to work with the InnoDB or ReplicaSet cluster. For this purpose, AdminAPI provides users with a very easy way to do this: Using dba.configureInstance() with the clusterAdmin option.

For example, a typical InnoDB cluster configuration will consist of the following sequence of steps:

mysql-js> // Configure instances to use IDC + create an administrator account
mysql-js> dba.configureInstance("root@instance1:3306", {clusterAdmin: "myAdmin"});
mysql-js> dba.configureInstance("root@instance2:3306", {clusterAdmin: "myAdmin"});
mysql-js> dba.configureInstance("root@instance3:3306"),
{clusterAdmin: "myAdmin"});
mysql-js>
mysql-js> // Connect with the newly created administrator account and create a cluster
mysql-js> \c myAdmin@instance1:3306
mysql-js> var cluster = dba.createCluster("myCluster");
mysql-js>
mysql-js> // Add instances to the cluster
mysql-js> cluster.addInstance("myAdmin@instance2:3306");
mysql-js> cluster.addInstance("myAdmin@instance3:3306");
mysql-js>
mysql-js> // Log out of mysqlsh and download the router
mysql-js> \quit
Bye!

$ mysqlrouter --bootstrap myAdmin@instance1:3306 ...

The clusterAdmin username and password must be the same in all instances of the cluster. For this reason, every time a new instance is added to a cluster, the database administrator must ensure that the account exists in the target instance.

A new internal router account is created for each initial load.

Frequent obstacles

Given the above, the following scenarios have become common in any high availability work environment: Creating multiple clusterAdmin accounts or using different passwords for the same account, which leads to cluster configuration failures.
The exponential growth of router administrator accounts, given the need to deploy multiple instances of the router – an unwanted and possible security threat.

With this in mind, as well as constant care to provide the most simple but powerful AdminAPI, simplified all account management.

New approach

In this release, you will be able to use the following new commands:

.setupRouterAccount(user, [options])
.setupRouterAccount(user, [options])

… Configure a centralized router administrator account for each cluster/replication, avoiding the increasing number of accounts and any necessary actions to clear unused accounts.

And, the following new commands:

.setupAdminAccount(user, [options])
.setupAdminAccount(user, [options])

… Configure a centralized cluster administrator/replication account, avoiding the need to create clusterAdmin accounts on each cluster element with the same credentials.

Conversion of a typical sequence of steps to configure the InnoDB cluster to the following:

mysql-js> // Configure instances to use IDC
mysql-js> dba.configureInstance("root@instance1:3306")
mysql-js> dba.configureInstance("root@instance2:3306")
mysql-js> dba.configureInstance("root@instance2:3306")
mysql-js>
mysql-js> // Connect to the target instance and create a cluster
mysql-js> \c root@instance1:3306
mysql-js> var cluster = dba.createCluster("myCluster")
mysql-js>
mysql-js> // Creating a cluster administrator account
mysql-js> cluster.setupAdminAccount("clusterAdmin")
mysql-js>
mysql-js> // Connect with the newly created cluster administrator account and perform cluster management/configuration.
mysql-js> \c clusterAdmin@instance1:3306
mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.addInstance("root@instance2:3306");
mysql-js> cluster.addInstance("root@instance3:3306");
mysql-js>
mysql-js> // Create a router administrator account
mysql-js> cluster.setupRouterAccount("routerAccount");
mysql-js>
mysql-js> // Exit mysqlsh and boot up the router using the newly created router administrator account
mysql-js> \quit
Bye!

$ mysqlrouter --bootstrap clusterAdmin@instance1:3306 --account=routerAccount ...

New metadata processing rights

With the new metadata schema and update function introduced in 8.0.19, the current set of privileges granted to clusterAdmin accounts created using dba.configureInstance() is no longer sufficient.

So that users can update their current clusters to the latest metadata schema using the same clusterAdmin accounts, the new cmdlets can also be used to update accounts to include all the necessary privileges. This is achieved through the use of the cradle option:

mysql-js> // Update clusterAdmin account privileges
mysql-js> cluster.setupAdminAccount("myAdmin@instance1", {update: true});
...
mysql-js> cluster.setupAdminAccount("myAdmin@instance2", {update: true});

MySQL Shell integration with ReplicaSet

The InnoDB ReplicaSet requires an object to work. Thus, similarly to the InnoDB Cluster, AdminAPI provides dba.getReplicaSet() command which returns the ReplicaSet descriptor. However, for the InnoDB Cluster, you can run MySQL Shell with an already created and filled cluster object using the command line parameter:

--cluster

This function has now been moved to the InnoDB ReplicaSet! You can now run MySQL Shell and use the following command line parameter to get a filled ReplicaSet object:

--replicaSet

Example:

$ mysqlsh --replicaset clusterAdmin@instance1:33

In order to successfully use this option, the command console must provide the URI for the ReplicaSet member.

Within this port, the following commands have also been extended to support ReplicaSet:

• redirect-primary: Connection to the ReplicaSet primary server;
• redirect-secondary: Connection to a ReplicaSet secondary server;

More secure InnoDB ReplicaSet management

AdminAPI, as an InnoDB ReplicaSets control panel, provides the most pleasant and easy to use solution for users. However, it is also responsible for providing a secure environment for such management tasks.

Even though it should not be very common that several Shell instances run on the same ReplicaSet at the same time, it was possible and allowed. Such situations can lead to inconsistencies in both metadata and replication mechanisms, resulting in unexpected or broken settings. For this reason, we have improved ReplicaSet operations to ensure that, where applicable, operations are mutually exclusive.

Let’s illustrate a catastrophic scenario: database administrators Alex and John work on the same ReplicaSet and almost simultaneously:

Alex:

mysql-js> // Primary switchover
mysql-js> replicaset.setPrimaryInstance("instance2");

Launch the following actions:

1. Metadata updated and topology change (view_id) registered
2. The “old” primary becomes read-only

John:

mysql-js> // Add a new instance to the ReplicaSet set
mysql-js> replicaset.addInstance("instance4");

The cause of the failure is due to one of the following factors:

1. Updating the metadata to include a new instance fails because the main instance became R/O
2. A change of view_id topology in metadata conflicts because it already exists with the same identifier.

And in the end, leads to an unexpected/unknown result.

Locks for help

We have implemented a locking mechanism for ReplicaSet operations, which ensures that no operation that changes the metadata topology and/or ReplicaSet can be performed simultaneously. Locks can be global or at the instance level.

Details:

• dba.upgradeMetadata() and dba.createReplicaSet() are globally exclusive operations. No other operation can be executed on the same ReplicaSet or any of its instances while they are being executed.
• <ReplicaSet>.forcePrimaryInstance() and <ReplicaSet>.setPrimaryInstance() are operations that modify the main one, so special attention is required. This approach ensures that no other operations that modify the primary update or update an instance can be performed until the first operation is completed.
• <ReplicaSet>.addInstance(), <ReplicaSet>.rejoinInstance() and <ReplicaSet>.removeInstance() are operations that modify an instance. For these operations, an instance-level lock has been implemented, which provides an operation for each instance at a time, but allows multiple operations to be performed simultaneously in different instances.

Errors related to the clone

Thanks to the integration of the MySQL Clone plug-in into InnoDB Cluster and ReplicaSet, preparation became as easy as a pie. Today, it is one of the most important built-in technologies available through AdminAPI, providing always-available initialization with incredibly fast speed.

Error # 30657911 – <REPLICASET>.ADDINSTANCE() USING CLONE SEGFAULTS WHEN RESTART IS NOT AVAILABLE

Using a clone as a security method to add an instance to ReplicaSet caused a crash if the target instance did not support RESTART. This bug was fixed in this release and the ReplicaSet.addInstance() behavior has been extended to take into account this scenario and whenever the operation expires, the command undoes and undoes the changes.

If it is impossible to upgrade an instance to a version that supports RESTART, the user can manually perform RESTART and then run the .addInstance() command again, which will use step-by-step recovery and will not require a subsequent restart.

Error # 30866632 – TIMEOUT FOR SERVER RESTART AFTER CLONE TOO SHORT

When a clone is run to provide an instance, a restart is required to complete the process. During this restart, transaction reservations are applied to the instance, and depending on the size of this lag, the process may take longer or less time. For servers that apply a very large transaction backup, the default 60-second timeout may not be sufficient.

For this reason, a new global parameter dba.restartWaitTimeout has been added to Shell, allowing users to set the correct value for restart time.

mysql-js> // Check current parameters on site
mysql-js> shell.options
mysql-js> // Change the value dba.restartWaitTimeout
mysql-js> shell.options.set("dba.restartWaitTimeout", 300);

Error # 30645697 – RECOVERY METHOD: CLONE WHEN ALL MEMBERS ARE IPV6 WILL ALLOW ERRANT GTIDS

One possible use for cloning is to allow adding an instance with a divergent GTID set to a cluster. However, the MySQL Clone plug-in has a limitation regarding IPv6 usage. In particular, if all cluster members use IPv6, donors cannot be used because the Clone does not support the use of IPv6 addresses for a valid donor list. Thus, in a scenario where all members use IPv6 and a clone has been selected, the clone will end up with an error and roll back to a gradual recovery.

Eventually, an instance will be added to the cluster using incremental recovery, but due to differences in the GTID set, the instance will not be accepted by the cluster.

This was improved by implementing a new check that prohibits the use of a clone as a recovery method when all cluster members use IPv6 addresses.

Errors related to the InnoDB cluster

InnoDB Cluster, as the main MySQL HA solution, attracted attention with several bug fixes:

Error # 30739252 – RACE CONDITION IN FORCEQUORUM

In some specific situations where .forceQuorumUsingPartitionOf() used to unlock a cluster that had lost its quorum, there was undefined and unexpected behavior that caused inconsistencies in the cluster. These situations were only visible when autoRejoinTries had a value above zero.

This was caused by an error in AdminAPI that failed to stop group replication for all reachable instances that were not part of the visible membership of the target instance. This was fixed by ensuring that group replication was stopped in these cases.

Error # 30661129 – DBA.UPGRADEMETADATA() AND DBA.REBOOTCLUSTERFROMCOMPLETEOUTAGE() BLOCK EACH OTHER

The previous 8.0.19 release introduced InnoDB ReplicaSet and a new version of the metadata schema. To allow continuous updating, a metadata update command was also added.

If a cluster created using a shell version lower than 8.0.19 is completely shut down and a shell update is performed, the cluster needs to be rebooted after a complete failure and the metadata schema needs to be updated. However, this was not possible due to this error and the user was faced with a “chicken egg” problem as the commands would block each other from executing.

This has been fixed in this release by changing the preconditions of the following commands to allow them to work even if the metadata schema requires an update:

dba.rebootClusterFromCompleteOutage()
.forceQuorumUsingPartitionOf()

Error number 30501628 – REMOVEINSTANCE, SETPRIMARY ETC SHOULD WORK WITH ANY FORM OF ADDRESS

.removeInstance() performed some checks to make sure that the instance being removed was indeed part of the cluster metadata. But in some specific cases, this was not enough, which led to crashes.

For this reason, .removeInstance() has been improved to allow removing an instance from a cluster where report_host does not match what is registered in the metadata, but the address (IP) specified in the command matches.

Error # 30625424 – REMOVEINSTANCE() FORCE: TRUE SAYS INSTANCE REMOVED, BUT NOT REALLY

As in the previous bug, it was impossible to remove OFFLINE, but an achievable instance from the cluster when an address other than the one registered in the metadata was used. This was fixed in this release, allowing such instances to be removed using the force parameter.

Error number 29922719 and Error number 30878446 – REPLICATION COORDINATES LEFT BEHIND AFTER REMOVEINSTANCE

After successful removal of an instance from a cluster, the replication channels that were used by group replication still existed, which allowed transmitting information about the replication coordinates, etc. This was fixed in this release by ensuring that the following channels were reset after Cluster.removeInstance() or Cluster.dissolve():

group_replication_applier
group_replication_recovery

Errors related to InnoDB ReplicaSet

Finally, the InnoDB ReplicaSet has also fixed an important bug:

Error # 30735124 – INNODB REPLICASET: ADMIN API TAKES the WRONG MEMBER AS HAVING LATEST VIEW/PRIMARY

In a very specific situation, it may happen that AdminAPI will select an invalid element as the last primary ReplicaSet set, which will lead to errors in subsequent operations. The main cause of this problem was the process of obtaining the main InnoDB ReplicaSet object, which incorrectly reconnected to the invalid element if it was the last InnoDB ReplicaSet instance. This was fixed and AdminAPI always guarantees that the correct primary one is selected.

MySQL 8.0.20 – New Features Summary

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.