7.4.2 Configuring Production Instances for InnoDB Cluster Usage

AdminAPI provides the dba.configureInstance() function that checks if an instance is suitably configured for InnoDB Cluster usage, and configures the instance if it finds any settings which are not compatible with InnoDB Cluster. You run the dba.configureInstance() command against an instance and it checks all of the settings required to enable the instance to be used for InnoDB Cluster usage. If the instance does not require configuration changes, there is no need to modify the configuration of the instance, and the dba.configureInstance() command output confirms that the instance is ready for InnoDB Cluster usage.

If any changes are required to make the instance compatible with InnoDB Cluster, a report of the incompatible settings is displayed, and you can choose to let the command make the changes to the instance's option file. Depending on the way MySQL Shell is connected to the instance, and the version of MySQL running on the instance, you can make these changes permanent by persisting them to a remote instance's option file, see Section 6.2.4, “Persisting Settings”.

Instances which do not support persisting configuration changes automatically require that you configure the instance locally, see Configuring Instances with dba.configureLocalInstance(). Alternatively you can make the changes to the instance's option file manually, see Using Option Files for more information. Regardless of the way you make the configuration changes, you might have to restart MySQL to ensure the configuration changes are detected.

The syntax of the dba.configureInstance() command is:

dba.configureInstance([instance][, options])

where instance is an instance definition, and options is a data dictionary with additional options to configure the operation. The operation returns a descriptive text message about the result.

The instance definition is the connection data for the instance. For example:

dba.configureInstance('user@example:3306')

For more information, see Connecting to the Server Using URI-Like Strings or Key-Value Pairs. If the target instance already belongs to an InnoDB Cluster an error is generated and the process fails.

The options dictionary can contain the following:

• mycnfPath - the path to the MySQL option file of the instance. Note that InnoDB Cluster only supports a single option file for server instances, and does not support the use of the --defaults-extra-file option to specify an additional option file. For any AdminAPI operation working with the instance's option file the main file must be specified.

• outputMycnfPath - alternative output path to write the MySQL option file of the instance.

• password - the password to be used by the connection.

• clusterAdmin - the name of an InnoDB Cluster administrator user to be created. The supported format is the standard MySQL account name format. Supports identifiers or strings for the user name and host name. By default if unquoted it assumes input is a string. See Section 6.4, “Creating User Accounts for AdminAPI”.

• clusterAdminPassword - the password for the InnoDB Cluster administrator account being created using clusterAdmin. Although you can specify using this option, this is a potential security risk. If you do not specify this option, but do specify the clusterAdmin option, you are prompted for the password at the interactive prompt.

• clearReadOnly - a Boolean value used to confirm that super_read_only should be set to off, see Instance Configuration in Super Read-only Mode. This option is deprecated, and scheduled for removal in a future version.

• interactive - a Boolean value used to disable the interactive wizards in the command execution, so that prompts are not provided to the user and confirmation prompts are not shown.

• restart - a Boolean value used to indicate that a remote restart of the target instance should be performed to finalize the operation.

Although the connection password can be contained in the instance definition, this is insecure and not recommended. Use the MySQL Shell Section 4.4, “Pluggable Password Store” to store instance passwords securely.

Once dba.configureInstance() is issued against an instance, the command checks if the instance's settings are suitable for InnoDB Cluster usage. A report is displayed which shows the settings required by InnoDB Cluster . If the instance does not require any changes to its settings you can use it in an InnoDB Cluster, and can proceed to Section 7.4.3, “Creating an InnoDB Cluster”. If the instance's settings are not valid for InnoDB Cluster usage the dba.configureInstance() command displays the settings which require modification. Before configuring the instance you are prompted to confirm the changes shown in a table with the following information:

• Variable - the invalid configuration variable.

• Current Value - the current value for the invalid configuration variable.

• Required Value - the required value for the configuration variable.

How you proceed depends on whether the instance supports persisting settings, see Section 6.2.4, “Persisting Settings”. When dba.configureInstance() is issued against the MySQL instance which MySQL Shell is currently running on, in other words the local instance, it attempts to automatically configure the instance. When dba.configureInstance() is issued against a remote instance, if the instance supports persisting configuration changes automatically, you can choose to do this. If a remote instance does not support persisting the changes to configure it for InnoDB Cluster usage, you have to configure the instance locally. See Configuring Instances with dba.configureLocalInstance().

In general, a restart of the instance is not required after dba.configureInstance() configures the option file, but for some specific settings a restart might be required. This information is shown in the report generated after issuing dba.configureInstance(). If the instance supports the RESTART statement, MySQL Shell can shutdown and then start the instance. This ensures that the changes made to the instance's option file are detected by mysqld. For more information see RESTART.

Note

After executing a RESTART statement, the current connection to the instance is lost. If auto-reconnect is enabled, the connection is reestablished after the server restarts. Otherwise, the connection must be reestablished manually.

The dba.configureInstance() method verifies that a suitable user is available for cluster usage, which is used for connections between members of the cluster, see Section 6.4, “Creating User Accounts for AdminAPI”.

If you do not specify a user to administer the cluster, in interactive mode a wizard enables you to choose one of the following options:

• enable remote connections for the root user, not recommended in a production environment

• create a new user

• no automatic configuration, in which case you need to manually create the user

Tip

If the instance has super_read_only=ON then you might need to confirm that AdminAPI can set super_read_only=OFF. See Instance Configuration in Super Read-only Mode for more information.

Configuring Instances with dba.configureLocalInstance()

Instances which do not support persisting configuration changes automatically (see Section 6.2.4, “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:

• dba.configureInstance()

• dba.createCluster()

• Cluster.addInstance()

• Cluster.removeInstance()

• Cluster.rejoinInstance()

Important

Failing to persist configuration changes to an instance's option file can result in the instance not rejoining the cluster after the next restart.

The relevant configuration options for the instance, particularly the Group Replication configuration options, must be in a single option file. InnoDB Cluster only supports a single option file for server instances and does not support the use of the --defaults-extra-file option to specify an additional option file. For any AdminAPI operation working with the instance's option file the main file must be specified.

The recommended method to persist configuration changes 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:

$> 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.

Instance Configuration in Super Read-only Mode

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:

• dba.configureInstance()

• dba.configureLocalInstance()

• dba.dropMetadataSchema()

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.