SQL cheat sheet

 How to Change the Authentication Method in MySQL? [Ubuntu-18.04]

Issue

I have an instance of mysql installed in my system. Say my username is test and the password is Test1234! Now after the installation I should be able to access my mysql server with the command mysql -u test -p isn’t it? But it throws the error related to Access denied to user ‘test’@’localhost’ But my password is correct.

Then in my terminal if I will only type sudo mysql without entering any user password, I will be logged in successfully.

So what is the issue then? Why am I not able to login via my mysql username and password?

Solution

This issue exists because of the Authentication Method in MySQL. By default during the installation, the authentication method for our user is set to the auth_socket method/plugin. You can observe the configuration by checking your mysql instance via sudo login.

So type sudo mysql  in terminal. This will let you access the mysql server. After logging in, type the following command:

SELECT user, authentication_string, plugin, host FROM mysql.user;

Result is somewhat like this:

word-image-131

Did you see what is causing the issue with mysql login as I explained above in the issue section? Now chnge the auth_socket method with mysql_native_password and you are set to login with your mysql user.

To alter the authentication method type the following in your mysql server console:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

We are now almost done but the changes wont be reflectd unless you flush the privilages or say reload the grant table.

So in order to reload the grant tables type the following:

mysql> FLUSH PRIVILEGES;

Now the privilege is flushed and after you login to the console you will get to login with your mysql username and password.