This section describes some information which is good to know when using InnoDB Cluster.
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
.
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(
operation, which creates or upgrades a MySQL user account with the necessary privileges to administer an InnoDB Cluster or InnoDB ReplicaSet.user
)In versions prior to 8.0.20, the preferred method to create users for administration is using the
clusterAdmin
option with thedba.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
, andEXECUTE
.NoteSUPER
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.*
, andmysql_innodb_cluster_metadata_previous.*
areALTER
,ALTER ROUTINE
,CREATE
,CREATE ROUTINE
,CREATE TEMPORARY TABLES
,CREATE VIEW
,DELETE
,DROP
,EVENT
,EXECUTE
,INDEX
,INSERT
,LOCK TABLES
,REFERENCES
,SHOW VIEW
,TRIGGER
,UPDATE
; and formysql.*
areINSERT
,UPDATE
,DELETE
.
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.
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 andauto_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 andauto_increment_offset
to 1 +server_id
% 7. If a multi-primary cluster has 8 or more instances setauto_increment_increment
to the number of instances andauto_increment_offset
to 1 +server_id
% the number of instances.
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
and so on might fail.
Cluster
.addInstance()
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 ofexpire_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.
From version 8.0.18, you can use the
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 Cluster
.resetRecoveryAccountsPassword()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.
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.