The MySQL Development Team is very happy to announce a new 8.0 Maintenance Release of InnoDB Cluster – 8.0.18.

In addition to major quality improvements, 8.0.18 brings some very useful features!

This blog post will only cover InnoDB cluster’s frontend and control panel – MySQL Shell and its AdminAPI – Stay tuned for other blog posts covering MySQL Router and Group Replication!

MySQL Shell AdminAPI

For this release, we have focused on three key-points of InnoDB cluster management:

• Networking: by extending the AdminAPI to fully leverage the cluster’s capabilities to use IPv6.
• Security: by providing a new command to refresh/reset the cluster’s internal recovery accounts passwords.
• Troubleshooting/observability: by extending Shell’s logging system to provide information about all SQL statements executed by the AdminAPI, and also by including information about replication-lag in the cluster’s status command.

And as always, quality has been greatly improved with many bugs fixed!

Full IPv6 support

MySQL InnoDB Cluster relies on Group Replication to provide the mechanism to replicate data within clusters, thereby, a network established between the cluster members is a foundational requirement. For that reason, support for the most recent network technologies is certainly a desired feature for InnoDB cluster deployments.

The most recent and important change in networking technologies is the “new” version of the Internet Protocol: IPv6. It emerged mostly from the growing concern about IPv4 address exhaustion. But additionally, it brought several features and improvements that may be of much interest in clustering solutions, such as:

• Stateless address auto-configuration (SLAAC)
• Improved security
• Easier administration
• Better/simplifies multicasting
• Simplified/more efficient routing
• etc.

To keep the pace with the most recent improvement and to provide the users’ freedom to choose which networking technologies better fits their needs, Group Replication has been extended to support IPv6. However, up to now, the AdminAPI was limited to IPv4 usage.

In this release, we have extended the AdminAPI to fully support IPv6 addresses on all of its commands. Specifically, if the target MySQL Server version is equal or higher to 8.0.14, IPv6 addresses can now be used.

To improve the usability and guiding capabilities of the AdminAPI, the following commands were extended to detect if the target instance is resolving to an IPv6 address and  if so, if that is supported or not in that particular cluster version:

• dba.checkInstanceConfiguration()
• dba.configureInstance()
• dba.configureLocalInstance()

Likewise, the cluster topology operations will verify that prerequisite and also support the usage of IPv6 addresses when applicable:

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

The following snippet, shows a typical example of the operation to add an instance to a cluster using an IPv6 address:

Finally, every network-related configuration of a cluster now supports IPv6 addresses. Namely:

• localAddress
• groupSeeds
• ipWhitelist

For example, creating a cluster specifying an IP whitelist:

And of course, the status of a cluster that is using IPv6 on its network:

Refresh/reset internal cluster recovery accounts

In the previous release, we improved the management of the internal recovery accounts, for a much simpler, secure and manageable account handling.

Just to refresh your memory, Group Replication requires a so-called recovery account to perform distributed recovery, but the AdminAPI handles the creation and management of those accounts automatically and transparently for you. The credentials for those accounts are based on unique and secure randomly generated passwords that are not stored anywhere for security reasons.

This fits most use-cases, however, security policies can be quite strict on some deployments. Even though the passwords are secure and not persisted, policies of password lifetime may be in place forcing every single account to be changed periodically to use a new password.

Up to 8.0.17, changing the recovery accounts passwords of a cluster was a troublesome and lengthy procedure. But considering that the automatic handling of those accounts was one of the goals of the AdminAPI, we have introduced a new simple command to refresh and reset the internal cluster recovery accounts credentials:

• <Cluster>.resetRecoveryAccountsPassword()

You can now use this operation to reset the passwords for all internal recovery accounts used by the cluster. The command ensures that a new random and secure password is set for the recovery account of every cluster member.

Password lifetime policies, or any other security reason, are no longer a problem in InnoDB cluster as a single command can refresh the passwords, at any time:

As simple as that!

Log AdminAPI queries (SQL)

I don’t want to scare you but… “Anything that can go wrong will go wrong.”. Murphy’s law applies to computer science too, right? 🙂

Troubleshooting is one of the main tasks of a DBA, which we keep in mind and try to make it easy and straightforward with the AdminAPI. And also, we do our best to provide valuable information in Shell’s default interactive output and log. But sometimes, extra detailed information is needed for troubleshooting…

Shell’s log file can provide you with important information about what’s happening under-the-hood and what has gone wrong. By default, error, warning and informational messages are logged, however, the detail level is controllable.

Nevertheless, the AdminAPI executes a large number of SQL queries for its management operations and those are not logged. Therefore, we have extended the monitoring and troubleshooting capabilities of MySQL Shell to provide the means to log all SQL statements that the AdminAPI executes.

A new Shell option, named dba.logSql, was added to enable logging of the SQL statements executed by the AdminAPI operations.

This option is changeable in runtime, using:

Or it can be set in the command-line, by using: --dba-log-sql

$ mysqlsh myAdmin@localhost:3306 --dba-log-sql=2

The option can take one of the following possible values:

• 0 (default): no logging
• 1: log all queries except SELECT and SHOW
• 2: log all queries

Hint: Combine the option with --verbose to get the SQL logged but also printed in the interactive output.

Replication Lag

The replication machinery built-in in InnoDB cluster is provided by Group Replication, which relies on a Group Communication system that ensures total ordering. For that reason, Group Replication is considered to be virtually synchronous.

So in a simplistic vision of it, there are two distinct phases:

1. A transaction comes in: Coordination of all cluster members happens, ensuring that the commit is received and will be processed by all cluster members.
2. The transaction is applied: Each individual cluster member has agreed on processing the commit so it proceeds to apply it. The applier thread kicks-in and it processes the transaction in the database.

As you can conclude by now, the second phase may take more or less time depending on each individual instance. CPU power, memory, disk, and so on are factors that influence the processing speed. For that reason, Group Replication is considered virtually synchronous.

In order to monitor the time between the most recent transaction being committed and being applied to a cluster member, we have extended the output of <Cluster>.status() with a new field named replicationLag.

The value of this field is displayed in HH:mm:ss.SSSSSS format and shows the time difference between the last transaction commit timestamp and the last transaction applied timestamp.

Notable Bugs fixed

For the full list of bugs fixed in this release, please consult the changelog. However, there are some that deserve being mentioned.

BUG#30174191 – Group Replication AUTO_INCREMENT policy does not follow correct behavior

The <Cluster>.rejoinInstance() operation was not setting the auto-increment values defined for InnoDB cluster, leading to the use of the default Group Replication behavior if the instance configuration was not properly persisted, for example on 5.7 servers. The fix ensures that the <Cluster>.rejoinInstance() operation updates the auto-increment settings of the target instance.

This was an important bug fix to ensure the right values of auto-increment-offset and auto-increment-increment are set accordingly in the whole cluster.

BUG#29954085 – Stuck trying to use clone recovery when cluster created using old shell

<Cluster>.addInstance() did not ensure that the MySQL Clone plugin was installed or loaded on all cluster instances, when available and not disabled. This meant that whenever a cluster was created using an older MySQL Shell version, on a target instance supporting clone, the instance would not have the clone plugin installed. This would eventually result in an error whenever <Cluster>.addInstance() was used with a MySQL Shell version >= 8.0.17, and clone was selected as the recovery method.

The same situation would happen if incremental recovery was used to add an instance to a cluster of just 1 member and afterward the seed instance (that had clone installed) was removed. This would result in all cluster members not having clone installed and thereby any instance added using the clone recovery method would fail

The bug fix ensures that the clone plugin is installed on all cluster members (if available and not disabled) at cluster creation time and also whenever an instance is added to a cluster.

BUG#29953812 – add_instance() picky about GTID_EXECUTED, rejoin_instance() not: data not copied

<Cluster>.addInstance() checks the GTID consistency of the target instance in regards to the cluster, to guide the user accordingly to the best recovery method. However, <Cluster>.rejoinInstance() was not checking the GTID consistency of an instance being rejoined to a cluster, accepting instances with a diverged GTID set which would result in an error.

This bug fix implements and improves the GTID consistency checks in the <Cluster>.rejoinInstance() operation to check for irrecoverable or diverged data-sets and also for empty GTID sets. If an instance is found to not be consistent with the cluster, it is not rejoined and the operation fails with a descriptive error. On top of that, the command shows the list of errant transactions, possible outcomes, and solutions.

BUG#29648806 – Cluster.describe() incorrectly gathering information directly from the session

<Cluster>.describe()is meant to display to the users the information about how was the cluster configured and how it should look like. This information is stored in the Metadata Schema, so the command shall only consult this schema. However, some information about the cluster’s topology and the MySQL version installed on instances was being retrieved directly from the current session.

This bug fix ensures that the information is retrieved from the Metadata schema only, and the MySQL version is not included in the information output by <Cluster>.describe().

BUG#25503159 – Addinstance() fails, creates replication accounts / user but does not clean up

The <Cluster>.addInstance() operation creates internal recovery users which are required by the Group Replication recovery process, as described below and in the previous blog post.

But up to now, if the <Cluster>.addInstance() operation failed, for example, because Group Replication could not start, the created recovery users were not removed.

This bug fix ensures that, in the event of failure, any internal users are removed.

BUG#30171090 / BUG#96489 – Mysql shell configureinstance does not set BINLOG_CHECKSUM = NONE

The <Cluster>.configureInstance() operation was not setting the binlog_checksum option with the required value (NONE) in the configuration file for instances that did not support SET PERSIST when the configuration file path was not provided as input parameter but instead specified through the wizard.

This bug fix ensures that all options are persisted in the configuration file, regardless if they are read-only or not, if the SET PERSIST feature is not supported by the instance (for example instances running MySQL 5.7).

Try it now and send us your feedback

MySQL Shell 8.0.18 GA is available for download from the following links:

• MySQL Community Downloads website: https://dev.mysql.com/downloads/shell/
• MySQL Shell is also available on GitHub: https://github.com/mysql/mysql-shell

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

You can also reach us at #shell and #mysql_innodb_cluster in Slack: https://mysqlcommunity.slack.com/

The documentation of MySQL Shell can be found in https://dev.mysql.com/doc/mysql-shell/8.0/en/ and the official documentation of InnoDB cluster can be found in the MySQL InnoDB Cluster User Guide.

The full list of changes and bug fixes can be found in the 8.0.18 Shell Release Notes.

Enjoy, and Thank you for using MySQL!