29

I am working on a project and I have been trying to use MySQL instead of SQLite. So I downloaded MySQL and I am trying to set the root password with mysql_secure_installation, but when I submit both passwords, it gives the following error:

 ... 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 been trying to set the password in mysql by doing:

$ sudo mysql -u root
mysql> SET PASSWORD FOR 'root' = PASSWORD('new_password');

and

update user set authentication_string=password("password") where user='root';

But then it gives syntax errors.

How can I solve this?

EDIT: One of the problems I have now is that I need the root password to connect to the MySQL server, leaving it empty didn't seem to work

3
  • 2
    For many years now there has not been the ability within mysql to set a root user password. mysql now uses your main root password, so one just runs sudo mysql May 3, 2022 at 16:56
  • I just had this happen to me and your question was the top search result after just 10 mins! The error was added only a few days ago in 8.0.29 dev.mysql.com/doc/mysql-errors/8.0/en/… so I think the install script just hasn't caught up. Trying to figure out an easy workaround now...
    – Paul Dixon
    May 3, 2022 at 16:59
  • I was installing the package from Ubuntu 20.04LTS, and it seems that killing that install script leaves the database accessible by root at least.
    – Paul Dixon
    May 3, 2022 at 17:03

4 Answers 4

55

I just found the same issue. So, these were my steps:

  1. I killed the mysql_secure_installation process, sudo pkill -f mysql_secure_installation.

  2. I logged into mysql using:

     sudo mysql
    

    of course, sudo asks my system root password. Once I provided the right root password, I'm connected on mysql as root mysql user.

  3. I use my mysql session to run ALTER USER:

     mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'my-secret-password';
    
  4. exit

     mysql> exit
     Bye
    
  5. Run sudo mysql_secure_installation command, and complete steps of securing Mysql.

PS.: It seems that neither /root/.mysql_history nor ~/.mysql_history contain my ALTER USER command. Anyway, it is good to check out there and remove/edit always you run queries with sensitive information.

3
  • 1
    After applying ALTER USER, I can no longer login using sudo mysql as it returns this error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Is there a way arounf this? May 6, 2022 at 19:42
  • 2
    @FareedFattal after ALTER USER you should to log in mysql using mysql -u root -p . This command will ask the password you filled using ALTER USER and once you type it correctly you are logged on the mysql server.
    – Duloren
    May 7, 2022 at 10:23
  • Its Working Fine For mysql Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) Sep 29, 2022 at 6:10
5

Try:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'yournewpassword';
1

Before starting the installation process "sudo mysql_secure_installation":

  1. login to mysql and set a root password.
  2. exit mysql and run the secure installation
  3. login into mysql with mysql -u root -p and the password you set in step 1 and set the following "ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;"

A detailed installation guide can be found here: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

1
  • 1
    I am following the guide on DigitalOcean. Step 3 here (auth_socket), allows me to use mysql as root without need to enter password. I still got the error in question, if I run mysql_secure_installation. Rebooting (after password change) fixed the issue for me. Oct 3, 2022 at 9:09
0

I just recently had to re-install MySQL on a headless Ubuntu server running Ubuntu 20.04, and the easiest way round where mysql_secure_installation didn't work when changing the password, I just used 'mysqladmin -u root password' (password is NOT the password to use, but it's the command to change the root password), then it'll ask for a new password, but for some reason the secure installation script messes up, so as long as MySQL root has a password, I'm not really worried about running the secure script, being that I'm not allowing port 3306 through the firewalls and only have local access.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .