2061：Authentication plugin 'caching_sha2_password' reported error:Authentication require secure connection

error occured¶

In MySQL8.0, create a user for master-slave replication:

mysql> CREATE USER 'repl'@'%' identified by 'XXXXXX' 
mysql> GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` 

After configuring the change master information from the library, start the slave to check the replication status, as shown below:

Last_IO_Errno:2061
Last_IO_Error:error connecting to mater 'repl@118.31.127.96:3307' - retry-time:60 retries:1 massage:Authentication plugin 'caching_sha2_password' reported error:Authentication require secure connection. 

caching_sha2_password¶

Before MySQL8.0, the authentication plug-in is mysql_native_password. In MySQL 8.0, caching_sha2_password is the default authentication plug-in, which is more secure.

In MySQL, the system state variable Rsa_public_key, this value is the public key used by the sha256_password authentication plug-in for password exchange based on the RSA key pair. For clients using the sha256_password plug-in, when connecting to the server, the password will never be disclosed in clear text. The method of password transmission depends on whether a secure connection or RSA encryption is used:

1. If the connection is secure, there is no need to use an RSA key pair. This applies to connections encrypted using TLS. The password is sent in clear text, but because the connection is secure, it cannot be monitored.
2. If the connection is not secure and the RSA key pair is available, the connection will remain unencrypted. This applies to connections that are not encrypted using TLS. RSA is only used for password exchange between client and server to prevent password monitoring. After the server receives the encrypted password, it decrypts it. Scrambling is used in encryption to prevent repeated attacks.
3. If a secure connection is not used and RSA encryption is not available, the connection attempt will fail because the password cannot be sent without exposing the password as clear text.
   The public key value in the file named by the —server-public-key-path option should be the same as the key value in the server-side file named by the caching_sha2_password_public_key_path system variable. If the key file contains a valid public key value, but the value is incorrect, an access denied error will occur. If the key file does not contain a valid public key, the client program cannot use it.

cause of issue¶

Through the introduction of the plug-in caching_sha2_password above, the cause of this failure can be guessed as: the RSA public key that is not recognized by caching_sha2_password was used when connecting from the library to the main library, so the main library MySQL rejected the database connection request, thus , From the library report error'caching_sha2_password' reported error: Authentication require secure connection.

According to Article 3 of the previous password transmission method, the plug-in found that the connection is not encrypted, so RSA encryption is required to transmit the password. However, the server does not send the public key to the client, and the client does not provide the public key, so it cannot encrypt the password and the connection fails:

ERROR 2061 (HY000): Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

The solutions given by the official website are as follows:

To request the RSA public key from the server, you need to specify the option --get-server-public-key.

Request the public key required for RSA key pair-based password exchange from the server. This option is suitable for clients that use the caching_sha2_password authentication plugin for authentication. For this plug-in, the server will not send the public key unless requested. For accounts that are not authenticated with the plugin, this option will be ignored. If RSA-based password exchange is not used, it will also be ignored, such as when the client uses a secure connection to connect to the server.

Or, if the client's file contains a local copy of the RSA public key required by the server, you can use the —server-public-key-path option to specify the file.

The path name of the file in PEM format, which contains the client copy of the public key required by the server for password exchange based on the RSA key pair. This option is suitable for clients that use sha256_password or caching_sha2_password authentication plugins for authentication.

Solution one:¶

Use the copy user to request the server public key:

mysql -u repl -p123 -h 118.31.127.96 -P3307 --get-server-public-key

In this case, the server sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plug-in uses the RSA private key on the server side to decrypt the password, and accepts or rejects the connection based on whether the password is correct.

Reconfigure change masrer to and start slave in the slave library, replication can start normally:

#Stop master-slave replication
#Empty the previous master-slave replication configuration information
stop slave;
reset slave;

#From the new configuration master-slave replication
change master to master_user='repl',master_password='123',master_host='118.31.127.96',master_port=3307,master_auto_position=1;
start slave;

Solution two¶

Use the copy user to request the server public key:

mysql -u repl -p123 -h 118.31.127.96 -P3307 --server-public-key-path=/mysqldata/my3308/data/public_key1.pem

Reconfigure change masrer to and start slave in the slave library, replication can start normally:

#Stop master-slave replication
#Empty the previous master-slave replication configuration information
stop slave;
reset slave;

#From the new configuration master-slave replication
change master to master_user='repl',master_password='123',master_host='118.31.127.96',master_port=3307,master_auto_position=1;
start slave;

Solution three:¶

According to the plan provided by the community, modify the copy account to avoid using the plug-in cache_sha2_password.

1. Modify the repl user to use another secret encryption method instead of the plug-in caching_sha2_password.
2.

CREATE USER'repl'@'%' IDENTIFIED WITH'mysql_native_password' BY'XXXX';
GRANT REPLICATION SLAVE ON *.* TO'repl'@'%';

#Check copy account
select user,host,plugin,authentication_string from user \G
*************************** 4. row ******************** *******
                  user: repl
                  host:%
                plugin: mysql_native_password
authentication_string: *B2A7A5489FB0EE54E43E3ADCDDVDG5CCF255AF0

#Reconfigure master-slave configuration

Summarize¶

ERROR 2061 (HY000): Authentication plugin caching_sha2_password reported error: Authentication requires secure connection. This is because the replication account repl is not encrypted to connect to the main library, so the main library rejects the connection that displays the password in plain text during the transmission process.

There are three solutions, as above. I personally think that solution one and solution two are more effective than solution three. You don't need to modify any user information. You only need to request the public key through --get-server-public-key and --server-public-key-path.
Solution three completely avoids the use of the MySQL8.0 password plug-in caching_sha2_password.