MySQL InnoDB Cluster – What’s New in the Release Candidate
Mysql 28-Apr-2017

MySQL InnoDB Cluster – What’s New in the Release Candidate

We carefully listened to the feedback we got from the last preview release and incorporated many of the suggested changes. On top of that, we have fixed several bugs and extended existing functionalities.

Here are the highlights of this release!

MySQL Shell / X AdminAPI

We keep aiming at the goal of hiding the complexity associated with configuring and managing H/A setups, but on top of that, we see usability as a great priority. Because of that, we have improved the X AdminAPI with extensions to existing commands in order to make it even more simple and easy to use, but also to overcome issues mostly related with real world scenarios.

As promised on the previous post, we have also now included a new command to recover a Cluster from a complete outage.

dba.rebootClusterFromCompleteOutage()

Should a complete outage occur – which means all instances of a cluster end up in the “OFFLINE” status relative to the cluster – it has not been possible to get the cluster up and running again just using the X AdminAPI. Having a previously configured cluster, the DBA would certainly want to bring it back after restoring the offline instances. For that purpose, we have introduced a new command called “dba.rebootClusterFromCompleteOutage()“.

This command restores a cluster from a complete outage by picking the instance the MySQL Shell is connected to and set it as the new seed instance. Then it will recover the cluster as defined on the cluster Metadata.

Several things have to be taken into account since this can be quite a dangerous operation otherwise. This includes:

  • The instances belonging to the cluster, as registered on the Metadata, being already part of a replication group
  • The selected instance to become new seed instance not holding the GTID superset, i.e. not being the most up-to-date instance
  • The cluster chosed for the reboot not existing on the Metadata

We have made sure that those scenarios are validated before any execution to keep your clusters safe.

In order to let the DBA specify which of the instances that have been previously registered in the Metadata should actually be kept in the cluster, or removed, the command allows the user to specify two optional parameters for that purpose: “removeInstances” and “rejoinInstances”. However, to provide a better user experience, the command discovers which instances are “OFFLINE”, “UNREACHABLE” or “MISSING” and interactively asks the user to rejoin or remove the instances from the cluster configuration.

Better SSL Support

On the previous release, we enabled the usage of SSL to make the deployments of secure InnoDB clusters possible. For this release, we have greatly improved this support and addressed several design concerns.

The following properties are ensured:

  • Improved SSL support in the MySQL Shell
  1. If SSL encryption is supported by the MySQL Server it is enabled by default
  2. If the user specifies that the connection between the Shell and MySQL must use SSL, then all connections either use SSL or fail to connect.
  • Improved SSL support to configure an InnoDB Cluster
  1. When creating a cluster from a server which has SSL enabled, it’s possible to choose whether SSL encryption should be used by the cluster members to communicate with each other. The ‘memberSslMode’ option can be used to control SSL usage for this purpose
  2. If the seed instance does not have SSL support, the cluster is created with SSL disabled. The default SSL mode that is used depends if the server supports SSL or not, being set automatically if not specified.
  3. The same SSL mode must be used by all instances of the cluster.

Cluster restart and automatic rejoin

One would expect that if a cluster instance is restarted, the instance automatically rejoins the cluster again. However, up to now, this was not supported directly in the X AdminAPI.

In order to achieve this automatic rejoin behaviour of cluster instances, we have extended the cluster.addInstance() and dba.configureLocalInstance() commands.

The dba.configureLocalInstance() will now update the my.cnf file with the parameters necessary for a server instance to automatically rejoin the cluster on startup.

Improved dba.configureLocalInstance()

This function allows the user to get an instance ready to be used on the InnoDB cluster, without advanced knowledge of the MySQL configuration options. To further improve usability we extended this command and applied several bug fixes.

One of the main concerns was the MySQL account not being adequate for cluster usage, i.e. not having the required privileges. By default, the super-user account only exists on localhost: root@localhost, not allowing remote connections to the server. To fix this issue we have added MySQL account validation that ensures the MySQL account used to configure the instance has proper privileges and access permissions. On top of that – to provide a better user experience – we are now asking the user if a new, dedicated MySQL account for InnoDB cluster management should be created. When the dba.configureLocalInstance() command is used in scripts, a new account can be immediately created with the new options ‘clusterAdmin’ and ‘clusterAdminPassword’.

We have also introduced automatic detection of default my.cnf file paths on the most used Operating Systems / Distributions as configured by our official MySQL packages.

Last but not least, the original command name “dba.configLocalInstance()” wasn’t following the convention that the API must have human-readable names and not computer-related terminology, acronyms or abbreviations. Therefore we have renamed the command from dba.configLocalInstance() to dba.configureLocalInstance().

Renamed dba.checkInstanceConfiguration()

Following the same consideration, we also renamed dba.checkInstanceConfig() to dba.checkInstanceConfiguration().

cluster.addInstance()

One of the core operations of the cluster is to add new instances to it. So, yet again, the cluster.addInstance() command was improved. Several bugs regarding corner-cases and some common use-case have been addresses and the validations logic was greatly expanded. The command is now able to validate the following scenarios:

  • The instance already belongs to the cluster
  • The instance belongs to a different replication group (unmanaged)
  • The instance belongs to a different cluster

Following the cluster.removeInstance() redesign, which does not allow the use of the instance ‘name’ as an argument, the optional property ‘name’ of addInstance() was renamed to ‘label’. The main reason for this decision falls on the simple fact that the label is just used for displaying purposes, on the cluster describe() and status() commands.

cluster.removeInstance()

To keep the API as simple and straightforward as possible, the cluster.removeInstance()command was slightly redesigned. The command signature was changed to the following:
cluster.removeInstance(instanceDef [, password]);
 

instanceDef can either be a URI string (e.g. dba@192.168.0.30:3306) or a JSON object holding connection information (e.g. {user:’dba’, password:’dbaPassword’, host: ‘192.168.0.30’, port: 3306}).

IP White List

On a default deployment, MySQL Group Replication allows the communication between members of hosts within the private network by default, disallowing communications from external hosts. A common scenario on which the use of non-private networks is mandatory is the Cloud. To overcome this scenario, an IP whitelist must be configured.

Therefore X AdminAPI was extended to allow the configuration of an IP whitelist, which can also be used to limit the accepted hosts on a local network if that is a security requirement.

The dba.createCluster(), cluster.addInstance() and cluster.rejoinInstance()commands were extended to include an optional parameter ‘ipWhitelist’.

Metadata

The Metadata is of extreme importance for the InnoDB cluster environment, and if it’s not replicated across the members of the cluster several errors can occur. There was a specific scenario which prevented the Metadata to be replicated, which was the use of binary log filters.

To overcome this difficulty, we have added checks on the cluster.createCluster() and dba.addInstance() functions to validate if any invalid binary log filter (binlog-do-db or binlog-ignore-db) is defined.

dba.deploySandboxInstance()

Until now the dba.deploySandboxInstance() function expected that the file path to the MySQLd binaries was set in the PATH environment variable. However, this is not set by a default installation and certainly inconvenient for inexperienced users. Because of that, we have added an automatic search routine for the default MySQLd location paths, apart from PATH, as follows:

Windows

  • C:/Program Files/MySQL/MySQL Server 5.7/bin
  • C:/Program Files/MySQL/MySQL Server 8.0/bin

Linux & macOS

  • /usr/sbin
  • /usr/local/mysql/bin/
  • /usr/bin/
  • /usr/local/bin/
  • /usr/local/sbin/
  • /opt/local/bin/
  • /opt/local/sbin/

dba.stopSandboxInstance()

The mechanism to shutdown the sandbox instances was improved. Previously, it was using the SIGTERM command signal. However, on Windows systems the SIGTERM signal does not allow the server to handle graceful shutdowns like it does on Unix systems. To correct that situation, the command now relies on the SQL command ‘SHUTDOWN’, introduced with MySQL 5.7.9.

As a consequence, the command does now require the root password to execute the shutdown.

MySQL Shell features

The MySQL Shell – apart from bug fixing – includes a few new features, some changes, and some removals:

  • The URI of a server instance is now the default argument when starting MySQL Shell at the command line, replacing the default schema name. The usage of --uri is not needed anymore.

 

 

  • In the MySQL Shell Python X DevAPI implementation, the mysqlx and mysql modules have been moved into the new mysqlsh module. This changes the way users import the modules to:

 

 

  • XSessions have been removed, which means the --x command option is deprecated. The default session is now a NodeSession.
  • The stored sessions functionality has been removed. We are considering a new implementation for a future version of the MySQL Shell.

MySQL Router

More options for Bootstrapping

In the previous releases, we introduced the bootstrapping functionality. The Router is capable of connecting to the Metadata storage and quickly configure itself for immediate usage on the InnoDB Cluster setup. In this release, we extended the bootstrapping capabilities to support several different use-cases. This was done by adding a list of new options:

  • --bootstrap
  • --conf-base-port
  • --conf-bind-address
  • --conf-use-sockets
  • --conf-skip-tcp
  • --directory
  • --force
  • --name

SSL Improvements

Following the SSL changes and improvements, the Router was also extended to support SSL by including the following new options:

  • --ssl-mode
  • --ssl-ca
  • --ssl-capath
  • --ssl-cipher
  • --ssl-crl
  • --ssl-crlpath
  • --tls-version

Adding support for X Protocol

One of the missing features of the Router was the support for the X Protocol. The Router now allows a new protocol configuration option to support the X Protocol. The default value is set to the classic MySQL protocol.

Dropping MySQL Fabric support

Support for MySQL Fabric was removed from the Router. Instead, the Router now focuses on the InnoDB cluster.

Conclusion

Our continuous work on InnoDB cluster is part of our efforts to improve ease of use of the High Availability technologies built into MySQL. Compared to our first Labs release of InnoDB cluster we have now reached a much better state of maturity of the X AdminAPI as well as an improved level of stability. We will keep working on our goal of making life easier for the common user and giving full scriptability to the power user, bringing simplicity and potential together.