Chapter 21 Using MySQL AdminAPI

To get information about the structure of the InnoDB Cluster itself, use the Cluster.describe() function:

mysql-js> cluster.describe();
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "ic-1:3306",
                "label": "ic-1:3306",
                "role": "HA"
            },
            {
                "address": "ic-2:3306",
                "label": "ic-2:3306",
                "role": "HA"
            },
            {
                "address": "ic-3:3306",
                "label": "ic-3:3306",
                "role": "HA"
            }
        ]
    }
}

The output from this function shows the structure of the InnoDB Cluster including all of its configuration information, and so on. The address, label and role values match those described at Checking a cluster's Status with Cluster.status() .

Cluster objects provide the status() method that enables you to check how a cluster is running. Before you can check the status of the InnoDB Cluster, you need to get a reference to the InnoDB Cluster object by connecting to any of its instances. However, if you want to make changes to the configuration of the cluster, you must connect to a "R/W" instance. Issuing status() retrieves the status of the cluster based on the view of the cluster which the server instance you are connected to is aware of and outputs a status report.

For information about how the InnoDB Cluster is running, use the cluster's status() method:

mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
    "clusterName": "testcluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "ic-1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "ic-1:3306": {
                "address": "ic-1:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "ic-2:3306": {
                "address": "ic-2:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "ic-3:3306": {
                "address": "ic-3:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://icadmin@ic-1:3306"
}

The output of Cluster.status() provides the following information:

To display more information about the cluster use the extended option. From version 8.0.17, the extended option supports integer or Boolean values. To configure the additional information that Cluster.status({'extended':value}) provides, use the following values:

Setting extended using Boolean values is the equivalent of setting the integer values 0 and 1. In versions prior to 8.0.17, the extended option was only Boolean. Similarly prior versions used the queryMembers Boolean option to provide more information about the instances in the cluster, which is the equivalent of setting extended to 3. The queryMembers option is deprecated and scheduled to be removed in a future release.

When you issue Cluster.status({'extended':1}), or the extended option is set to true, the output includes:

To see information about recovery and regular transaction I/O, applier worker thread statistics and any lags; applier coordinator statistics, if parallel apply is enabled; error, and other information from I/O and applier threads issue use the values 2 and 3. A value of 3 is the equivalent of setting the deprecated queryMembers option to true. When you use these values, a connection to each instance in the cluster is opened so that additional instance specific statistics can be queried. The exact statistics that are included in the output depend on the state and configuration of the instance and the server version. This information matches that shown in the replication_group_member_stats table, see the descriptions of the matching columns for more information. Instances which are ONLINE have a transactions section included in the output. Instances which are RECOVERING have a recovery section included in the output. When you set extended to 2, in either case, these sections can contain the following:

When you set extended to 3, the connection section shows information from the replication_connection_status table. The connection section can contain the following:

The currentlyQueueing section has information about the transactions currently queued:

The lastQueued section has information about the most recently queued transaction:

Instances which are using a multithreaded replica have a workers section which contains information about the worker threads, and matches the information shown by the replication_applier_status_by_worker table.

The lastApplied section shows the following information about the last transaction applied by the worker:

The currentlyApplying section shows the following information about the transaction currently being applied by the worker:

The lastProcessed section has the following information about the last transaction processed by the worker:

If parallel applier workers are enabled, then the number of objects in the workers array in transactions or recovery matches the number of configured workers and an additional coordinator object is included. The information shown matches the information in the replication_applier_status_by_coordinator table. The object can contain:

The currentlyProcessing section has the following information about the transaction being processed by the worker:

worker objects have the following information if an error was detected in the replication_applier_status_by_worker table:

connection objects have the following information if an error was detected in the replication_connection_status table:

coordinator objects have the following information if an error was detected in the replication_applier_status_by_coordinator table:

The output of Cluster.status() shows information about the progress of recovery operations for instances in RECOVERING state. Information is shown for instances recovering using either MySQL Clone, or incremental recovery. Monitor these fields:

From MySQL 8.0.16, Group Replication has the concept of a communication protocol for the group, see Section 18.4.1.4, “Setting a Group's Communication Protocol Version” for background information. The Group Replication communication protocol version usually has to be managed explicitly, and set to accommodate the oldest MySQL Server version that you want the group to support. However, InnoDB Cluster automatically and transparently manages the communication protocol versions of its members, whenever the cluster topology is changed using AdminAPI operations. A cluster always uses the most recent communication protocol version that is supported by all the instances that are currently part of the cluster or joining it.

To see the communication protocol version being used in a cluster, use the Cluster.status() function with the extended option enabled. The communication protocol version is returned in the GRProtocolVersion field, provided that the cluster has quorum and no cluster members are unreachable.

The following operations can report information about the MySQL Server version running on the instance:

The behavior varies depending on the MySQL Server version of the Cluster object session.

Before creating a production deployment from server instances you need to check that MySQL on each instance is correctly configured. In addition to dba.configureInstance(), which checks the configuration as part of configuring an instance, you can use the dba.checkInstanceConfiguration(instance) function. This ensures that the instance satisfies the Section 21.2.1, “MySQL InnoDB Cluster Requirements” without changing any configuration on the instance. This does not check any data that is on the instance, see Checking Instance State for more information.

The user which you use to connect to the instance must have suitable privileges, for example as configured at Configuring Users for AdminAPI. The following demonstrates issuing this in a running MySQL Shell:

mysql-js> dba.checkInstanceConfiguration('icadmin@ic-1:3306')
Please provide the password for 'icadmin@ic-1:3306': ***
Validating MySQL instance at ic-1:3306 for use in an InnoDB cluster...

This instance reports its own address as ic-1
Clients and other cluster members will communicate with it through this address by default.
If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             |                | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "1",
            "option": "server_id",
            "required": ""
        }
    ],
    "status": "error"
}

Repeat this process for each server instance that you plan to use as part of your cluster. The report generated after running dba.checkInstanceConfiguration() provides information about any configuration changes required before you can proceed. The action field in the config_error section of the report tells you whether MySQL on the instance requires a restart to detect any change made to the configuration file.

Instances which do not support persisting configuration changes automatically (see Persisting Settings) require you to connect to the server, run MySQL Shell, connect to the instance locally and issue dba.configureLocalInstance(). This enables MySQL Shell to modify the instance's option file after running the following commands against a remote instance:

The recommended method is to log in to the remote machine, for example using SSH, run MySQL Shell as the root user and then connect to the local MySQL server. For example, use the --uri option to connect to the local instance:

shell> sudo -i mysqlsh --uri=instance

Alternatively use the \connect command to log in to the local instance. Then issue dba.configureInstance(instance), where instance is the connection information to the local instance, to persist any changes made to the local instance's option file.

mysql-js> dba.configureLocalInstance('icadmin@ic-2:3306')

Repeat this process for each instance in the cluster which does not support persisting configuration changes automatically. For example if you add 2 instances to a cluster which do not support persisting configuration changes automatically, you must connect to each server and persist the configuration changes required for InnoDB Cluster before the instance restarts. Similarly if you modify the cluster structure, for example changing the number of instances, you need to repeat this process for each server instance to update the InnoDB Cluster metadata accordingly for each instance in the cluster.

The cluster.checkInstanceState() function can be used to verify the existing data on an instance does not prevent it from joining a cluster. This process works by validating the instance's global transaction identifier (GTID) state compared to the GTIDs already processed by the cluster. For more information on GTIDs see Section 17.1.3.1, “GTID Format and Storage”. This check enables you to determine if an instance which has processed transactions can be added to the cluster.

The following demonstrates issuing this in a running MySQL Shell:

mysql-js> cluster.checkInstanceState('icadmin@ic-4:3306')

The output of this function can be one of the following:

Instances with an OK status can be added to the cluster because any data on the instance is consistent with the cluster. In other words the instance being checked has not executed any transactions which conflict with the GTIDs executed by the cluster, and can be recovered to the same state as the rest of the cluster instances.

You can remove an instance from a cluster at any time should you wish to do so. This can be done with the Cluster.removeInstance(instance) method, as in the following example:

mysql-js> cluster.removeInstance('root@localhost:3310')

The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

Attempting to leave from the Group Replication group...

The instance 'localhost:3310' was successfully removed from the cluster.

You can optionally pass in the interactive option to control whether you are prompted to confirm the removal of the instance from the cluster. In interactive mode, you are prompted to continue with the removal of the instance (or not) in case it is not reachable. The cluster.removeInstance() operation ensures that the instance is removed from the metadata of all the cluster members which are ONLINE, and the instance itself.

When the instance being removed has transactions which still need to be applied, AdminAPI waits for up to the number of seconds configured by the MySQL Shell dba.gtidWaitTimeout option for transactions (GTIDs) to be applied. The MySQL Shell dba.gtidWaitTimeout option has a default value of 60 seconds, see Configuring MySQL Shell Options for information on changing the default. If the timeout value defined by dba.gtidWaitTimeout is reached when waiting for transactions to be applied and the force option is false (or not defined) then an error is issued and the remove operation is aborted. If the timeout value defined by dba.gtidWaitTimeout is reached when waiting for transactions to be applied and the force option is set to true then the operation continues without an error and removes the instance from the cluster.

To dissolve an InnoDB Cluster you connect to a read-write instance, for example the primary in a single-primary cluster, and use the Cluster.dissolve() command. This removes all metadata and configuration associated with the cluster, and disables Group Replication on the instances. Any data that was replicated between the instances is not removed.

The Cluster.dissolve() operation can only configure instances which are ONLINE or reachable. If members of a cluster cannot be reached by the member where you issued the Cluster.dissolve() command you have to decide how the dissolve operation should proceed. If there is any chance you want to rejoin any instances that are identified as missing from the cluster, it is strongly recommended to cancel the dissolve operation and first bring the missing instances back online, before proceeding with a dissolve operation. This ensures that all instances can have their metadata updated correctly, and that there is no chance of a split-brain situation. However, if the instances from the cluster which cannot be reached have permanently left the cluster there could be no choice but to force the dissolve operation, which means that the missing instances are ignored and only online instances are affected by the operation.

In interactive mode, if members of a cluster are not reachable during a dissolve operation then an interactive prompt is displayed, for example:

mysql-js> Cluster.dissolve()
The cluster still has the following registered instances:
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "ic-1:3306", 
                "label": "ic-1:3306", 
                "role": "HA"
            }, 
            {
                "address": "ic-2:3306", 
                "label": "ic-2:3306", 
                "role": "HA"
            }, 
            {
                "address": "ic-3:3306", 
                "label": "ic-3:3306", 
                "role": "HA"
            }
        ]
    }
}
WARNING: You are about to dissolve the whole cluster and lose the high
availability features provided by it. This operation cannot be reverted. All
members will be removed from the cluster and replication will be stopped,
internal recovery user accounts and the cluster metadata will be dropped. User
data will be maintained intact in all instances.

Are you sure you want to dissolve the cluster? [y/N]: y

ERROR: The instance 'ic-2:3306' cannot be removed because it is on a '(MISSING)'
state. Please bring the instance back ONLINE and try to dissolve the cluster
again. If the instance is permanently not reachable, then you can choose to
proceed with the operation and only remove the instance from the Cluster
Metadata.

Do you want to continue anyway (only the instance metadata will be removed)?
[y/N]: y

Instance 'ic-3:3306' is attempting to leave the cluster...  Instance 'ic-1:3306'
is attempting to leave the cluster...

WARNING: The cluster was successfully dissolved, but the following instance was
skipped: 'ic-2:3306'. Please make sure this instance is permanently unavailable
or take any necessary manual action to ensure the cluster is fully dissolved.

In this example, the cluster consisted of three instances, one of which was offline when dissolve was issued. The error is caught, and you are given the choice how to proceed. In this case the missing ic-2 instance is ignored and the reachable members have their metadata updated.

When MySQL Shell is running in non-interactive mode, for example when running a batch file, you can configure the behavior of the Cluster.dissolve() operation using the force option. To force the dissolve operation to ignore any instances which are unreachable, issue:

mysql-js> Cluster.dissolve({force: true})

Any instances which can be reached are removed from the cluster, and any unreachable instances are ignored. The warnings in this section about forcing the removal of missing instances from a cluster apply equally to this technique of forcing the dissolve operation.

You can also use the interactive option with the Cluster.dissolve() operation to override the mode which MySQL Shell is running in, for example to make the interactive prompt appear when running a batch script. For example:

mysql-js> Cluster.dissolve({interactive: true})

The dba.gtidWaitTimeout MySQL Shell option configures how long the Cluster.dissolve() operation waits for cluster transactions to be applied before removing a target instance from the cluster, but only if the target instance is ONLINE. An error is issued if the timeout is reached when waiting for cluster transactions to be applied on any of the instances being removed, except if force: true is used, which skips the error in that case.

By default, an InnoDB Cluster runs in single-primary mode, where the cluster has one primary server that accepts read and write queries (R/W), and all of the remaining instances in the cluster accept only read queries (R/O). When you configure a cluster to run in multi-primary mode, all of the instances in the cluster are primaries, which means that they accept both read and write queries (R/W). If a cluster has all of its instances running MySQL server version 8.0.15 or later, you can make changes to the topology of the cluster while the cluster is online. In previous versions it was necessary to completely dissolve and re-create the cluster to make the configuration changes. This uses the group action coordinator exposed through the UDFs described at Section 18.4.1, “Configuring an Online Group”, and as such you should observe the rules for configuring online groups.

Usually a single-primary cluster elects a new primary when the current primary leaves the cluster unexpectedly, for example due to an unexpected halt. The election process is normally used to choose which of the current secondaries becomes the new primary. To override the election process and force a specific server to become the new primary, use the Cluster.setPrimaryInstance(instance) function, where instance specifies the connection to the instance which should become the new primary. This enables you to configure the underlying Group Replication group to choose a specific instance as the new primary, bypassing the election process.

You can change the mode (sometimes described as the topology) which a cluster is running in between single-primary and multi-primary using the following operations:

You can check and modify the settings in place for an InnoDB Cluster while the instances are online. To check the current settings of a cluster, use the following operation:

You can configure the options of an InnoDB Cluster at a cluster level or instance level, while instances remain online. This avoids the need to remove, reconfigure and then again add the instance to change InnoDB Cluster options. Use the following operations:

The way which you use InnoDB Cluster options with the operations listed depends on whether the option can be changed to be the same on all instances or not. These options are changeable at both the cluster (all instances) and per instance level:

This option is changeable at the per instance level only:

These options are changeable at the cluster level only:

When you create a cluster and add instances to it, values such as the group name, the local address, and the seed instances are configured automatically by AdminAPI. These default values are recommended for most deployments, but advanced users can override the defaults by passing the following options to the dba.createCluster() and Cluster.addInstance().

To customize the name of the replication group created by InnoDB Cluster, pass the groupName option to the dba.createCluster() command. This sets the group_replication_group_name system variable. The name must be a valid UUID.

To customize the address which an instance provides for connections from other instances, pass the localAddress option to the dba.createCluster() and cluster.addInstance() commands. Specify the address in the format host:port. This sets the group_replication_local_address system variable on the instance. The address must be accessible to all instances in the cluster, and must be reserved for internal cluster communication only. In other words do not use this address for communication with the instance.

To customize the instances used as seeds when an instance joins the cluster, pass the groupSeeds option to the dba.createCluster() and Cluster.addInstance() operations. Seed instances are contacted when a new instance joins a cluster and are used to provide data to the new instance. The addresses of the seed instances are specified as a comma separated list such as host1:port1,host2:port2. This configures the group_replication_group_seeds system variable. When a new instance is added to a cluster, the local address of this instance is automatically appended to the list of group seeds of all online cluster members in order to allow them to use the new instance to rejoin the group if necessary.

For more information see the documentation of the system variables configured by these AdminAPI options.

You can optionally configure how a single-primary cluster elects a new primary, for example to prefer one instance as the new primary to fail over to. Use the memberWeight option and pass it to the dba.createCluster() and Cluster.addInstance() methods when creating your cluster. The memberWeight option accepts an integer value between 0 and 100, which is a percentage weight for automatic primary election on failover. When an instance has a higher precentage number set by memberWeight, it is more likely to be elected as primary in a single-primary cluster. When a primary election takes place, if multiple instances have the same memberWeight value, the instances are then prioritized based on their server UUID in lexicographical order (the lowest) and by picking the first one.

Setting the value of memberWeight configures the group_replication_member_weight system variable on the instance. Group Replication limits the value range from 0 to 100, automatically adjusting it if a higher or lower value is provided. Group Replication uses a default value of 50 if no value is provided. See Section 18.1.3.1, “Single-Primary Mode” for more information.

For example to configure a cluster where ic-3 is the preferred instance to fail over to in the event that ic-1, the current primary, leaves the cluster unexpectedly use memberWeight as follows:

dba.createCluster('cluster1', {memberWeight:35})
var mycluster = dba.getCluster()
mycluster.addInstance('icadmin@ic2', {memberWeight:25})
mycluster.addInstance('icadmin@ic3', {memberWeight:50})

Group Replication provides the ability to specify the failover guarantees (eventual or “read your writes”) if a primary failover happens in single-primary mode (see Section 18.4.2.2, “Configuring Transaction Consistency Guarantees”). You can configure the failover guarantees of an InnoDB Cluster at creation by passing the consistency option (prior to version 8.0.16 this option was the failoverConsistency option, which is now deprecated) to the dba.createCluster() operation, which configures the group_replication_consistency system variable on the seed instance. This option defines the behavior of a new fencing mechanism used when a new primary is elected in a single-primary group. The fencing restricts connections from writing and reading from the new primary until it has applied any pending backlog of changes that came from the old primary (sometimes referred to as “read your writes”). While the fencing mechanism is in place, applications effectively do not see time going backward for a short period of time while any backlog is applied. This ensures that applications do not read stale information from the newly elected primary.

The consistency option is only supported if the target MySQL server version is 8.0.14 or later, and instances added to a cluster which has been configured with the consistency option are automatically configured to have group_replication_consistency the same on all cluster members that have support for the option. The variable default value is controlled by Group Replication and is EVENTUAL, change the consistency option to BEFORE_ON_PRIMARY_FAILOVER to enable the fencing mechanism. Alternatively use consistency=0 for EVENTUAL and consistency=1 for BEFORE_ON_PRIMARY_FAILOVER.

Instances running MySQL 8.0.16 and later support the Group Replication automatic rejoin functionality, which enables you to configure instances to automatically rejoin the cluster after being expelled. See Section 18.6.6, “Responses to Failure Detection and Network Partitioning” for background information. AdminAPI provides the autoRejoinTries option to configure the number of tries instances make to rejoin the cluster after being expelled. By default instances do not automatically rejoin the cluster. You can configure the autoRejoinTries option at either the cluster level or for an individual instance using the following commands:

The autoRejoinTries option accepts positive integer values between 0 and 2016 and the default value is 0, which means that instances do not try to automatically rejoin. When you are using the automatic rejoin functionality, your cluster is more tolerant to faults, especially temporary ones such as unreliable networks. But if quorum has been lost, you should not expect members to automatically rejoin the cluster, because majority is required to rejoin instances.

Instances running MySQL version 8.0.12 and later have the group_replication_exit_state_action variable, which you can configure using the AdminAPI exitStateAction option. This controls what instances do in the event of leaving the cluster unexpectedly. By default the exitStateAction option is READ_ONLY, which means that instances which leave the cluster unexpectedly become read-only. If exitStateAction is set to OFFLINE_MODE (available from MySQL 8.0.18), instances which leave the cluster unexpectedly become read-only and also enter offline mode, where they disconnect existing clients and do not accept new connections (except from clients with administrator privileges). If exitStateAction is set to ABORT_SERVER then in the event of leaving the cluster unexpectedly, the instance shuts down MySQL, and it has to be started again before it can rejoin the cluster. Note that when you are using the automatic rejoin functionality, the action configured by the exitStateAction option only happens in the event that all attempts to rejoin the cluster fail.

There is a chance you might connect to an instance and try to configure it using the AdminAPI, but at that moment the instance could be rejoining the cluster. This could happen whenever you use any of these operations:

These operations might provide extra information while the instance is automatically rejoining the cluster. In addition, when you are using Cluster.removeInstance(), if the target instance is automatically rejoining the cluster the operation aborts unless you pass in force:true.

Server instances can be configured to use secure connections. For general information on using secure connections with MySQL see Section 6.3, “Using Encrypted Connections”. This section explains how to configure a cluster to use encrypted connections. An additional security possibility is to configure which servers can access the cluster, see Creating a Whitelist of Servers.

When using dba.createCluster() to set up a cluster, if the server instance provides encryption then it is automatically enabled on the seed instance. Pass the memberSslMode option to the dba.createCluster() method to specify a different SSL mode. The SSL mode of a cluster can only be set at the time of creation. The memberSslMode option is a string that configures the SSL mode to be used, it defaults to AUTO. The permitted values are DISABLED, REQUIRED, and AUTO. These modes are defined as:

When you issue the cluster.addInstance() and cluster.rejoinInstance() commands, SSL encryption on the instance is enabled or disabled based on the setting found for the seed instance.

When using createCluster() with the adoptFromGR option to adopt an existing Group Replication group, no SSL settings are changed on the adopted cluster:

MySQL Shell always enables or disables SSL for the cluster for both Group Replication recovery and Group Communication, see Section 18.5.2, “Securing Group Communication Connections with Secure Socket Layer (SSL)”. A verification is performed and an error issued in case those settings are different for the seed instance (for example as the result of a dba.createCluster() using adoptFromGR) when adding a new instance to the cluster. SSL encryption must be enabled or disabled for all instances in the cluster. Verifications are performed to ensure that this invariant holds when adding a new instance to the cluster.

The dba.deploySandboxInstance() command attempts to deploy sandbox instances with SSL encryption support by default. If it is not possible, the server instance is deployed without SSL support. Use the ignoreSslError option set to false to ensure that sandbox instances are deployed with SSL support, issuing an error if SSL support cannot be provided. When ignoreSslError is true, which is the default, no error is issued during the operation if the SSL support cannot be provided and the server instance is deployed without SSL support.

When using a cluster's createCluster(), addInstance(), and rejoinInstance() methods you can optionally specify a list of approved servers that belong to the cluster, referred to as a whitelist. By specifying the whitelist explicitly in this way you can increase the security of your cluster because only servers in the whitelist can connect to the cluster. Using the ipWhitelist option configures the group_replication_ip_whitelist system variable on the instance. By default, if not specified explicitly, the whitelist is automatically set to the private network addresses that the server has network interfaces on. To configure the whitelist, specify the servers to add with the ipWhitelist option when using the method. IP addresses must be specified in IPv4 format. Pass the servers as a comma separated list, surrounded by quotes. For example:

mysql-js> cluster.addInstance("icadmin@ic-3:3306", {ipWhitelist: "203.0.113.0/24, 198.51.100.110"})

This configures the instance to only accept connections from servers at addresses 203.0.113.0/24 and 198.51.100.110. The whitelist can also include host names, which are resolved only when a connection request is made by another server.

If an instance leaves the cluster, for example because it lost connection, and for some reason it could not automatically rejoin the cluster, it might be necessary to rejoin it to the cluster at a later stage. To rejoin an instance to a cluster issue Cluster.rejoinInstance(instance).

In the case where an instance has not had its configuration persisted (see Persisting Settings), upon restart the instance does not rejoin the cluster automatically. The solution is to issue cluster.rejoinInstance() so that the instance is added to the cluster again and ensure the changes are persisted. Once the InnoDB Cluster configuration is persisted to the instance's option file it rejoins the cluster automatically.

If you are rejoining an instance which has changed in some way then you might have to modify the instance to make the rejoin process work correctly. For example, when you restore a MySQL Enterprise Backup backup, the server_uuid changes. Attempting to rejoin such an instance fails because InnoDB Cluster instances are identified by the server_uuid variable. In such a situation, information about the instance's old server_uuid must be removed from the InnoDB Cluster metadata and then a Cluster.rescan() must be executed to add the instance to the metadata using it's new server_uuid. For example:

cluster.removeInstance("root@instanceWithOldUUID:3306", {force: true})

cluster.rescan()

In this case you must pass the force option to the Cluster.removeInstance() method because the instance is unreachable from the cluster's perspective and we want to remove it from the InnoDB Cluster metadata anyway.

If an instance (or instances) fail, then a cluster can lose its quorum, which is the ability to vote in a new primary. This can happen when there is a failure of enough instances that there is no longer a majority of the instances which make up the cluster to vote on Group Replication operations. See Section 18.1.4.3, “Fault-tolerance”. When a cluster loses quorum you can no longer process write transactions with the cluster, or change the cluster's topology, for example by adding, rejoining, or removing instances. However if you have an instance online which contains the InnoDB Cluster metadata, it is possible to restore a cluster with quorum. This assumes you can connect to an instance that contains the InnoDB Cluster metadata, and that instance can contact the other instances you want to use to restore the cluster.

Connect to an instance which contains the cluster's metadata, then use the Cluster.forceQuorumUsingPartitionOf(instance) operation, which restores the cluster based on the metadata on instance, and then all the instances that are ONLINE from the point of view of the given instance definition are added to the restored cluster.

mysql-js> cluster.forceQuorumUsingPartitionOf("icadmin@ic-1:3306")

  Restoring replicaset 'default' from loss of quorum, by using the partition composed of [icadmin@ic-1:3306]

  Please provide the password for 'icadmin@ic-1:3306': ******
  Restoring the InnoDB cluster ...

  The InnoDB cluster was successfully restored using the partition from the instance 'icadmin@ic-1:3306'.

  WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset
  are removed or joined back to the group that was restored.

In the event that an instance is not automatically added to the cluster, for example if its settings were not persisted, use Cluster.rejoinInstance() to manually add the instance back to the cluster.

The restored cluster might not, and does not have to, consist of all of the original instances which made up the cluster. For example, if the original cluster consisted of the following five instances:

and the cluster experiences a split-brain scenario, with ic-1, ic-2, and ic-3 forming one partition while ic-4 and ic-5 form another partition. If you connect to ic-1 and issue Cluster.forceQuorumUsingPartitionOf('icadmin@ic-1:3306') to restore the cluster the resulting cluster would consist of these three instances:

because ic-1 sees ic-2 and ic-3 as ONLINE and does not see ic-4 and ic-5.

If your cluster suffers from a complete outage, you can ensure it is reconfigured correctly using dba.rebootClusterFromCompleteOutage(). This operation takes the instance which MySQL Shell is currently connected to and uses its metadata to recover the cluster. In the event that a cluster's instances have completely stopped, the instances must be started and only then can the cluster be started. For example if the machine a sandbox cluster was running on has been restarted, and the instances were at ports 3310, 3320 and 3330, issue:

mysql-js> dba.startSandboxInstance(3310)
mysql-js> dba.startSandboxInstance(3320)
mysql-js> dba.startSandboxInstance(3330)
    

This ensures the sandbox instances are running. In the case of a production deployment you would have to start the instances outside of MySQL Shell. Once the instances have started, you need to connect to an instance with the GTID superset, which means the instance which had applied the most transaction before the outage. If you are unsure which instance contains the GTID superset, connect to any instance and follow the interactive messages from the dba.rebootClusterFromCompleteOutage(), which detects if the instance you are connected to contains the GTID superset. Reboot the cluster by issuing:

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

The dba.rebootClusterFromCompleteOutage() operation then follows these steps to ensure the cluster is correctly reconfigured:

If you are not using MySQL Shell's interactive mode, you can use the rejoinInstances and removeInstances options to manually configure instances which should be joined or removed during the reboot of the cluster.

If you encounter an error such as The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. then the instance you are connected to does not have the GTID superset of transactions applied by the cluster. In this situation, connect MySQL Shell to the instance suggested in the error message and issue dba.rebootClusterFromCompleteOutage() from that instance.

mysql-sql> SHOW VARIABLES LIKE 'gtid_executed';

If this process fails, and the cluster metadata has become badly corrupted, you might need to drop the metadata and create the cluster again from scratch. You can drop the cluster metadata using dba.dropMetadataSchema().

If you are using MySQL Router with the cluster, when you drop the metadata, all current connections are dropped and new connections are forbidden. This causes a full outage.

If you make configuration changes to a cluster outside of the AdminAPI commands, for example by changing an instance's configuration manually to resolve configuration issues or after the loss of an instance, you need to update the InnoDB Cluster metadata so that it matches the current configuration of instances. In these cases, use the Cluster.rescan() operation, which enables you to update the InnoDB Cluster metadata either manually or using an interactive wizard. The Cluster.rescan() operation can detect new active instances that are not registered in the metadata and add them, or obsolete instances (no longer active) still registered in the metadata, and remove them. You can automatically update the metadata depending on the instances found by the command, or you can specify a list of instance addresses to either add to the metadata or remove from the metadata. You can also update the topology mode stored in the metadata, for example after changing from single-primary mode to multi-primary mode outside of AdminAPI.

The syntax of the command is Cluster.rescan([options]). The options dictionary supports the following:

The Cluster.options() operation shows information about the tags assigned to individual cluster instances as well as to the cluster itself. For example, the InnoDB Cluster assigned to myCluster could show:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1", 
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden", 
                    "value": true
                },
                {
                    "option": "_hidden", 
                    "value": false
                }
            ], 
            "ic-2:3306": [], 
            "ic-3:3306": [], 
            "global": [
                {
                    "option": "location:", 
                    "value": "US East"
                }
            ]
        }
    }
}

This cluster has a global tag named location which has the value US East, and instance ic-1 has been tagged.

You can set tags at the instance level, which enables you for example to mark an instance as not available, so that applications and router treat it as offline. Use the Cluster.setInstanceOption(instance, option, value) operation to set the value of a tag for the instance. The instance argument is a connection string to the target instance. The option argument must be a string with the format namespace:option. The value parameter is the value that should be assigned to option in the specified namespace. If the value is null, the option is removed from the specified namespace. For instances which belong to a cluster, the setInstanceOption() operation only accepts the tag namespace. Any other namespace results in an ArgumentError.

For example, to set the tag test to true on the myCluster instance ic-1, issue:

mysql-js> myCluster.setInstanceOption(icadmin@ic-1:3306, "tag:test", true);

When AdminAPI and MySQL Router are working together, they support specific tags that enable you to mark instances as hidden and remove them from routing. MySQL Router then excludes such tagged instances from the routing destination candidates list. This enables you to safely take a server instance offline, so that applications and MySQL Router ignore it, for example while you perform maintenance tasks, such as server upgrade or configuration changes.

When the _hidden tag is set to true, this instructs MySQL Router to exclude the instance from the list of possible destinations for client applications. The instance remains online, but is not routed to for new incoming connections. The _disconnect_existing_sessions_when_hidden tag controls how existing connections to the instance are closed. This tag is assumed to be true, and it instructs any MySQL Routers bootstrapped against the InnoDB Cluster or InnoDB ReplicaSet to disconnect any existing connections from the instance when the _hidden tag is true. When _disconnect_existing_sessions_when_hidden is false, any existing client connections to the instance are not closed if _hidden is true.

For example, suppose the you want to remove the ic-1 instance which is part of an InnoDB Cluster assigned to myCluster from the routing destinations. Use the setInstanceOption() operation to enable the _hidden and _disconnect_existing_sessions_when_hidden tags:

mysql-js> myCluster.setInstanceOption(icadmin@ic-1:3306, "tag:_hidden", true);

You can verify the change in the metadata by checking the options. For example the change made to ic-1 would show in the options as:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1", 
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden", 
                    "value": true
                },
                {
                    "option": "_hidden", 
                    "value": true
                }
            ], 
            "ic-2:3306": [], 
            "ic-3:3306": [], 
            "global": []
        }
    }
}

You can verify that MySQL Router has detected the change in the metadata by viewing the log file. A MySQL Router that has detected the change made to ic-1 would show a change such as:

2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700] Potential changes detected in cluster 'testCluster' after metadata refresh
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700] view_id = 4, (3 members)
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RW 
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RO 
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RO hidden=yes disconnect_when_hidden=yes
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_x_ro listening on 64470 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_x_rw listening on 64460 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_rw listening on 6446 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_ro listening on 6447 got request to disconnect invalid connections: metadata change

To bring the instance back online, use the setInstanceOption() operation to remove the tags, and MySQL Router automatically adds the instance back to the routing destinations, and it becomes online for applications. For example:

mysql-js> myCluster.setInstanceOption(icadmin@ic-1:3306, "tag:_hidden", false);

Verify the change in the metadata by checking the options again:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1", 
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden", 
                    "value": true
                },
                {
                    "option": "_hidden", 
                    "value": false
                }
            ], 
            "ic-2:3306": [], 
            "ic-3:3306": [], 
            "global": []
        }
    }
}

The Cluster.setOption(option, value) operation enables you to change the value of a namespace option for the whole cluster. The option argument must be a string with the format namespace:option. The value parameter is the value to be assigned to option in the specified namespace. If the value is null, the option is removed from the specified namespace. For Clusters, the setOption() operation accepts the tag namespace. Any other namespace results in an ArgumentError.

There is no requirement for all the instances to be online, only that the cluster has quorum. To tag the InnoDB Cluster assigned to myCluster with the location tag set to US East, issue:

mysql-js> myCluster.setOption("tag:location", "US East")
mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1", 
        "tags": {
            "ic-1:3306": [],
            "ic-2:3306": [], 
            "ic-3:3306": [], 
            "global": [
                {
                    "option": "location:", 
                    "value": "US East"
                }
            ]
        }
    }
}

AdminAPI supports the tag namespace, where you can store information in the key-value pairs associated with a given Cluster, ReplicaSet or instance. The options under the tag namespace are not constrained, meaning you can tag with whatever information you choose, as long as it is a valid MySQL ASCII identifier. You can use any name and value for a tag, as long as the name follows the following syntax: _ or letters followed by alphanumeric and _ characters.

The namespace option is a colon separated string with the format namespace:option, where namespace is the name of the namespace and option is the actual option name. You can set and remove tags at the instance level, or at the Cluster or ReplicaSet level.

Tag names can have any value as long as it starts with a letter or underscore, optionally followed by alphanumeric and _ characters, for example, ^[a-zA-Z_][0-9a-zA-Z_]*. Only built-in tags are allowed to start with the underscore _ character.

How you use custom tags is up to you. You could set a custom tag on a Cluster to mark the region which it is operating in. For example, you could set a custom tag named location, with a value of EMEA on the cluster.

Whenever Group Replication stops, the super_read_only variable is set to ON to ensure no writes are made to the instance. When you try to use such an instance with the following AdminAPI commands you are given the choice to set super_read_only=OFF on the instance:

When AdminAPI encounters an instance which has super_read_only=ON, in interactive mode you are given the choice to set super_read_only=OFF. For example:

mysql-js> var myCluster = dba.dropMetadataSchema()
Are you sure you want to remove the Metadata? [y/N]: y
The MySQL instance at 'localhost:3310' currently has the super_read_only system
variable set to protect it from inadvertent updates from applications. You must
first unset it to be able to perform any changes to this instance.
For more information see:
https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.

Do you want to disable super_read_only and continue? [y/N]: y

Metadata Schema successfully removed. 

The number of current active sessions to the instance is shown. You must ensure that no applications can write to the instance inadvertently. By answering y you confirm that AdminAPI can write to the instance. If there is more than one open session to the instance listed, exercise caution before permitting AdminAPI to set super_read_only=OFF.

Working with instances that belong to an InnoDB Cluster or InnoDB ReplicaSet requires that you connect to the instances with a user that has the correct privileges to administer the instances. AdminAPI provides the following ways to administer suitable users:

For more information, see Creating User Accounts for Administration. If you want to manually configure an administration user, that user requires the following privileges, all with GRANT OPTION:

If only read operations are needed, for example to create a user for monitoring purposes, an account with more restricted privileges can be used. To give the user your_user the privileges needed to monitor InnoDB Cluster issue:

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO your_user@'%';
GRANT SELECT ON performance_schema.global_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO your_user@'%';
GRANT SELECT ON performance_schema.threads TO your_user@'%' WITH GRANT OPTION; 

For more information, see Section 13.7.1, “Account Management Statements”.

When you are using an instance as part of an InnoDB Cluster, the auto_increment_increment and auto_increment_offset variables are configured to avoid the possibility of auto increment collisions for multi-primary clusters up to a size of 9 (the maximum supported size of a Group Replication group). The logic used to configure these variables can be summarized as:

In MySQL 8, the binary log is automatically purged (as defined by binlog_expire_logs_seconds). This means that a cluster which has been running for a longer time than binlog_expire_logs_seconds could eventually not contain an instance with a complete binary log that contains all of the transactions applied by the instances. This could result in instances needing to be provisioned automatically, for example using MySQL Enterprise Backup, before they could join the cluster. Instances running 8.0.17 and later support the MySQL Clone plugin, which resolves this issue by providing an automatic provisioning solution which does not rely on incremental recovery, see Section 21.2.2.2, “Using MySQL Clone with InnoDB Cluster”. Instances running a version earlier than 8.0.17 only support incremental recovery, and the result is that, depending on which version of MySQL the instance is running, instances might have to be provisioned automatically. Otherwise operations which rely on distributed recovery, such as Cluster.addInstance() and so on might fail.

On instances running earlier versions of MySQL the following rules are used for binary log purging:

Thus, depending on how long the cluster has been running binary logs could have been purged and you might have to provision instances manually. Similarly, if you manually purged binary logs you could encounter the same situation. Therefore you are strongly advised to upgrade to a version of MySQL later than 8.0.17 to take full advantage of the automatic provisioning provided by MySQL Clone for distributed recovery, and to minimize downtime while provisioning instances for your InnoDB Cluster.

From version 8.0.18, you can use the Cluster.resetRecoveryAccountsPassword() operation to reset the passwords for the internal recovery accounts created by InnoDB Cluster, for example to follow a custom password lifetime policy. Use the Cluster.resetRecoveryAccountsPassword() operation to reset the passwords for all internal recovery accounts used by the cluster. The operation sets a new random password for the internal recovery account on each instance which is online. If an instance cannot be reached, the operation fails. You can use the force option to ignore such instances, but this is not recommended, and it is safer to bring the instance back online before using this operation. This operation only applies to the passwords created by InnoDB cluster and cannot be used to update manually created passwords.

An InnoDB ReplicaSet has several limitations compared to an InnoDB Cluster and thus, it is recommended that you deploy InnoDB Cluster wherever possible. Generally, an InnoDB ReplicaSet on its own does not provide high availability. Among the limitations of InnoDB ReplicaSet are:

To use InnoDB ReplicaSet you should be aware of the following prerequisites:

AdminAPI and InnoDB ReplicaSet enable you to work with MySQL replication without a deep understanding of the underlying concepts. However, for background information see Chapter 17, Replication.

Use dba.configureReplicaSetInstance(instance) to configure each instance you want to use in your replica set. MySQL Shell can either connect to an instance and then configure it, or you can pass in instance to configure a specific remote instance. To use an instance in a ReplicaSet, it must support persisting settings. See Persisting Settings.

When you connect to the instance for administration tasks you require a user with suitable privileges. The preferred method to create users to administer a ReplicaSet is using the setupAdminAccount() operation. See Creating User Accounts for Administration. Alternatively, the dba.configureReplicaSetInstance() operation can optionally create an administrator account, if the clusterAdmin option is provided. The account is created with the correct set of privileges required to manage InnoDB ReplicaSet.

To configure the instance at rs-1:3306, with a cluster administrator named rsadmin issue:

mysql-js> dba.configureReplicaSetInstance('root@rs-1:3306', {clusterAdmin: "'rsadmin'@'rs-1%'"});

The interactive prompt requests the password required by the specified user. To configure the instance MySQL Shell is currently connected to, you can specify a null instance definition. For example issue:

mysql-js> dba.configureReplicaSetInstance('', {clusterAdmin: "'rsadmin'@'rs-1%'"});

The interactive prompt requests the password required by the specified user. This checks the instance which MySQL Shell is currently connected to is valid for use in an InnoDB ReplicaSet. Settings which are not compatible with InnoDB ReplicaSet are configured if possible. The cluster administrator account is created with the privileges required for InnoDB ReplicaSet.

Once you have configured your instances, connect to an instance and use dba.createReplicaSet() to create a managed ReplicaSet that uses MySQL asynchronous replication, as opposed to MySQL Group Replication used by InnoDB Cluster. The MySQL instance which MySQL Shell is currently connected to is used as the initial primary of the ReplicaSet.

The dba.createReplicaSet() operation performs several checks to ensure that the instance state and configuration are compatible with a managed ReplicaSet and if so, a metadata schema is initialized on the instance. If you want to check the operation but not actually make any changes to the instances, use the dryRun option. This checks and shows what actions the MySQL Shell would take to create the ReplicaSet. If the ReplicaSet is created successfully, a ReplicaSet object is returned. Therefore it is best practice to assign the returned ReplicaSet to a variable. This enables you to work with the ReplicaSet, for example by calling the ReplicaSet.status() operation. To create a ReplicaSet named example on instance rs-1 and assign it to the rs variable, issue:

mysql-js> \connect root@rs-1:3306
...
mysql-js> var rs = dba.createReplicaSet("example")
A new replicaset with instance 'rs-1:3306' will be created.

* Checking MySQL instance at rs-1:3306

This instance reports its own address as rs-1:3306
rs-1:3306: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for rs-1:3306.
Use rs.add_instance() to add more asynchronously replicated instances to this replicaset
and rs.status() to check its status.

To verify that the operation was successful, you work with the returned ReplicaSet object. For example this provides the ReplicaSet.status() operation, which displays information about the ReplicaSet. We already assigned the returned ReplicaSet to the variable rs, so issue:

mysql-js> rs.status()
{
    "replicaSet": {
        "name": "example", 
        "primary": "rs-1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "rs-1:3306": {
                "address": "rs-1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

This output shows that the ReplicaSet named example has been created, and that the primary is rs-1. Currently there is only one instance, and the next task is to add more instances to the ReplicaSet.

When new instances are added to an InnoDB ReplicaSet they need to be provisioned with the existing data which it contains. This can be done automatically using one of the following methods:

When an instance is joining a ReplicaSet, recovery is used in much the same way that it is in InnoDB Cluster. MySQL Shell attempts to automatically select a suitable recovery method. If it is not possible to choose a method safely, MySQL Shell prompts for what to use. For more information, see Section 21.2.2.2, “Using MySQL Clone with InnoDB Cluster”. This section covers the differences when adding instances to a ReplicaSet.

Use the ReplicaSet.addInstance(instance) operation to add secondary instances to the ReplicaSet. You specify the instance as a URI-like connection string. The user you specify must have the privileges required and must be the same on all instances in the ReplicaSet, see Configuring InnoDB ReplicaSet Instances.

For example, to add the instance at rs-2 with user rsadmin, issue:

mysql-js> rs.addInstance('rsadmin@rs-2')

Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as rsadmin@rs-2
rsadmin@rs-2: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'rsadmin@rs-2' has not been pre-provisioned (GTID set
is empty). The Shell is unable to decide whether replication can completely
recover its state.  The safest and most convenient way to provision a new
instance is through automatic clone provisioning, which will completely
overwrite the state of 'rsadmin@rs-2' with a physical snapshot from an existing
replicaset member. To use this method by default, set the 'recoveryMethod'
option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the
state of the new instance if you are sure all updates ever executed in the
replicaset were done with GTIDs enabled, there are no purged transactions and
the new instance contains the same GTID set as the replicaset or a subset of it.
To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):

In this case we did not specify the recovery method, so the operation advises you on how to best proceed. In this example we choose the clone option because we do not have any existing transactions on the instance joining the ReplicaSet. Therefore there is no risk of deleting data from the joining instance.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: rsadmin@rs-2 is being cloned from rsadmin@rs-1
** Stage DROP DATA: Completed
** Clone Transfer  
FILE COPY  ############################################################  100%  Completed
PAGE COPY  ############################################################  100%  Completed
REDO COPY  ############################################################  100%  Completed
** Stage RECOVERY: \
NOTE: rsadmin@rs-2 is shutting down...

* Waiting for server restart... ready
* rsadmin@rs-2 has restarted, waiting for clone to finish...
* Clone process has finished: 59.63 MB transferred in about 1 second (~1.00 B/s)

** Configuring rsadmin@rs-2 to replicate from rsadmin@rs-1
** Waiting for new instance to synchronize with PRIMARY...

The instance 'rsadmin@rs-2' was added to the replicaset and is replicating from rsadmin@rs-1.

Assuming the instance is valid for InnoDB ReplicaSet usage, recovery proceeds. In this case the newly joining instance uses MySQL Clone to copy all of the transactions it has not yet applied from the primary, then it joins the ReplicaSet as an online instance. To verify, use the ReplicaSet.status() operation:

mysql-js> rs.status()
{    
    "replicaSet": {
        "name": "example", 
        "primary": "rs-1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "rs-1:3306": {
                "address": "rs-1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "rs-2:3306": {
                "address": "rs-2:3306", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Slave has read all relay log; waiting for more updates", 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

This output shows that the ReplicaSet named example now consists of two MySQL instances, and that the primary is rs-1. Currently there is one secondary instance at rs-2, which is a replica of the primary. The ReplicaSet is online, which means that the primary and secondary are in synchrony. At this point the ReplicaSet is ready to process transactions.

If you want to override the interactive MySQL Shell mode trying to choose the most suitable recovery method, use the recoveryMethod option to configure how the instance recovers the data required to be able to join the ReplicaSet. For more information, see Section 21.2.2.2, “Using MySQL Clone with InnoDB Cluster”.

Use the ReplicaSet.setPrimaryInstance() operation to safely perform a change of the primary of a ReplicaSet to another instance. The current primary is demoted to a secondary and made read-only, while the promoted instance becomes the new primary and is made read-write. All other secondary instances are updated to replicate from the new primary. MySQL Router instances which have been bootstrapped against the ReplicaSet automatically start redirecting read-write clients to the new primary.

For a safe change of the primary to be possible, all replica set instances must be reachable by MySQL Shell and have consistent GTID_EXECUTED sets. If the primary is not available, and there is no way to restore it, a forced failover might be the only option instead, see Forcing the Primary Instance in a ReplicaSet.

During a change of primary, the promoted instance is synchronized with the old primary, ensuring that all transactions present on the primary are applied before the topology change is committed. If this synchronization step takes too long or is not possible on any of the secondary instances, the operation is aborted. In such a situation, these problematic secondary instances must be either repaired or removed from the ReplicaSet for the fail over to be possible.

Unlike InnoDB Cluster, which supports automatic failover in the event of an unexpected failure of the primary, InnoDB ReplicaSet does not have automatic failure detection or a consensus based protocol such as that provided by Group Replication. If the primary is not available, a manual failover is required. An InnoDB ReplicaSet which has lost its primary is effectively read-only, and for any write changes to be possible a new primary must be chosen. In the event that you cannot connect to the primary, and you cannot use ReplicaSet.setPrimaryInstance() to safely perform a switchover to a new primary as described at Planned Changes of the ReplicaSet Primary, use the ReplicaSet.forcePrimaryInstance() operation to perform a forced failover of the primary. This is a last resort operation that must only be used in a disaster type scenario where the current primary is unavailable and cannot be restored in any way.

If a target instance is not given (or is null), the most up-to-date instance is automatically selected and promoted to be the new primary. If a target instance is provided, it is promoted to a primary, while other reachable secondary instances are switched to replicate from the new primary. The target instance must have the most up-to-date GTID_EXECUTED set among reachable instances, otherwise the operation fails.

A failover is different from a planned primary change because it promotes a secondary instance without synchronizing with or updating the old primary. That has the following major consequences:

If the last known primary is still reachable, the ReplicaSet.forcePrimaryInstance() operation fails, to reduce the risk of split-brain situations. But it is the administrator's responsibility to ensure that the old primary it is not reachable by the other instances to prevent or minimize such scenarios.

After a forced failover, the old primary is considered invalid by the new primary and can no longer be part of the replica set. If at a later date you find a way to recover the instance, it must be removed from the ReplicaSet and re-added as a new instance. If there were any secondary instances that could not be switched to the new primary during the failover, they are also considered invalid.

Data loss is possible after a failover, because the old primary might have had transactions that were not yet replicated to the secondary being promoted. Moreover, if the instance that was presumed to have failed is still able to process transactions, for example because the network where it is located is still functioning but unreachable from MySQL Shell, it continues diverging from the promoted instances. Recovering once transaction sets on instances have diverged requires manual intervention and could not be possible in some situations, even if the failed instances can be recovered. In many cases, the fastest and simplest way to recover from a disaster that required a forced failover is by discarding such diverged transactions and re-provisioning a new instance from the newly promoted primary.

From version 8.0.20, AdminAPI uses a locking mechanism to avoid different operations from performing changes on an InnoDB ReplicaSet simultaneously. Previously, different instances of MySQL Shell could connect to an InnoDB ReplicaSet at the same time and execute AdminAPI operations simultaneously. This could lead to inconsistent instance states and errors, for example if ReplicaSet.addInstance() and ReplicaSet.setPrimaryInstance() were executed in parallel.

The InnoDB ReplicaSet operations have the following locking:

In practice, if you try to execute an InnoDB ReplicaSet related operation while another operation that cannot be executed concurrently is still running, you get an error indicating that a lock on a needed resource failed to be acquired. In this case, you should wait for the running operation which holds the lock to complete, and only then try to execute the next operation. For example:

mysql-js> rs.addInstance("admin@rs2:3306");

ERROR: The operation cannot be executed because it failed to acquire the lock on
instance 'rs1:3306'. Another operation requiring exclusive access to the
instance is still in progress, please wait for it to finish and try again.

ReplicaSet.addInstance: Failed to acquire lock on instance 'rs1:3306' (MYSQLSH
51400)

In this example, the ReplicaSet.addInstance() operation failed because the lock on the primary instance (rs1:3306) could not be acquired, for example because a ReplicaSet.setPrimaryInstance() operation (or other similar operation) was still running.

Tagging is supported by ReplicaSets, and their instances. For the purpose of tagging, ReplicaSets support the setOption(), setInstanceOption() and options() operations. These operations function in generally the same way as their Cluster equivalents. For more information, see Section 21.2.9, “Tagging the Metadata”. This section documents the differences in working with tags for ReplicaSets.

The ReplicaSet.options() operation shows information about the tags assigned to individual ReplicaSet instances as well as to the ReplicaSet itself.

The option argument of ReplicaSet.setOption() and ReplicaSet.setInstanceOption() only support options with the tag namespace and throw an error otherwise.

The ReplicaSet.setInstanceOption(instance, option, value) and ReplicaSet.setOption(option, value) operations behave in the same way as the Cluster equivalent operations.

There are no differences in hiding instances as described at Removing Instances from Routing. For example, to hide the ReplicaSet instance rs-1, issue:

mysql-js> myReplicaSet.setInstanceOption(icadmin@rs-1:3306, "tag:_hidden", true);

A MySQL Router that has been bootstrapped against the ReplicaSet detects the change and removes the rs-1 instance from the routing destinations.

When MySQL Router connects to an InnoDB Cluster or InnoDB ReplicaSet, it requires a user account which has the correct privileges. From MySQL Router version 8.0.19 this internal user can be specified using the --account option. In previous versions, MySQL Router created internal accounts at each bootstrap of the cluster, which could result in a number of accounts building up over time. From MySQL Shell version 8.0.20, you can use AdminAPI to set up the user account required for MySQL Router. Use the setupRouterAccount(user, [options]) operation to create a MySQL user account or upgrade an existing account so that it can be used by MySQL Router to operate on an InnoDB Cluster or InnoDB ReplicaSet. This is the recommended method of configuring MySQL Router with InnoDB Cluster and InnoDB ReplicaSet.

To add a new MySQL Router account named myRouter1 to the InnoDB Cluster referenced by the variable testCluster, issue:

mysqlsh> testCluster.setupRouterAccount(myRouter1)

In this case, no domain is specified and so the account is created with the wildcard (%) character, which ensures that the created user can connect from any domain. To limit the account to only be able to connect from the example.com domain, issue:

mysqlsh> testCluster.setupRouterAccount(myRouter1@example.com)

The operation prompts for a password, and then sets up the MySQL Router user with the correct privileges. If the InnoDB Cluster or InnoDB ReplicaSet has multiple instances, the created MySQL Router user is propagated to all of the instances.

When you already have a MySQL Router user configured, for example if you were using a version prior to 8.0.20, you can use the setupRouterAccount() operation to reconfigure the existing user. In this case, pass in the update option set to true. For example, to reconfigure the myOldRouter user, issue:

mysqlsh> testCluster.setupRouterAccount(myOldRouter, {'update':1})

The recommended deployment of MySQL Router is on the same host as the application. When using a sandbox deployment, everything is running on a single host, therefore you deploy MySQL Router to the same host. When using a production deployment, we recommend deploying one MySQL Router instance to each machine used to host one of your client applications. It is also possible to deploy MySQL Router to a common machine through which your application instances connect. For more information, see Installing MySQL Router.

To bootstrap MySQL Router based on an InnoDB Cluster or InnoDB ReplicaSet, you need the URI-like connection string to an online instance. Run the mysqlrouter command and provide the --bootstrap=instance option, where instance is the URI-like connection string to an online instance. MySQL Router connects to the instance and uses the included metadata cache plugin to retrieve the metadata, consisting of a list of server instance addresses and their role. For example:

shell> mysqlrouter --bootstrap icadmin@ic-1:3306 --user=mysqlrouter 

You are prompted for the instance password and encryption key for MySQL Router to use. This encryption key is used to encrypt the instance password used by MySQL Router to connect to the cluster. The ports you can use for client connections are also displayed.

The MySQL Router bootstrap process creates a mysqlrouter.conf file, with the settings based on the metadata retrieved from the address passed to the --bootstrap option, in the above example icadmin@ic-1:3306. Based on the metadata retrieved, MySQL Router automatically configures the mysqlrouter.conf file, including a metadata_cache section. If you are using MySQL Router 8.0.14 and later, the --bootstrap option automatically configures MySQL Router to track and store active MySQL metadata server addresses at the path configured by dynamic_state. This ensures that when MySQL Router is restarted it knows which MySQL metadata server addresses are current. For more information, see the dynamic_state documentation.

In earlier MySQL Router versions, metadata server information was defined during MySQL Router's initial bootstrap operation and stored statically as bootstrap_server_addresses in the configuration file, which contained the addresses for all server instances in the cluster. For example:

[metadata_cache:prodCluster]
router_id=1
bootstrap_server_addresses=mysql://icadmin@ic-1:3306,mysql://icadmin@ic-2:3306,mysql://icadmin@ic-3:3306
user=mysql_router1_jy95yozko3k2
metadata_cluster=prodCluster
ttl=300

The generated MySQL Router configuration creates TCP ports which you use to connect to the cluster. By default, ports for communicating with the cluster using both classic MySQL protocol and X Protocol are created. To use X Protocol the server instances must have X Plugin installed and configured, which is the default for MySQL 8.0 and later. The default available TCP ports are:

Depending on your MySQL Router configuration the port numbers might be different to the above. For example if you use the --conf-base-port option, or the group_replication_single_primary_mode variable. The exact ports are listed when you start MySQL Router.

The way incoming connections are redirected depends on the underlying topology being used. For example, when using a single-primary cluster, by default MySQL Router publishes a X Protocol and a classic MySQL protocol port, which clients connect to for read-write sessions and which are redirected to the cluster's single primary. With a multi-primary cluster read-write sessions are redirected to one of the primary instances in a round-robin fashion. For example, this means that the first connection to port 6446 would be redirected to the ic-1 instance, the second connection to port 6446 would be redirected to the ic-2 instance, and so on. For incoming read-only connections MySQL Router redirects connections to one of the secondary instances, also in a round-robin fashion. To modify this behavior see the routing_strategy option.

Once bootstrapped and configured, start MySQL Router. If you used a system wide install with the --bootstrap option then issue:

shell> mysqlrouter &

If you installed MySQL Router to a directory using the --directory option, use the start.sh script found in the directory you installed to. Alternatively set up a service to start MySQL Router automatically when the system boots, see Starting MySQL Router. You can now connect a MySQL client, such as MySQL Shell to one of the incoming MySQL Router ports as described above and see how the client gets transparently connected to one of the server instances.

shell> mysqlsh --uri root@localhost:6442

To verify which instance you are actually connected to, simply issue an SQL query against the port status variable.

mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+

You can use MySQL Router 8.0.19 and later to bootstrap against an InnoDB ReplicaSet, see Section 21.4, “MySQL Router”. The only difference in the generated MySQL Router configuration file is the addition of the cluster_type option. When MySQL Router is bootstrapped against a ReplicaSet, the generated configuration file includes:

cluster_type=rs

When you use MySQL Router with InnoDB ReplicaSet, be aware that:

You work with the MySQL Router instances which have been bootstrapped against a ReplicaSet in exactly the same way as with InnoDB Cluster. See Working with a Cluster's Routers for information on ReplicaSet.listRouters() and ReplicaSet.removeRouterMetadata().

To test if InnoDB Cluster high availability works, simulate an unexpected halt by killing an instance. The cluster detects the fact that the instance left the cluster and reconfigures itself. Exactly how the cluster reconfigures itself depends on whether you are using a single-primary or multi-primary cluster, and the role the instance serves within the cluster.

In single-primary mode:

For more information see Section 18.1.3.1, “Single-Primary Mode”.

In multi-primary mode:

For more information see Section 18.1.3.2, “Multi-Primary Mode”.

There are various ways to simulate an instance leaving a cluster, for example you can forcibly stop the MySQL server on an instance, or use the AdminAPI dba.killSandboxInstance() if testing a sandbox deployment. In this example assume there is a single-primary sandbox cluster deployment with three server instances and the instance listening at port 3310 is the current primary. Simulate the instance leaving the cluster unexpectedly:

mysql-js> dba.killSandboxInstance(3310)

The cluster detects the change and elects a new primary automatically. Assuming your session is connected to port 6446, the default read-write classic MySQL protocol port, MySQL Router should detect the change to the cluster's topology and redirect your session to the newly elected primary. To verify this, switch to SQL mode in MySQL Shell using the \sql command and select the instance's port variable to check which instance your session has been redirected to. Notice that the first SELECT statement fails as the connection to the original primary was lost. This means the current session has been closed, MySQL Shell automatically reconnects for you and when you issue the command again the new port is confirmed.

mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> SELECT @@port;
ERROR: 2013 (HY000): Lost connection to MySQL server during query
The global session got disconnected.
Attempting to reconnect to 'root@localhost:6446'...
The global session was successfully reconnected.
mysql-sql> SELECT @@port;
+--------+
| @@port |
+--------+
|   3330 |
+--------+
1 row in set (0.00 sec)

In this example, the instance at port 3330 has been elected as the new primary. This shows that the InnoDB Cluster provided us with automatic failover, that MySQL Router has automatically reconnected us to the new primary instance, and that we have high availability.

You can bootstrap multiple instances of MySQL Router against a cluster or ReplicaSet. From version 8.0.19, to show a list of all registered MySQL Router instances, issue:

Cluster.listRouters()

The result provides information about each registered MySQL Router instance, such as its name in the metadata, the hostname, ports, and so on. For example, issue:

mysql-js> Cluster.listRouters()
{
    "clusterName": "example", 
    "routers": {
        "ic-1:3306": {
            "hostname": "ic-1:3306", 
            "lastCheckIn": "2020-01-16 11:43:45", 
            "roPort": 6447, 
            "roXPort": 64470, 
            "rwPort": 6446, 
            "rwXPort": 64460, 
            "version": "8.0.19"
        }
    }
}

The returned information shows:

Additionally, the Cluster.listRouters() operation can show a list of instances that do not support the metadata version supported by MySQL Shell. Use the onlyUpgradeRequired option, for example by issuing Cluster.listRouters({'onlyUpgradeRequired':'true'}). The returned list shows only the MySQL Router instances registered with the Cluster which require an upgrade of their metadata. See Section 21.2.8.2, “Upgrading InnoDB Cluster Metadata”.

MySQL Router instances are not automatically removed from the metadata, so for example as you bootstrap more instances the InnoDB Cluster metadata contains a growing number of references to instances. To remove a registered MySQL Router instance from a cluster's metadata, use the Cluster.removeRouterMetadata(router) operation, added in version 8.0.19. Use the Cluster.listRouters() operation to get the name of the MySQL Router instance you want to remove, and pass it in as router. For example suppose your MySQL Router instances registered with a cluster were:

mysql-js> Cluster.listRouters(){

    "clusterName": "testCluster",
    "routers": {
        "myRouter1": {
            "hostname": "example1.com",
            "lastCheckIn": null,
            "routerId": "1",
            "roPort": "6447",
            "rwPort": "6446"
            "version": null
        },
        "myRouter2": {
            "hostname": "example2.com",
            "lastCheckIn": "2019-11-27 16:25:00",
            "routerId": "3",
            "roPort": "6447",
            "rwPort": "6446"
            "version": "8.0.19"
        }
    }
}

Based on the fact that the instance named “myRouter1” has null for “lastCheckIn” and “version”, we decide to remove this old instance from the metadata by issuing:

mysql-js> cluster.removeRouterMetadata('myRouter1')

The MySQL Router instance specified is unregistered from the cluster by removing it from the InnoDB Cluster metadata.