database related logos

This time i got this PHP error after upgrading my MYSQL from version 5 to 8. Error says:

The server requested authentication method unknown to the client [caching_sha2_password]

This is because of the changing of default authentication password mechanism from previous MySQL to newest MySQL 8. The newest version uses an authentication plugin called caching_sha2_password meanwhile the older version is mysql using native password. So I guess it's a security thing, but dont worry the older version I believe is already secure enough, you just need to make your password really strong (for your production server).

So if you execute SQL query below

select User,plugin from mysql.user ;


You will see what the plugin is being used for the user password, something like

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | caching_sha2_password |
+------------------+-----------------------+


So the easiest way to solve this problem is by just alter user password plugin back to native, do this by changing your my.ini configuration under [mysqld] block, add or change:

[mysqld]
default-authentication-plugin=mysql_native_password


Then restart MySQL service. Now you have one more thing to do, to alter the existing user plugin with native_password. Just do this alter command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';
flush privileges;


You can change your root user with the desired Mysql password, I am just using the simplest 1234 as my password because its the most convenient just for local development, not production.

Now if you execute SQL query : select User,plugin from mysql.user; one more time, then it will show its plugin changed to mysql_native_password.