How to Fix: “Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost'”

Last updated on | one reply

Introduction

As of May 2022, running the mysql_secure_installation script without further configuration will result in an error. This is because the script attempts to set a password for the root MySQL account, but on newer installations of MySQL, this root account is not configured to use a password by default.

The mysql_secure_installation script will return the following error after you enter and confirm a password:

“… 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.” 

You may also bee stuck at the “New password:” prompt, unable to exit by pressing Ctrl + C. In this case, you will need to close down your terminal window manually and log back in.

Change MySQL authentication method and root password manually

Log into mysql.

sudo mysql

Run the following ALTER USER command to change the root user’s authentication method to mysql_native_password. Generate a strong password here.

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

After making this change, exit the MySQL prompt:

exit

Now you can run mysql_secure_installation again and the error should be gone.

sudo mysql_secure_installation

Enter your MySQL root password:

Enter password for user root:

Follow the steps of the mysql_secure_installation as normal.

Once the security script completes, you can then reopen MySQL and change the root user’s authentication method back to the default, auth_socket.

mysql -u root -p

Set authentication method back to auth_socket.

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

Now you will be able to connect to MySQL as your root user using the sudo mysql command.

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

p.s. I increased my AdSense revenue by 200% using AI 🤖. Read my Ezoic review to find out how.

1 reply

Leave a reply

Your email address will not be published. Required fields are marked *