MySQL Access denied for user 'root'@'localhost' ubuntu 20.04 and MYSQL8

I keep getting this error. I have been looking at a few QA sites trying to reset my local mysql pw. I have added the skip grant tables under [mysqld] and now I am running in safe mode. I have followed these steps

mysqld_safe --skip-grant-tables &
use mysql;
update user set password=PASSWORD(”my_password”) where user=’root’;

When I get to the last step on the update I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("my_password") where user="root"' at line 1

I also tried

 UPDATE mysql.user SET authentication_string=PASSWORD(''), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

and this

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

But get the same error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Ihost IDENTIFIED WITH mysql_native_password BY 'root'' at line 1

Not sure how to reset my password at this point.

1 Answer

I am updating what works for me everytime, follow these steps

1. Login as root user

   sudo mysql -u root

2. select mysql db

   use mysql;

3. Check plugin used for desired user, if it is auth_login? change this to mysql_native_password, using this query

   UPDATE user SET plugin='mysql_native_password' WHERE User='root';

4. Now use this query to update root or any other user's password

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

5. Restart mysql service using

   sudo systemctl restart mysql

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy