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.
auth_socketplugin 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_stringcolumn of the
mysql.usertable 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.
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
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 .
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.
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;
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.
A man with a tech effusive who has explored some of the amazing technology stuff and is exploring more. While moving towards, I had a chance to work on Android development, Linux, AWS, and DevOps with several open-source tools.