Change the authentication method in MySQL
Surely, if you program in PHP or other languages that use the latest versions of MySQL, you have seen this error when trying to authenticate:
The server requested authentication method unknown to the client [caching_sha2_password]
This is due to the fact that in the latest versions of MySQL, the default authentication method is caching_sha2_password as opposed to the classic mysql_native_password. Some languages like PHP do not yet support this new authentication method, so users who are used to perform operations in MySQL from these languages must have the classic authentication method. Next, we will see how to change the authentication method for one or all users of the database.
Identification of authentication method or plugin
To identify which users are registered in the database and which authentication plugin each one uses, you must log in as root and execute the following command:
select User, plugin from mysql.user;You should get a result similar to the following:
+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+|
| some_user | caching_sha2_password ||
| mysql.infoschema | caching_sha2_password ||
| mysql.session | caching_sha2_password ||
| mysql.sys | caching_sha2_password ||
| root | caching_sha2_password ||
+------------------+-----------------------+|
As you can see, all users have the new authentication plugin.
Changing the plugin for all users
First of all, this procedure only works for new users, users already registered will continue to keep the authentication method with which they were created. The first thing you need to do is locate the my.cnf file and open it with your preferred text editor, in my case, vim.
GNU\Linux Debian-based distributions:
sudo vim /etc/mysql/my.cnfMacOS with homebrew:
sudo vim /usr/local/etc/my.cnfNext, you must add the following line at the end of the file:
default_authentication_plugin=mysql_native_passwordThe next step would be to restart the MySQL server, although I'm not sure if it is really necessary.
Changing the plugin for a single user
To change the plugin for a single user, you must log in as the root user and execute the following command where you must replace some_user with the user you want to change and mypass with the new password.
For MySQL < 8.0.11
UPDATE mysql.user SET authentication_string = PASSWORD('mypass') WHERE user = 'some_user';For MySQL >= 8.0.11
ALTER USER 'declarations_usr'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret_usr';If you want to leave the password blank so that the user enters and changes it, you must leave the authentication_string field as NULL.
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'some_user', authentication_string=NULL;Next, you must reload the privileges with
FLUSH PRIVILEGES;If you ran the script to leave the password blank, you must log in with the user in question and set the new password with the following command:
SET PASSWORD = 'mypass';Create a user with a defined plugin
If you want to create a user with a plugin different than the one configured by default, you must add the WITH clause to explicitly indicate the plugin.
CREATE USER 'declarations_usr'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secret_usr';That's it, see you next time!.