Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
PDF (RPM) - 39.9Mb
HTML Download (TGZ) - 10.6Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.2Mb
Man Pages (TGZ) - 241.2Kb
Man Pages (Zip) - 346.4Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  InnoDB Cluster Tips

21.2.10 InnoDB Cluster Tips

This section describes some information which is good to know when using InnoDB Cluster.

Super Read-only and Instances

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.

Configuring Users for AdminAPI

Working with instances that belong to an InnoDB Cluster or InnoDB ReplicaSet requires that you connect to the instances with a user that has the correct privileges to administer the instances. AdminAPI provides the following ways to administer suitable users:

  • In version 8.0.20 and later, use the setupAdminAccount(user) operation, which creates or upgrades a MySQL user account with the necessary privileges to administer an InnoDB Cluster or InnoDB ReplicaSet.

  • In versions prior to 8.0.20, the preferred method to create users for administration is using the clusterAdmin option with the dba.configureInstance() operation.

For more information, see Creating User Accounts for Administration. If you want to manually configure an administration user, that user requires the following privileges, all with GRANT OPTION:

Note

This list of privileges is based on the current version of MySQL Shell. The privileges are subject to change between releases. Therefore the recommended way to administer accounts is using the operations described at Creating User Accounts for Administration

If only read operations are needed, for example to create a user for monitoring purposes, an account with more restricted privileges can be used. To give the user your_user the privileges needed to monitor InnoDB Cluster issue:

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO your_user@'%';
GRANT SELECT ON performance_schema.global_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO your_user@'%';
GRANT SELECT ON performance_schema.threads TO your_user@'%' WITH GRANT OPTION;

For more information, see Section 13.7.1, “Account Management Statements”.

InnoDB Cluster and Auto-increment

When you are using an instance as part of an InnoDB Cluster, the auto_increment_increment and auto_increment_offset variables are configured to avoid the possibility of auto increment collisions for multi-primary clusters up to a size of 9 (the maximum supported size of a Group Replication group). The logic used to configure these variables can be summarized as:

InnoDB Cluster and Binary Log Purging

In MySQL 8, the binary log is automatically purged (as defined by binlog_expire_logs_seconds). This means that a cluster which has been running for a longer time than binlog_expire_logs_seconds could eventually not contain an instance with a complete binary log that contains all of the transactions applied by the instances. This could result in instances needing to be provisioned automatically, for example using MySQL Enterprise Backup, before they could join the cluster. Instances running 8.0.17 and later support the MySQL Clone plugin, which resolves this issue by providing an automatic provisioning solution which does not rely on incremental recovery, see Section 21.2.2.2, “Using MySQL Clone with InnoDB Cluster”. Instances running a version earlier than 8.0.17 only support incremental recovery, and the result is that, depending on which version of MySQL the instance is running, instances might have to be provisioned automatically. Otherwise operations which rely on distributed recovery, such as Cluster.addInstance() and so on might fail.

On instances running earlier versions of MySQL the following rules are used for binary log purging:

  • Instances running a version earlier than 8.0.1 have no automatic binary log purging because the default value of expire_logs_days is 0.

  • Instances running a version later than 8.0.1 but earlier than 8.0.4 purge the binary log after 30 days because the default value of expire_logs_days is 30.

  • Instances running a version later than 8.0.10 purge the binary log after 30 days because the default value of binlog_expire_logs_seconds is 2592000 and the default value of expire_logs_days is 0.

Thus, depending on how long the cluster has been running binary logs could have been purged and you might have to provision instances manually. Similarly, if you manually purged binary logs you could encounter the same situation. Therefore you are strongly advised to upgrade to a version of MySQL later than 8.0.17 to take full advantage of the automatic provisioning provided by MySQL Clone for distributed recovery, and to minimize downtime while provisioning instances for your InnoDB Cluster.

Resetting Recovery Account Passwords

From version 8.0.18, you can use the Cluster.resetRecoveryAccountsPassword() operation to reset the passwords for the internal recovery accounts created by InnoDB Cluster, for example to follow a custom password lifetime policy. Use the Cluster.resetRecoveryAccountsPassword() operation to reset the passwords for all internal recovery accounts used by the cluster. The operation sets a new random password for the internal recovery account on each instance which is online. If an instance cannot be reached, the operation fails. You can use the force option to ignore such instances, but this is not recommended, and it is safer to bring the instance back online before using this operation. This operation only applies to the passwords created by InnoDB cluster and cannot be used to update manually created passwords.

Note

The user which executes this operation must have all the required administer privileges, in particular CREATE USER, in order to ensure that the password of recovery accounts can be changed regardless of the password verification-required policy. In other words, independent of whether the password_require_current system variable is enabled or not.