7.3 User Accounts for InnoDB Cluster

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.

InnoDB Cluster server configuration account

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.

InnoDB Cluster administrator accounts

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() command after you have added all the instances to that cluster. The command creates an account with the user name and password that you specify, with all the required permissions. A transaction to create an account with cluster.setupAdminAccount() is written to the binary log and sent to all the other server instances in the cluster to create the account on them.

Note

If the primary InnoDB Cluster was set up in a version before MySQL Shell 8.0.20, the cluster.setupAdminAccount() command might have been used with the 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.

MySQL Router accounts

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() command. For instructions to create or upgrade a MySQL Router account, see Section 6.10.2, “Configuring the MySQL Router User”.

Configuring InnoDB Cluster Administrator Accounts Manually

If you want to manually configure an InnoDB Cluster administration user, that user requires the privileges listed here, 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 set up administration accounts is using the dba.configureInstance() or cluster.setupAdminAccount() operation.

Important

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

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 mysql.user 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.

Internal User Accounts Created by InnoDB Cluster

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_server_id@%, where 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 '%'. Before v8.0.17, the ipAllowlist affected hostname behavior by creating one account per host in the ipAllowlist. For more information, see Creating an Allowlist of Servers.

Each internal user has a randomly generated password. From version 8.0.18, 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

Before v8.0.17, ipAllowlist caused Cluster.rejoinInstance() to remove old internal users and generate new ones instead of reusing them.

For more information on the internal users required by Group Replication, see User Credentials For Distributed Recovery.

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.