When working with MySQL 8.0, you might encounter the following issue during remote connection attempts:

# mysql -h10.254.73.103
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

This error arises due to MySQL 8.0's default authentication plugin being updated to 'caching_sha2_password'. Let's delve deeper into this problem and understand its cause based on MySQL’s official documentation:

Understanding the Authentication Plugin Update

MySQL 8.0 introduced caching_sha2_password and sha256_password as alternatives to the mysql_native_password plugin. These newer plugins provide enhanced security for password encryption. Furthermore, caching_sha2_password offers better performance compared to sha256_password. Due to these benefits, caching_sha2_password has been set as the default authentication plugin starting from MySQL 8.0.

While this update helps improve security, it can also lead to compatibility issues with older clients that do not support the updated authentication mechanism. To address this, MySQL allows changing the authentication plugin from caching_sha2_password back to mysql_native_password on the server if necessary. However, solving the issue directly on the client side is often a more desirable solution.

Resolving the Error from the Client Side

To eliminate the error, you can update or install a compatible MySQL client library. Below is the process for CentOS 7.

Step 1: Download the MySQL Repository

Begin by downloading the MySQL repository configuration package tailored for CentOS:

# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

Step 2: Install the Repository Configuraton

Install the downloaded RPM package to set up the MySQL repository on your system:

# yum install mysql80-community-release-el7-3.noarch.rpm

Step 3: Search for Client Libraries

Use yum to search for available MySQL client libraries:

# yum list all | grep mysql | grep client
mysql-community-client.i686       8.0.21-1.el7           mysql80-community
mysql-community-client.x86_64     8.0.21-1.el7           mysql80-community

Step 4: Install the Updated Client Library

Install the MySQL client that supports the authentication plugin:

# yum install mysql-community-client -y

Step 5: Test the Connection

After completing the installation, test the remote connection using the MySQL client:

# mysql -uroot -p123456 -h10.254.73.103
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
mysql> exit
Bye

If no errors occur and the connection is successful, you’ve resolved the issue.

Conclusion

This approach resolves error 2059 by updating the client to a version compatible with the caching_sha2_password authentication plugin. Using this method ensures you retain the enhanced security features introduced in MySQL 8.0 while avoiding the need to modify server-side authentication settings.