96

I'm trying to install MySQL on Ubuntu 20.04 and when I run the command sudo mysql_secure_installation, it fails with the error below:

... 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.`

I have seen the documentation and I'm stuck on how I can solve this.

Any help will be appreciated.

War10ck
  • 12,387
  • 7
  • 41
  • 54
briantuju
  • 1,109
  • 1
  • 8
  • 12
  • 1
    Does [this](https://stackoverflow.com/questions/47099351/mysql-5-7-20-unable-to-set-root-password#answer-47099742) provide any insight? See if changing the authentication plugin helps... – War10ck May 03 '22 at 17:37
  • [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html) – Luuk May 03 '22 at 17:46
  • Maybe [this](https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost) would help – Ergest Basha May 03 '22 at 18:20
  • Does this answer your question? [MySQL 5.7.20 unable to set root password](https://stackoverflow.com/questions/47099351/mysql-5-7-20-unable-to-set-root-password) – Progman May 04 '22 at 19:09
  • Sorry everyone for the late reply. The answer provided did help me resolve the issue. Once again, thanks for all your help. @War10ck – briantuju May 15 '22 at 11:42
  • Open the terminal application. 1. Terminate the mysql_secure_installation from another terminal using the killall command: sudo killall -9 mysql_secure_installation 2. Start the mysql client: sudo MySQL 3. Run the following SQL query: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SetRootPasswordHere'; exit 4. Then run the following command to secure it: sudo mysql_secure_installation 5. When promoted for the password enter the SetRootPasswordHere (or whatever you set when you ran the above SQL query) That is all. – Elson Costa Dec 28 '22 at 16:53

1 Answers1

202

Had the same error, solved it by running sudo mysql which logged me in as root without a password, then I ran ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

Following this I was able to run mysql_secure_installation again and this time I got to choose to use existing password (the one I set with above SQL command).

However now I can no longer login without a password, running sudo mysql now denies root user access.

Tigz
  • 2,163
  • 1
  • 10
  • 8
  • 1
    Yes, because the login password is `mynewpassword` (and the authentication system is changed to `mysql_native_password`) – Progman May 04 '22 at 19:08
  • So basically I'm in the same situation as @Tigz and I tried `mysql -U root -p` Then I gave the root password that I previously set but it did not log me in as root bu a local user and denied my access due to wrong password – Soumalya Bhattacharya May 04 '22 at 21:00
  • 9
    On this issue I found this tutorial helpful https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04 – Trunk Sep 16 '22 at 12:15
  • ^ I did as well, but had to restart mysql before it worked for me. – ow3n Sep 27 '22 at 20:44
  • 1
    recommended to change back to auth_socket once installation is finish `ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;` – Tony Sawlwin Jan 23 '23 at 11:28