How to Resolve Unable to Load Authentication Plugin 'Caching_Sha2_Password' Issue

Authentication plugin 'caching_sha2_password' cannot be loaded

You can change the encryption of the user's password by altering the user with below Alter command :

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY
'password';

OR

We can avoid this error by make it work with old password plugin:

First change the authentication plugin in my.cnf file for Linux / my.ini file in Windows:

[mysqld]

default_authentication_plugin=mysql_native_password

Restart the mysql server to take the changes in affect and try connecting via MySQL with any mysql client.

If still unable to connect and getting the below error:

Unable to load plugin 'caching_sha2_password'

It means your user needs the above plugin. So try creating new user with create user or grant command after changing default plugin. then new user need the native plugin and you will able to connect MySQL.

Thanks

Authentication plugin 'caching_sha2_password' is not supported

Per Caching SHA-2 Pluggable Authentication

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

You're using mysql_native_password, which is no longer the default. Assuming you're using the correct connector for your version you need to specify the auth_plugin argument when instantiating your connection object

cnx = mysql.connector.connect(user='lcherukuri', password='password',
                              host='127.0.0.1', database='test',
                              auth_plugin='mysql_native_password')

From those same docs:

The connect() method supports an auth_plugin argument that can be used to force use of a particular plugin. For example, if the server is configured to use sha256_password by default and you want to connect to an account that authenticates using mysql_native_password, either connect using SSL or specify auth_plugin='mysql_native_password'.

Cannot connect to mysql : Unable to load authentication plugin 'caching_sha2_password

MyISAM is based on the older (and no longer available) ISAM storage
engine

... See https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

There are several checks you can run ON your MySQL 5.* installation to see if you can upgrade ( these are just examples):

mysqlcheck -u root -p --all-databases --check-upgrade

mysql_upgrade --user=root -p --host=localhost —force

If you have the mysql shell:

mysqlsh -- util checkForServerUpgrade root@localhost:3306 --target-version=8.0.16  --config-path=/etc/my.cnf 

Those commands may fix, upgrade or repair problems with your database.

On your 5.* installation, I recommend you convert your MyISAM tables to InnoDB and use the new default character set utf8mb4 which has wider support for more character sets.

ALTER TABLE table_name ENGINE=InnoDB;

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

Use mysqldump to save your schema.
Install MySQL 8 separately. (don't upgrade your 5.* installation to 8.)
Import your mysqldump file into your mysql 8 installation under the new database you created.

Create user accounts:
The user accounts must be created using the old authentication plugin - it's the only way PHP can connect. Also, you must be using PHP 7.2.4 or higher.

CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_password';

Grant your user access to the appropriate schema. Something like:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost';

NOTE:
PHP's mysql native driver ( mysqlnd ) has not been updated to use the new default authentication plugin (caching_sha2_password)
Tell mysql to use the old authentication plugin when creating user accounts by adding this to your /etc/my.cnf file:

default_authentication_plugin=mysql_native_password

But if you don't include the above directive in your my.cnf file, you can still issue the create user statement with the "IDENTIFIED WITH mysql_native_password" clause to use the old auth plugin!

---

Related Topics