Getting error: “Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost”? Then read the steps here to fix it up.

When you run the mysql_secure_installation command in your terminal window, it will ask you to enter the root password and set up security options for your MySQL installation.

But when you try to set a password for the root account, you get the following error message on the screen: 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.

This error usually happens when you try to set a password for the root account. This is because the default mechanism for the mysql root account is “auth_socket,” which uses the system user information rather than a password.

Here is a short piece of information from the MySQL documentation on how auth_sockets works.

The auth_socket plugin checks whether the socket user name matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin also checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user table row. If a match is found, the plugin permits the connection.

To fix this, you can change the authentication method for the root account to use a password instead of auth_socket by replacing auth_socket with mysql_native_password or caching_sha2_password .

Steps to Fix: Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost

The steps are pretty simple; you just need to run a couple of commands, and the issue will be resolved for sure.

Start MySQL

I believe you may be stuck with the window where it’s asking for a password. If so, then close that terminal and open a new one where you can run the commands again.

Once you’ve opened a new terminal, type the following command to start MySQL with sudo privileges:

$ sudo mysql

Change Authenication

Next, you need to use the ALTER USER command to change the authentication parameter from auth_socket to mysql_native_password or caching_sha2_password by running the below command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SET_PASSWORD';
#OR
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'SET_PASSWORD';

The behaviour of the command is shown below:

Once you get the “Query OK” message, please close the mysql .

mysql> exit

After making the following changes, you can now use the mysql_secure_installation script for the mysql command line client to improve the security of your MySQL installation.

$ sudo mysql_secure_installation

Now, whenever you want to access MySQL on your system, you must use the mysql -u root -p command, and if you ever forget the root password of MySQL, then read this guide.

Revert Changes

Once you’re done with script usage, you can revert the setting to default so MySQL won’t ask you for the password when you try to login from the root account or with sudo privileges.

First get login to mysql

$ mysql -u root -p

After getting the mysql prompt, copy and paste the below command into it, and once you get the “OK” message, it means mysql will use ‘auth_sockets’ for authentication from now on.

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

Wrap up

That’s all for this short troubleshooting guide, where you learn how to fix a failed error: SET PASSWORD has no significance for user ‘root’@’localhost'”.

In a nutshell, you need to run one command in your system shell like bash, zsh, fish, etc. and another into the MySQL console:

• $ sudo mysql
• mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SET_PASSWORD';

If you are facing any difficulty while making changes, then feel free to comment here.