Insecure Identity Authentication When Setting Up the MySQL+MGR Single-Primary Mode

Symptom

During the setup of MySQL 8.0.18+MGR single-primary mode, the message "Authentication requires secure connection" is displayed after the START GROUP_REPLICATION; statement is executed.

Key Process and Cause Analysis

MySQL 8.0 uses the caching_sha2_password authentication mechanism by default. If you use the default authentication mechanism to create a user, an error is reported when you connect to a remote node.

Conclusion and Solution

1. Change mysql_native_password to caching_sha2_password. Run the following statements in the database:

   SET SQL_LOG_BIN=0;
   alter user 'repl'@'%' IDENTIFIED WITH mysql_native_password  BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' ;
   SET SQL_LOG_BIN=1;

2. Run the following statement to start MGR:

   START GROUP_REPLICATION;