MySQL :: 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:

mysql-js> dba.checkInstanceConfiguration("root@ic-1:3306")

Please provide the password for 'root@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:

+--------------------------+---------------+----------------+--------------------------------------------------+

+--------------------------+---------------+----------------+--------------------------------------------------+

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

+--------------------------+---------------+----------------+--------------------------------------------------+

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

{

"config_errors": [

{

"action": "server_update",

"current": "CRC32",

"option": "binlog_checksum",

"required": "NONE"

{

"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": ""

}

"errors": [],

"status": "error"

}

Configuring an instance for InnoDB Cluster usage:

mysql-js> dba.configureInstance("root@ic-1:3306")

Please provide the password for 'root@ic-1:3306': ***

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

Some configuration options need to be fixed:

+--------------------------+---------------+----------------+--------------------------------------------------+

+--------------------------+---------------+----------------+--------------------------------------------------+

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

+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y

Do you want to restart the instance after configuring it? [y/n]: y

Configuring instance...

The instance 'ic-1:3306' was configured for cluster usage.

Restarting MySQL...

MySQL server at ic-1:3306 was restarted.

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

mysql-js> dba.checkInstanceConfiguration("root@ic-1:3306")

Please provide the password for 'root@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...

Instance configuration is compatible with InnoDB cluster

The instance 'ic-1:3306' is valid for InnoDB cluster usage.

{

"status": "ok"

}

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;

$ ./bin/mysqlsh root@localhost:3320 --cluster

Creating a session to 'root@localhost:3320'

Enter password: ***

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 36

Server version: 8.0.11-debug Source distribution

No default schema selected; type \use to set one.

MySQL Shell 8.0.11

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type '\help' or '\?' for help; '\quit' to exit.

You are connected to a member of cluster 'devCluster'.

Variable 'cluster' is set.

Use cluster.status() in scripting mode to get status of this cluster or cluster.help() for more commands.

mysql-js> cluster.status()

{

"clusterName": "devCluster",

"defaultReplicaSet": {

"name": "default",

"primary": "localhost:3310",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"localhost:3310": {

"address": "localhost:3310",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"localhost:3320": {

"address": "localhost:3320",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"localhost:3330": {

"address": "localhost:3330",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"groupInformationSourceMember": "mysql://root@localhost:3310"

}

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:

mysql-js> shell.connect("root@ic-1:3306");

shell.connect("root@ic-1:3306");

Please provide the password for 'root@ic-1:3306': ***

Creating a session to 'root@ic-1:3306'

Fetching schema names for autocompletion... Press ^C to stop.

Closing old connection...

Your MySQL connection id is 31

Server version: 8.0.11 MySQL Community Server - GPL

No default schema selected; type \use to set one.

<ClassicSession:root@ic-1:3306>

mysql-js> var cluster = dba.createCluster("testCluster")

A new InnoDB cluster will be created on instance 'root@ic-1:3306'.

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

You may want to kill these sessions to prevent them from performing unexpected updates:

1 open session(s) of 'root@192.168.56.1'.

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

Validating instance at ic-1:3306...

This instance reports its own address as ic-1

Instance configuration is suitable.

Creating InnoDB cluster 'testCluster' on 'root@ic-1:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

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:

Group Replication group name UUID. (group_replication_group_name)
Group Replication local address (group_replication_local_address)
Comma-separated list of the Group Replication seed addresses (group_replication_group_seeds)

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:

mysql-js> var cluster = dba.createCluster("testCluster", {localAddress: "ic-1:44060", groupName: "f82bb252-4327-11e8-842f-0ed5f89f718b"})

A new InnoDB cluster will be created on instance 'root@ic-1:3306'.

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

You may want to kill these sessions to prevent them from performing unexpected updates:

1 open session(s) of 'root@192.168.56.1'.

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

Validating instance at ic-1:3306...

This instance reports its own address as ic-1

Instance configuration is suitable.

Creating InnoDB cluster 'testCluster' on 'root@ic-1:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

mysql-js> \sql

Switching to SQL mode... Commands end with ;

mysql-sql> select @@group_replication_group_name;

+--------------------------------------+

| @@group_replication_group_name |

+--------------------------------------+

| f82bb252-4327-11e8-842f-0ed5f89f718b |

+--------------------------------------+

1 row in set (0.0008 sec)

mysql-js> select @@group_replication_local_address;

+-----------------------------------+

| @@group_replication_local_address |

+-----------------------------------+

| ic-1:44060 |

+-----------------------------------+

1 row in set (0.0009 sec)

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

$ ./bin/mysqlsh --help

MySQL Shell 8.0.11

...

--ssl-key=name X509 key in PEM format.

--ssl-cert=name X509 cert in PEM format.

--ssl-ca=name CA file in PEM format.

--ssl-capath=dir CA directory.

--ssl-cipher=name SSL Cipher to use.

--ssl-crl=name Certificate revocation list.

--ssl-crlpath=dir Certificate revocation list path.

--ssl-mode=mode SSL mode to use, allowed values:

DISABLED,PREFERRED, REQUIRED, VERIFY_CA,

VERIFY_IDENTITY.

--tls-version=version TLS version to use, permitted values are:

TLSv1, TLSv1.1.

--auth-method=method Authentication method to use.

...

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!