20.6.3.1 Secure User Credentials for Distributed Recovery

State transfer from the binary log requires a replication user with the correct permissions so that Group Replication can establish direct member-to-member replication channels. The same replication user is used for distributed recovery on all the group members. If group members have been set up to support the use of a remote cloning operation as part of distributed recovery, this replication user is also used as the clone user on the donor, and requires the correct permissions for this role. For detailed instructions to set up this user, see Section 20.2.1.3, “User Credentials For Distributed Recovery”.

To secure the user credentials, you can require SSL for connections with the user account, and provide the user credentials when Group Replication is started, rather than storing them in the replica status tables. Also, if you are using caching SHA-2 authentication, you must set up RSA key-pairs on the group members.

Important

When using the MySQL communication stack (group_replication_communication_stack=MYSQL) AND secure connections between members (group_replication_ssl_mode is not set to DISABLED), the recovery users must be properly set up, as they are also the users for group communications. Follow the instructions in Section 20.6.3.1.2, “Replication User With SSL” and Section 20.6.3.1.3, “Providing Replication User Credentials Securely”.

20.6.3.1.1 Replication User With The Caching SHA-2 Authentication Plugin

By default, users created in MySQL 8 use Section 8.4.1.1, “Caching SHA-2 Pluggable Authentication”. If the replication user you configure for distributed recovery uses the caching SHA-2 authentication plugin, and you are not using SSL for distributed recovery connections, RSA key-pairs are used for password exchange. For more information on RSA key-pairs, see Section 8.3.3, “Creating SSL and RSA Certificates and Keys”.

In this situation, you can either copy the public key of the rpl_user to the joining member, or configure the donors to provide the public key when requested. The more secure approach is to copy the public key of the replication user account to the joining member. Then you need to configure the group_replication_recovery_public_key_path system variable on the joining member with the path to the public key for the replication user account.

The less secure approach is to set group_replication_recovery_get_public_key=ON on donors so that they provide the public key of the replication user account to joining members. There is no way to verify the identity of a server, therefore only set group_replication_recovery_get_public_key=ON when you are sure there is no risk of server identity being compromised, for example by a man-in-the-middle attack.

20.6.3.1.2 Replication User With SSL

A replication user that requires an SSL connection must be created before the server joining the group (the joining member) connects to the donor. Typically, this is set up at the time you are provisioning a server to join the group. To create a replication user for distributed recovery that requires an SSL connection, issue these statements on all servers that are going to participate in the group:

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'rec_ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rec_ssl_user'@'%';
mysql> GRANT CONNECTION_ADMIN ON *.* TO 'rec_ssl_user'@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO 'rec_ssl_user'@'%';
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rec_ssl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

Note

The GROUP_REPLICATION_STREAM privilege is required when using both the MySQL communication stack (group_replication_communication_stack=MYSQL) and secure connections between members (group_replication_ssl_mode not set to DISABLED). See Section 20.6.1, “Communication Stack for Connection Security Management”.

20.6.3.1.3 Providing Replication User Credentials Securely

To supply the user credentials for the replication user, you can set them permanently as the credentials for the group_replication_recovery channel, using a CHANGE REPLICATION SOURCE TO statement. Alternatively, you can specify them in the START GROUP_REPLICATION statement each time Group Replication is started. User credentials specified in START GROUP_REPLICATION take precedence over any user credentials that have been set using a CHANGE REPLICATION SOURCE TO statement.

User credentials set using CHANGE REPLICATION SOURCE TO are stored in plain text in the replication metadata repositories on the server, but user credentials specified on START GROUP_REPLICATION are saved in memory only, and are removed by a STOP GROUP_REPLICATION statement or server shutdown. Using START GROUP_REPLICATION to specify the user credentials therefore helps to secure the Group Replication servers against unauthorized access. However, this method is not compatible with starting Group Replication automatically, as specified by the group_replication_start_on_boot system variable.

If you want to set the user credentials permanently using a CHANGE REPLICATION SOURCE TO statement, issue this statement on the member that is going to join the group:

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rec_ssl_user', 
    ->   SOURCE_PASSWORD='password' 
    ->   FOR CHANNEL 'group_replication_recovery';

To supply the user credentials on START GROUP_REPLICATION, issue this statement when starting Group Replication for the first time, or after a server restart:

mysql> START GROUP_REPLICATION USER='rec_ssl_user', PASSWORD='password';

Important

If you switch to using START GROUP_REPLICATION to specify user credentials on a server that previously supplied the credentials using CHANGE REPLICATION SOURCE TO, you must complete the following steps to get the security benefits of this change.

1. Stop Group Replication on the group member using a STOP GROUP_REPLICATION statement. Although it is possible to take the following two steps while Group Replication is running, you need to restart Group Replication to implement the changes.

2. Set the value of the group_replication_start_on_boot system variable to OFF (the default is ON).

3. Remove the distributed recovery credentials from the replica status tables by issuing this statement:

   mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='', SOURCE_PASSWORD='' 
       ->   FOR CHANNEL 'group_replication_recovery';

4. Restart Group Replication on the group member using a START GROUP_REPLICATION statement that specifies the distributed recovery user credentials.

Without these steps, the credentials remain stored in the replica status tables, and can also be transferred to other group members during remote cloning operations for distributed recovery. The group_replication_recovery channel could then be inadvertently started with the stored credentials, on either the original member or members that were cloned from it. An automatic start of Group Replication on server boot (including after a remote cloning operation) would use the stored user credentials, and they would also be used if an operator did not specify the distributed recovery credentials as part of START GROUP_REPLICATION.