“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” while trying to run the ‘mysql_secure_installation‘ command, This error can be resolved by using an alternative command, ALTER USER, to change the authentication for the ‘root’ user in MySQL. In this post, we will talk about how to fix this error and properly secure your MySQL installation.

The ‘mysql_secure_installation‘ command is a commonly used script to enhance the security of the MySQL installation. However, if you encounter the error message mentioned above, the ‘mysql_secure_installation‘ script will not be able to complete successfully until this issue is resolved.

So, let’s dive into the step-by-step instructions on how to fix this error

Table of Contents:

• Step 1: Connect to MySQL Server
• Step 2: Identify the Authentication Method
• Step 3: Use ALTER USER Command
• Step 4: Flush Privileges
• Step 5: Exit MySQL
• Step 6: Retry ‘mysql_secure_installation’
• Conclusion

Step 1: Connect to MySQL Server

First, you need to connect to your MySQL server:

sudo mysql

Step 2: Identify the Authentication Method

Next, you need to identify the authentication method that is being used for the ‘root’ user. To do this, you can run the following SQL query:

SELECT user, plugin FROM mysql.user WHERE user = 'root';

This will display the ‘root’ user and the plugin associated with it in the result set. The plugin column will indicate the authentication method being used, which could be ‘mysql_native_password’, ‘caching_sha2_password’, or any other method.

Step 3: Use ALTER USER Command

Once you have identified the authentication method, you can use the ALTER USER command to modify the authentication. Here’s an example of how to change the password for the ‘root’ user using the ALTER USER command for the ‘mysql_native_password’ plugin:

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

Replace ‘new_password’ with the password you want to set for the ‘root’ user.

Step 4: Flush Privileges

After you have executed the ALTER USER command, you need to flush the privileges to apply the changes:

FLUSH PRIVILEGES;

Step 5: Exit MySQL

Once the privileges are flushed, you can exit the MySQL:

EXIT;

Step 6: Retry ‘mysql_secure_installation’

Now, you can retry running the ‘mysql_secure_installation’ command, and it should complete without encountering the previous error. You will be able to set the password for the ‘root’ user and configure other security settings as needed.

Conclusion

It’s important to note that the error message indicates that the authentication method being used for the ‘root’ user does not store authentication data in the MySQL server.