0

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?

3
  • 1
    If you use SUDO then MySQL assumes you to be the Linux ROOT account and therefore lets you do anything
    – RiggsFolly
    Apr 14, 2021 at 21:50
  • The mysql root account is normally only allowed to connect to MySQL from the machine running MySQL, it cannot be used remotely
    – RiggsFolly
    Apr 14, 2021 at 21:51
  • If you look at the manual there is only one way to do it right. Trust the up to date docs rather than the incomplete ramblings of" "solutions" that may have worked in the past.
    – danblack
    Apr 14, 2021 at 22:03

1 Answer 1

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

3
  • If newpassword wasn't a hash of the same sort as the authentication plugin expects then no, this is wrong. There's a reason ALTER USER and SET PASSWORD syntax has been implemented for the last 10 years, so people can stop fiddling with MySQL tables they don't fully understand.
    – danblack
    Apr 15, 2021 at 0:15
  • Could you provide some insight then as to why alter user and set password do not work? set password gives a syntax error and alter user just says it was a successful query that didn't alter anything. If what i did was the improper method then i will continue searching, but any assistance would be appreciated.
    – mrlizard
    Apr 15, 2021 at 13:27
  • I'm recommending using alter user / set password. You have than error there. I can't guess it. I referenced the manual in the comments on the question.
    – danblack
    Apr 15, 2021 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.