MySQL InnoDB Cluster – What’s new in the 8.0 GA Release

The MySQL Development Team is proud to announce the second GA release of InnoDB cluster!

We listened carefully to the feedback we got from the previous major release (1.0 GA) and the last release candidate (8.0.4 RC) and incorporated many of the suggested changes. And that’s not all, we have introduced exciting new features and fixed several bugs to reach the desired maturity state.

Here are the highlights of this release!

MySQL Shell 8.0

For this release, we have, as always, focused on the usability of InnoDB cluster as a whole. With that in mind, we have introduced several new features to overcome common user problems and improve the user experience. Also, for advanced users, we have introduced features to increase the control and personalization of clusters. Finally, the MySQL Shell application has been improved with fantastic commonly requested features and some eye-candy.

Below are the most important highlights of this release.

MySQL 8 support

Following MySQL’s Server 8.0 GA release, MySQL Shell, as expected, has full support for MySQL 8. However, it retains full backwards compatibility with MySQL 5.7+ GA.

All changes on the libmysqlclient are now supported in Shell 8.0.

Below, a quick demonstration of the Shell working with both 8.0 and 5.7 servers.

Have you noticed the new look-and-feel of the Shell?

Instance Configuration

MySQL Shell includes the AdminAPI, which has had support for automatic configuration of instances for InnoDB cluster usage since version 1.0, with the command dba.configureLocalInstance(). However, the command had a pitfall: it had to be executed locally on the target instance…

With the 8.0 GA release, we have extended our support to automatic instance configuration with a new command, that most importantly, supports remote configuration:

dba.configureInstance()

This new command enables remote automatic configuration of compatible instances (8.0.11+) for InnoDB cluster usage and if necessary it also supports the remote restart of the instances after the configuration.

This is based on the SET PERSIST feature of MySQL 8 and makes working with production clusters, which are built on networked instances, much easier. By removing the need to install MySQL Shell on each instance and completely removing the need to connect (SSH / Windows remote) to remote instances to run MySQL Shell and the dba.configureLocalInstance() command locally.

In order to keep backwards compatibility, dba.configureInstance() is fully compatible with MySQL 5.7. However, remote configuration of such instances is not available. In order to automatically configure a 5.7 instance, the command requires local execution.

In addition, instance configuration has been greatly improved and extended by adding the following in dba.configureInstance():

  • Better detection of loopback hostnames on Debian and Ubuntu, and local instances based on the hostname;
  • Improved display of configuration errors and required changes;
  • Improved look and feel of the command output;
  • Added support for configuration of MySQL 5.7 instances without root access, by allowing to store the resulting ‘my.cnf’ on a separate file that can be copied to the right path afterwards;
  • Better detection of the target instance administration account privileges and outcome options;

Finally, following the usability and look-and-feel improvements of dba.configureInstance() we have similarly improved dba.checkInstanceConfiguration().

Getting ready to work with InnoDB Clusters is now easier, simpler and more straight-forward!

The following examples show the process of configuring an instance for InnoDB cluster usage.

Starting by checking if the instance is valid for InnoDB cluster usage:

Configuring an instance for InnoDB Cluster usage:

And re-checking if the instance is now ready for InnoDB Cluster usage:

Automatic settings persisted

In previous releases, in order to make the instances of a cluster to withstand a restart, regardless of the cause, it was required to persist the configuration of each instance.

That required running dba.configureLocalInstance() on each of the cluster members after setup, and after any changes to the structure of the cluster.

This was inconvenient, adding an extra step to the setup; and the command had to be executed locally on each instance, which required local access to the machines running the instances and MySQL Shell to be installed.

With this release, we introduce a huge improvement in regard to this matter.

Now, compatible instances (running version 8.0.11+) support having any changes to their settings automatically persisted by the AdminAPI after any cluster topology change. Now, when working with remote instances if you create a cluster, add an instance to a cluster, or remove an instance from a cluster supporting this functionality, there is no need to log in to the instance and run dba.configureLocalInstance() to persist the changes to the instance’s option file.

Again, this makes working with production clusters much easier. For example, it ensures that instances automatically rejoin a cluster in the case of a restart.

If your cluster is running on MySQL 5.7, you still have to use dba.configureLocalIsntance() in order to persist the settings on each cluster member.

InnoDB Cluster connections made easier

Connections to InnoDB cluster have been greatly improved and simplified, again with usability in mind.

Whenever a cluster object is retrieved, a session to one of the members of the cluster is required, however, in the past if that session was to a read-only (R/O) member of a single-primary cluster, cluster modification operations weren’t allowed and the user would have to establish a new connection to the cluster using a session to the read-write (R/W) member.

Now, when dba.getCluster() is used and the active Shell session is not a connection to the primary, the cluster is queried for the primary instance information and a connection to it is automatically established, removing any need to change the session.

As part of this improvement you can now configure the type of MySQL Shell connection to an InnoDB cluster with the following options which have been added to the Shell:

  • --redirect-primary :  Connect to the primary of the group;
  • --redirect-secondary : Connect to a secondary of the group;
  • --cluster : Enable cluster management, setting the cluster global variable;

Additionally, the following changes were made:

  • dba.resetSession() was removed;
  • Added new disconnect() method to the cluster object, which closed all internal sessions opened by the cluster. Note: InnoDB Cluster operations on a disconnected cluster object will result in an error;
  • Added new field to the output of <Cluster>.status()groupInformationSourceMember, to indicate the URI of the internal connection used by the cluster object to obtain information and execute cluster management operations;

Note that the initial connection wasn’t established to the primary instance of the cluster but rather to a R/O one. As –redirect-primary is enabled by default, the connection to the primary instance is automatically and transparently established.

super_read_only handling

In MySQL Server 8.0, whenever Group Replication is stopped, ‘super_read_only‘ is enabled to prevent any client to execute write transactions on the instance. In order to support this behaviour, the following AdminAPI commands have been extended with a new option ‘clearReadOnly‘ that allows disabling super_read_only:

  • dba.configureInstance()
  • dba.configureLocalInstance()
  • dba.createCluster()
  • dba.rebootClusterFromCompleteOutage()
  • dba.dropMetadataSchema()

As always, we have usability in mind, so Shell’s default mode (interactive) will detect if super_read_only is enabled on the target instance and if so prompt you accordingly to disable it in order to proceed with the operation.

The following example illustrates the operation to create a cluster on an instance with super_read_only enabled:

Cluster customization

Advanced users of InnoDB cluster don’t just want to benefit from the great user experience of MySQL Shell and the AdminAPI but also from its flexibility and power. Thus, we’ve extended the AdminAPI to support advanced customization of your InnoDB clusters and enables overriding the default values of:

On the following commands:

  • dba.createCluster()
  • <Cluster>.addInstance()

By adding the following optional parameters:

  • groupName
  • localAddress
  • groupSeeds

The following example shows the usage of groupName and localAddress:

Cluster object session

One of the most important objects in an InnoDB cluster is the cluster object <Cluster>.

There are two main functions in the AdminAPI to handle cluster objects:

  • dba.createCluster()
  • dba.getCluster()

Both require a global session to the target instance and return a cluster object. However, in the past, a cluster object returned from those functions would become unusable once the Shell session in which the object was created changes to another server. To overcome such issue and increase the usability of the AdminAPI, the cluster object does now hold an internal reference to the session from which it was created or retrieved. This greatly improves the user experience since the global Shell session can be changed without affecting the Cluster object.

SSL options standardized

The connection options passed to MySQL Shell have been renamed to follow the standard defined by MySQL Server:

  • sslMode is now ssl-mode
  • sslCa is now ssl-ca
  • sslCaPath is now ssl-capath
  • sslCert is now ssl-cert
  • sslKey is now ssl-key
  • sslCrl is now ssl-crl
  • sslCrlPath is now ssl-crlpath
  • sslCiphers is now ssl-ciphers
  • sslTlsVersion is now tls-version
  • authMethod is now auth-method

Python support

Up to now, Shell supported Python 2.7 only. However, in favour of multiple platform support, we have improved our support in platforms such as Oracle Enterprise Linux 6, which does not have Python 2.7 available. Therefore, MySQL Shell’s Python support has been extended to support Python 2.6 as well.

Other features

Auto-completion

Auto-complete in consoles has become a standard for many years. MySQL Shell is the unified DevOps tool for MySQL and the frontend for InnoDB cluster, and it now has support for auto-completion of text preceding the cursor by pressing the Tab key.

This is an exciting feature that greatly improves the user experience!

Autocompletion is available for:

  • Built-in MySQL Shell commands, for example, typing
    \con followed by the Tab key completes to \connect.
  • SQL, JavaScript and Python language keywords
    depending on the current MySQL Shell mode.
  • Table names, column names, and active schema names in SQL mode, based on the current default schema.

Autocompletion can be configured using the following command options:

  • --name-cache
  • --no-name-cache
caching_sha2_password

MySQL Shell can now connect to a MySQL server with an account that uses the caching_sha2_password authentication plugin. MySQL Server 8.0.11 GA uses the caching_sha2_password authentication plugin by default, implying that any new accounts created in MySQL use this new plugin.

Assuming the server is configured for encrypted connections, this new authentication method is completely transparent on both X Protocol and classic MySQL protocol.

Note: When using the classic MySQL protocol for connections with such an account and unencrypted connections, you can configure MySQL for password exchange using an RSA key pair. MySQL Shell supports such connections and the following command options have been added:

  • --server-public-key-path option to specify the RSA public key file.
  • --get-server-public-key option to request the public key from the server.

MySQL Router

MySQL Router has been greatly improved, with several new features to overcome common user problems and general usability improvements. In addition, for advanced users, we have introduced features to add extra control over Router’s behaviour, adding extra flexibility and power to InnoDB cluster as the native HA solution for MySQL.

Below are the most important highlights of this release.

Bootstrapping

In the past, the boostraping process required a session to the primary member of an InnoDB cluster. That could be inconvenient if you didn’t know which member was the primary member of the cluster. To improve usability, now the bootstrap process accepts any member of an InnoDB cluster whether it is primary or secondary. Under the hood, Router discovers which member is the primary member of the cluster and automatically reconnects to it.

Routing strategy

By default, Router used the round-robin routing strategy for the read-only instances of an InnoDB cluster and the first-available strategy to the read-write instances.

To enable advanced users to control the routing strategy used by the Router, we have introduced new routing strategies. Router now supports the following:

  • First-available
  • Next-available
  • Round-robin
  • Round-robin-with-fallback

The strategy can be defined with the routing_strategy option.

Concurrent connections

In the past, due to implementation limitations, the Router could not support more than 500 client connection on some platforms. This could be a limiting factor for some InnoDB cluster environments.

In this release, that support has been greatly improved and the Router does now support at least 5000 connections on all the supported platforms.

Connection handling

Depending on your InnoDB cluster setup requirements, you might want to change the default internal operations timeouts. Internal operations refer to the metadata server connections to query the cluster status.

With this in mind, we have introduced two new options to define the internal connection and read timeouts:

  • connect_timeout
  • read_timeout

These options are defined, in the configuration file, under the [DEFAULT] section. Keep in mind that those affect only internal operations.

In addition, the default TTL (Time To Live, in seconds) of the metadata-cache handling has been decreased from 300 to 5. Even though this is configurable, the value was decreased having in mind the common situation where an instance from an InnoDB cluster goes offline and it isn’t updated fast enough in the metadata cache, leading to the connection being routed to that instance when it could be avoided. This is a huge improvement over the Router default routing mechanisms.

Debugging

Debugging information was improved in general, providing extra and more reliable information to the users.

In addition, advanced users that want or need extra debugging information about MySQL Router plugins have now a new utility to help in that process: mysqlrouter_plugin_info.

This utility provides information such as:

  • Plugin version
  • Description
  • ABI version
  • Requirements
  • Function pointers

Router service handling

Handling of the Router service (or process) was also improved.

Support for TERM and INT signals was introduced in order to gracefully shutdown the Router and loaded plugins. On top of that, the restart support was also improved.

In Linux, killing the Router process with SIGINT or SIGTERM is now handled to shut down the process in a clean fashion. Similarly, stopping the Windows service will also gracefully shut down Router.

Recovering instances

InnoDB cluster instances with RECOVERING status are now handled, and likewise instances with UNREACHABLE status. This has been introduced to avoid routing requests to those instances which are still not up-to-date with the cluster data resulting in stale or wrong data reads.

Summary

MySQL InnoDB Cluster, the HA solution for MySQL, brings a whole new set of amazing features and enhancements with MySQL 8.

We have fulfilled our goals for this milestone, but our continuous work to improve the ease-of-use and power of InnoDB cluster certainly isn’t finished yet! InnoDB cluster will not only continue to make MySQL easier for all users, but also give full advanced control and capabilities to power users.
As always, we are eager to listen to the community feedback! So please let us know your thoughts here in the comments, via a bug report, or a support ticket.

In this blog post, we have covered the set of features of two of the InnoDB cluster components: MySQL Shell and MySQL Router, but there’s more! Don’t miss the new features in Replication and MySQL 8.0.

Enjoy, and Thank you for using MySQL!

About Miguel Araújo

Miguel Araújo is a Senior Software Engineer on the MySQL Middleware and Clients Team, at Oracle. He has worked on different projects and teams, mostly related with Middleware and High-Availability. Currently working on the MySQL Shell, leading the AdminAPI developments as part of the MySQL InnoDB Cluster project. He has a Computer Science Engineering degree and Master's degree, from the University of Minho, Portugal, where he was also a researcher. His backgrounds are on distributed systems, scalability, database replication and high-availability.