Fix error SET PASSWORD has no significance for user root@localhost

Sometimes during MySQL installation the user does not set the root password, then the unix_socket method is used as authorization. If this authorization is used, only a superuser of the system or a user with sudo privileges will be able to log in as the root user.

While using mysql_secure_installation an attempt to change the password will be made, in this case you will get an error from mysql SET PASSWORD has no significance for user'root'@'localhost'. We will tell you how to solve and eliminate it further.

Eliminating the error

The full name of the error looks as follows:

SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

The error is translated as:

"It makes no sense to execute the SET PASSWORD method for'root'@'localhost' because the selected authorization method does not support saving authorization data. If you want to change the authorization parameters use ALTER USER."

The unix_socket authorization method does not save passwords, it does not perform authorization by means of a password, it checks if a particular user meets the superuser conditions. And there is no way for a user to simply skip setting the root password (if it has not been set). This is why the program does not allow you to proceed. You can fix the problem by connecting to MySQL.

You also need to change the authorization method and the password at the same time:

sudo mysql

In the SQL environment, you need to execute the following command:

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

Close the MySQL client with the command:

exit;

After you need to run mysql_secure_installation, enter the root password. Then you can finish the configuration without any problems. Please note that after that you can connect to mysql using sudo without password, because the password authorization method is activated. To return to the previous settings after mysql_secure_installation, connect to MySQL.

Use the password and execute:

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

That's it - the instruction is complete.