Reset Root Password MySQL on Ubuntu

Question

I know there are tons of posts about this on here but nothing seems to work. I have tried every command I could find it will not work.

I just recently setup mysql on Ubuntu (SQL version is 8.0.23-0ubuntu0.20.04.1). Afterwards i installed myphpadmin and I can get to it, but I noticed that it said that root doesn't have a password, even though I set one in the mysql_secure_installation. I don't really want to go any farther until i get a password on root, as eventually i'm going to have this server exposed to the outside, but every single command I have tried seems to do nothing. I am testing if it is working by entering "sudo mysql -u root" with no password and i'm assuming if there is a root password set then it will prompt for one instead of just going straight into the mysql> prompt.

These are the commands that i've tried:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword');
RESULT: 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 '('newpassword')' at line 1

mysql> SET PASSWORD = PASSWORD('newpassword');
RESULT: 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 'PASSWORD('newpassword')' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
RESULT: Query OK, 0 rows affect (0.01 sec)

Seeing this made me think its working, so i ran FLUSH PRIVILEGES; and refreshed myphpadmin but it still shows that it doesn't have password, so i kept going.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
RESULT: 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 'PASSWORD('newpassword')' at line 1

shell> sudo mysqladmin -u root password
New password: newpassword
Confirm new password: newpassword
RESULT: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This one seems like the right path maybe? But it says i need SSL...which is weird because i'm on the computer in a terminal, i don't know how to do an ssl connection to mysqladmin on the same computer using the terminal.

shell> sudo mysql_secure_installation
RESULT: Success.  All done!

This acts like it works, but it does nothing

Change from myphpadmin: I tried changing it from myphpadmin as well, it goes through with no issues, but the password still seems to be the same.

mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
RESULT: 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 '('newpassword')' at line 1

There was also mention of getting the first root login from /var/log/mysqld.log, but that doesn't exist on my computer

There are probably a few other odd things here and there i've tried but nothing has worked, does anyone have any insight into this?

Answer 1

I managed to figure it out. I started digging more into the user table and found the root user entry, and then found the authentication_string column. I had already followed the guides that someone commented and it didn't work, not sure why, so I tried the following.

mysql> USE mysql
mysql> UPDATE user
    -> SET authentication_string = 'newpassword'
    -> WHERE User = 'root';

RESULT: Query OK, 1 row affected (0.01 sec) Rows matched:1 Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;

I then checked on phpmyadmin and it shows that it has a password on it now. I think it should be good now...unless there is another check i need to run to be sure?