MySQL Shell 9.3
The member servers in an InnoDB Cluster make use of three types of user accounts. One InnoDB Cluster server configuration account is used to configure the server instances for the cluster. One or more InnoDB Cluster administrator accounts can be created for administrators to manage the server instances after the cluster has been set up. One or more MySQL Router accounts can be created for MySQL Router instances to connect to the cluster. Each of the user accounts must exist on all of the member servers in the InnoDB Cluster, with the same user name and the same password.
This account is used to create and configure the member
servers of an InnoDB Cluster. Each member server has only
one server configuration account. The same user account name
and password must be used on every member server in the
cluster. You can use the root account on
the servers for this purpose, but if you do this, the
root account on every member server in
the cluster must have the same password. This is not
recommended for security reasons.
The preferred approach is to create the InnoDB Cluster
server configuration account using a
dba.configureInstance() command with the
clusterAdmin option. For better security,
specify the password at the interactive prompt, otherwise
specify it using the clusterAdminPassword
option. Create the same account, with the same user name and
password, in the same way on every server instance that will
be part of the InnoDB Cluster - both the instance to which
you connect to create the cluster, and the instances that
will join the cluster after that.
The dba.configureInstance() command
grants the account the required permissions automatically.
You may set up the account manually if you prefer, granting
it the permissions listed in
Configuring InnoDB Cluster Administrator Accounts Manually. The account
needs full read and write privileges on the InnoDB Cluster
metadata tables, in addition to full MySQL administrator
privileges.
The InnoDB Cluster server configuration account that you
create using the dba.configureInstance()
operation is not replicated to other
servers in the InnoDB Cluster. MySQL Shell disables
binary logging for the
dba.configureInstance() operation. This
means that you must create the account on every server
instance individually.
These accounts can be used to administer an InnoDB Cluster after you have completed the configuration process. You can set up more than one of them. Each account must exist on every member server in an InnoDB Cluster with the same user name and password.
To create an InnoDB Cluster administrator account for an
InnoDB ClusterSet deployment, you issue a
cluster.setupAdminAccount()cluster.setupAdminAccount()
If the primary InnoDB Cluster was set up by a
MySQL Shell version before MySQL Shell 8.0.20, the
cluster.setupAdminAccount()update option to update the privileges of
the InnoDB Cluster server configuration account. This is
a special use of the command that is not written to the
binary log.
These accounts are used by MySQL Router to connect to server
instances in an InnoDB Cluster. You can set up more than
one of them. Each account must exist on every member server
in an InnoDB Cluster with the same user name and password.
The process to create a MySQL Router account is the same as for
an InnoDB Cluster administrator account, but using a
cluster.setupRouterAccount()
If you want to manually configure an InnoDB Cluster
administration user, that user requires the privileges listed
here, all with GRANT OPTION.
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 set up
administration accounts is using the
dba.configureInstance() or
cluster.setupAdminAccount()
Each account used to administer an InnoDB Cluster, InnoDB ClusterSet, or InnoDB ReplicaSet deployment must exist on all the member server instances in the deployment, with the same user name, and the same password.
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.
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.
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 retrieve the privileges required by
the user your_user to monitor
InnoDB Cluster run the following command:
dba.configureInstance('root:password@localhost:3306',{'clusterAdmin':'your_user'})
where root is either the root user or a user with the required
privileges to create users with the required privileges, and
your_user is the InnoDB Cluster
administrator user you want to create. The command returns the
full list of privileges required by the administrator user.
For more information, see Account Management Statements.
As part of using Group Replication, InnoDB Cluster creates
internal recovery users which enable connections between the
servers in the cluster. These users are internal to the cluster,
and the user name of the generated users follows a naming scheme
of
mysql_innodb_cluster_,
where server_id@%server_id is unique to
the instance. In versions earlier than 8.0.17 the user name of
the generated users followed a naming scheme of
mysql_innodb_cluster_r[.
10_numbers]
The hostname used for these internal users is set to '%'. For more information, see Creating an Allowlist of Servers.
Each internal user has a randomly generated password. AdminAPI enables you to change the generated password for internal users. See Resetting Recovery Account Passwords. The randomly generated users are given the following grants:
GRANT REPLICATION SLAVE ON *.* to internal_user;The internal user accounts are created on the seed instance and then replicated to the other instances in the cluster. The internal users are:
Generated when creating a new cluster by issuing
dba.createCluster()
Generated when adding a new instance to the cluster by
issuing
Cluster.addInstance()
Generated using the authentication plugin in use by the primary member
For more information on the internal users required by Group Replication, see User Credentials For Distributed Recovery.
You can use the
Cluster.resetRecoveryAccountsPassword()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.