I recently ran mysql_secure_installation to secure the MySQL installation on a new server, and when I reached the second prompt, it asked me to enter a root password.
Upon entering a password, I was met with the following error:
... Failed! Error: 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.
It then prompted me to enter a password again, leading to the same error each time. To make matters worse, there’s no way to get out of this password-asking loop except to close your command line window.
The cause
The issue is that MySQL on Ubuntu (as of this writing) does not use password authentication for the root user. Instead it uses a unix_socket authentication plugin, which makes it so that if you’re acting on your server as its root user, you will be authenticated with the MySQL service as root.
Because of this, when the mysql_secure_installation attempts to set a password for the root user, the above error is triggered because it’s not expecting a password for root.
The solution
To get around this - we need to temporarily change the root authentication method to be password-based in order to complete the mysql_secure_installation script.
To do this, open the MySQL command prompt:
> sudo mysql
Then run the following command to change the root user’s authentication method to one that uses a password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
After making this change, exit the MySQL prompt:
mysql> exit;
You should now be able to run the mysql_secure_installation without issue.
Once the security script completes, lets get back to the MySQL prompt to revert our above changes. Use the following command to enter the prompt via a password:
> mysql -u root -p
Once at the MySQL prompt, run the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;