MySQL root password setup error

Question

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

Solution 1

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

1 - I killed the mysql_secure_installation process

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

Solution 2

try that

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

Comments

[Admin]

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

[Admin]

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

[Admin]

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.

[Admin]

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?

[Admin]

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