Chapter 20 InnoDB Cluster

MySQL Shell includes the AdminAPI, which is accessed through the dba global variable and its associated methods. The dba variable's methods enable you to deploy, configure, and administer InnoDB clusters. For example, use the dba.createCluster() method to create an InnoDB cluster.

MySQL Shell provides online help for the AdminAPI. To list all available dba commands, use the dba.help() method. For online help on a specific method, use the general format object.help('methodname'). For example:

mysql-js> dba.help('getCluster')

Retrieves a cluster from the Metadata Store.

SYNTAX
  <Dba>.getCluster([name])

WHERE
  name: Parameter to specify the name of the cluster to be returned.

DESCRIPTION

If name is not specified, the default cluster will be returned.

If name is specified, and no cluster with the indicated name is found, an error
will be raised.

To test if 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 17.4.1.1, “Single-Primary Mode”.

In multi-primary mode:

For more information see Section 17.4.1.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)

Switch to SQL mode in MySQL Shell using the \sql command and verify the port variable to check which instance you are connected 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)

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.

When MySQL Router is bootstrapped against a cluster, it records the server instance's addresses in its configuration file. If any additional instances are added to the cluster after bootstrapping the MySQL Router, they are not automatically detected and therefore are not used for connection routing.

To ensure that newly added instances are routed to correctly you must bootstrap MySQL Router against the cluster to read the updated metadata. This means that you must restart MySQL Router and include the --bootstrap option.

When you create a cluster using dba.createCluster(), the operation returns a Cluster object which can be assigned to a variable. You use this object to work with the cluster, for example to add instances or check the cluster's status. If you want to retrieve a cluster again at a later date, for example after restarting MySQL Shell, use the dba.getCluster(name, [options]) function. For example:

mysql-js> var cluster1 = dba.getCluster()

If you do not specify a cluster name then the default cluster is returned. If there is more than one cluster stored in the InnoDB cluster metadata of the server instance which the MySQL Shell global session is currently connected to, specify the name of the cluster you want to retrieve.

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": "localhost:3320", 
	"ssl": "REQUIRED",
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}

The information output by cluster.status() provides the following information:

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

mysql-js> cluster.describe();
{
    "clusterName": "test",
    "adminType": "local",
    "defaultReplicaSet": {
        "name": "default",
        "instances": [
            {
                "name": "localhost:3310",
                "host": "localhost:3310",
                "role": "HA"
            },
            {
                "name": "localhost:3320",
                "host": "localhost:3320",
                "role": "HA"
            },
            {
                "name": "localhost:3330",
                "host": "localhost:3330",
                "role": "HA"
            }
        ]
    }
}

The output from this function shows the structure of the InnoDB cluster including all of its configuration information, and so on.

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.createCluster('testCluster')
A new InnoDB cluster will be created on instance 'ic@ic-1:3306'.

The MySQL instance at 'ic@ic-1:3306' 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.

Note: there are open sessions to 'ic@ic-1:3306'.
You may want to kill these sessions to prevent them from performing unexpected updates: 

1 open session(s) of 'ic@ic-1:3306'. 


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

The number of current active sessions to the instance is shown. You must ensure that no applications might 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.

To force the function to set super_read_only=OFF in a script, pass the clearReadOnly option set to true. For example dba.configureInstance(instance, {clearReadOnly: true}).

Once a sandbox instance is running, it is possible to change its status at any time using the following:

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

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

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 these 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() commands. Seed instances are contacted when a new instance joins a cluster and used to provide data to the new instance. The addresses are specified as a comma separated list such as host1:port1,host2:port2. This configures the group_replication_group_seeds system variable.

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

If an instance leaves the cluster, for example because it lost connection and did not or 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().

In the case where an instance has not had it's configuration persisted, for example when you have not issued dba.configureLocalInstance() locally on the instance but it has been added to a cluster, 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. Then connect to the instance, run MySQL Shell locally and issue dba.configureLocalInstance(). This ensures the InnoDB cluster configuration is persisted to the instance's option file to enable it to rejoin the cluster automatically.

If a instance (or instances) fail, then a cluster can lose its quorum, which is the ability to vote in a new primary. In this case you can re-establish quorum using the method cluster.forceQuorumUsingPartitionOf(), as shown in the following MySQL Shell example:

  // open session to a cluster

mysql-js> cluster = dba.getCluster("prodCluster")

  // The cluster lost its quorum and its status shows
  // "status": "NO_QUORUM"

mysql-js> cluster.forceQuorumUsingPartitionOf("localhost:3310")

  Restoring replicaset 'default' from loss of quorum, by using the partition composed of [localhost:3310]

  Please provide the password for 'root@localhost:3310': ******
  Restoring the InnoDB cluster ...

  The InnoDB cluster was successfully restored using the partition from the instance 'root@localhost:3310'.

  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.

If your cluster suffers from a complete outage, you can ensure it is reconfigured correctly using dba.rebootClusterFromCompleteOutage(). In the event that a cluster has 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, connect to an instance and run MySQL Shell. Then restart the cluster by issuing:

        
mysql-js> shell.connect('root@localhost:3310');
mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

This ensures the cluster is correctly reconfigured after a complete outage. It uses the instance that MySQL Shell is connected to as the new seed instance and recovers the cluster based on the existing metadata of that instance.

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 changes to an instance's configuration are made without using AdminAPI, you need to rescan the cluster to update the InnoDB cluster metadata. For example, if you manually add a new instance to the Group Replication group, the InnoDB cluster metadata is not modified based on this change to the cluster because MySQL Shell was not used. In such a scenario it is necessary to rescan the cluster with cluster.rescan() to update the InnoDB cluster metadata.

After the command cluster.rescan() has been run, instances are identified that are newly discovered instances. You are prompted to add each of these newly discovered instances into your cluster as required, or you can choose to ignore them.

Instances that no longer belong to the cluster or which are unavailable are also reported. In this case you are prompted to remove the instance, or you can later attempt to add it back into the cluster using a command such as cluster.rejoin('ic@ic-4:3306').

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 16.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('ic@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.

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. There is no way to undo the dissolving of a cluster, therefore you must pass force: true to confirm you want to dissolve the cluster. For example: to create it again use dba.createCluster().

mysql-js> session
<ClassicSession:root@localhost:3310>
mysql-js> cluster.dissolve({force:true})
The cluster was successfully dissolved.
Replication was disabled but user data was left intact.

Server instances can be configured to use secure connections. For general information on using SSL with MySQL see Section 6.3, “Using Encrypted Connections”. This section explains how to configure a cluster to use SSL. 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 SSL 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. For more control, the cluster.addInstance(), and cluster.rejoinInstance() commands accept the memberSslMode option. This can be used to test the SSL settings of the cluster when instances join. The behavior of the commands in this case is:

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 17.5.2, “Group Replication Secure Socket Layer (SSL) Support”. 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 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. 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. Pass the servers as a comma separated list, surrounded by quotes. Using the ipWhitelist option configures the group_replication_ip_whitelist system variable on the instance. For example:

mysql-js> cluster.addInstance("ic@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. From MySQL 5.7.21, the whitelist can also include host names, which are resolved only when a connection request is made by another server.

You can automate cluster configuration with scripts. For example:

shell> mysqlsh -f setup-innodb-cluster.js

The contents of an example script file is shown here:

  print('MySQL InnoDB cluster sandbox set up\n');
  print('==================================\n');
  print('Setting up a MySQL InnoDB cluster with 3 MySQL Server sandbox instances.\n');
  print('The instances will be installed in ~/mysql-sandboxes.\n');
  print('They will run on ports 3310, 3320 and 3330.\n\n');

  var dbPass = shell.prompt('Please enter a password for the MySQL root account: ', {type:"password"});

  try {
     print('\nDeploying the sandbox instances.');
     dba.deploySandboxInstance(3310, {password: dbPass});
     print('.');
     dba.deploySandboxInstance(3320, {password: dbPass});
     print('.');
     dba.deploySandboxInstance(3330, {password: dbPass});
     print('.\nSandbox instances deployed successfully.\n\n');

     print('Setting up InnoDB cluster...\n');
     shell.connect('root@localhost:3310', dbPass);

     var cluster = dba.createCluster("prodCluster");

     print('Adding instances to the cluster.');
     cluster.addInstance({user: "root", host: "localhost", port: 3320, password: dbPass});
     print('.');
     cluster.addInstance({user: "root", host: "localhost", port: 3330, password: dbPass});
     print('.\nInstances successfully added to the cluster.');

     print('\nInnoDB cluster deployed successfully.\n');
  } catch(e) {
     print('\nThe InnoDB cluster could not be created.\n\nError: ' +
     + e.message + '\n');
}