MySQL :: MySQL Shell 8.0 :: 6.2.10 InnoDB Cluster Tips

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:

Global privileges on *.* for RELOAD, SHUTDOWN, PROCESS, FILE, SELECT, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, REPLICATION_APPLIER, CREATE USER, SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, BACKUP_ADMIN, CLONE_ADMIN, and EXECUTE.

Note

SUPER includes the following required privileges: SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN.
Schema specific privileges for mysql_innodb_cluster_metadata.*, mysql_innodb_cluster_metadata_bkp.*, and mysql_innodb_cluster_metadata_previous.* are ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE; and for mysql.* are INSERT, UPDATE, DELETE.

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 mysql.slave_master_info TO your_user@'%';
GRANT SELECT ON performance_schema.global_status TO your_user@'%';
GRANT SELECT ON performance_schema.global_variables 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 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:

If the group is running in single-primary mode, then set auto_increment_increment to 1 and auto_increment_offset to 2.
If the group is running in multi-primary mode, then when the cluster has 7 instances or less set auto_increment_increment to 7 and auto_increment_offset to 1 + server_id % 7. If a multi-primary cluster has 8 or more instances set auto_increment_increment to the number of instances and auto_increment_offset to 1 + server_id % the number of instances.

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