Authentication plugin 'caching_sha2_password' cannot be loaded

Symptoms

Logins to phpMyAdmin fail and one of the following errors is displayed.

mysqli_connect(): (HY000/2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/cpanel/3rdparty/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory error.

mysqli_connect(): (HY000/2061): Authentication plugin 'sha256_password' reported error: Authentication requires SSL encryption

Description

MySQL's "Password Validation" component can conflict with various applications, such as phpMyAdmin. Such conflicts will cause mysql user logins to fail.

Workaround

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Run the following command to verify that the "Password Validation" component is not installed. mysql -e "SHOW VARIABLES LIKE 'validate_password%'"
3. If the previous command returns any results, run the following command to uninstall the "Password Validation" component.

   mysql -e "UNINSTALL COMPONENT 'file://component_validate_password'"

4. Run the following command to locate all MySQL users configured to use the caching_sha2_password plugin.

   mysql -e "SELECT User FROM mysql.user WHERE Plugin = 'caching_sha2_password' AND User NOT LIKE 'mysql%';"

   Keep in mind that only the cPanel users need their plugins updated, MySQL system accounts do not need to be modified.
5. Run the following command for each user found by the previous command.

   mysql -e "ALTER USER '$mysqlusername'@'localhost' IDENTIFIED WITH mysql_native_password BY '$newmysqluserpassword'"

   Please note, "$mysqlusername" must be replaced with the MySQL user's username, and "$newmysqluserpassword" must be replaced with the new password for the MySQL user.

Additional resources

Password Validation Component Installation and Uninstallation