1

I have set a root password, but cannot authenticate with it. How can I reset this password?

~ mysql --version: mysql  Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

I used these stackoverflow instructions to set a root password and queried the user table to verify the password.

I then tried mysql -u root -p and mysql -u root and got authentication errors.

Command: mysql -u root -p Authentication error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES). I am SURE I am using the correct password.

Command:mysql -u root Authentication error: Error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).

That led me to this this stackoverflow question and attempted @Lahiru's solution, but got No such file or directory.

I found the Mysql documentation and performed the following: (as outlined in B.6.3.2.3 Resetting Root Password: Generic Instructions)

mysql.server start --skip-grant-tables

Mysql -u root

FLUSH PRIVILEGES

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Then I got an error: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Lastly, using the Mysql 8.0 documentation: 13.7.1.10 SET PASSWORD Syntax I tried:

mysql.server start --skip-grant-tables

SET PASSWORD FOR 'root'@'localhost' = '1111';

and get error: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

I can't try to do this while running the server out of safe mode because it doesn't accept the password I set.

Thanks in advance for any help.

awoodrum87
  • 33
  • 1
  • 6
  • 1
    Started with --skip-grant-tables, update the mysql.user table with DML. For MySQL 5.6, we would do: `UPDATE mysql.user SET password = PASSWORD('mysupersecret') WHERE user = 'root' AND host = 'localhost' ; ` Then restart MySQL server without --skip-grant-tables. – spencer7593 Jan 18 '19 at 21:44
  • Thanks @spencer7593 I've tried this multiple times with no luck. Also, in this version of Mysql there is no `password` field. It's `authentication_string` – awoodrum87 Jan 18 '19 at 21:49
  • "with no luck" is practically useless in describing the observed behavior. With MySQL 5.7 and later `UPDATE mysql.user SET authentication_string = PASSWORD('secretpassword') WHERE user = 'root' AND host = 'localhost' ;` Does that row exist in mysql.user table? `SELECT user, host FROM mysql.user WHERE user = 'root' AND host = 'localhost'`. – spencer7593 Jan 18 '19 at 21:52
  • If i run your first command I get a syntax error. If I remove the `PASSWORD()` method, the user record updates and i query the user record and it verifies the new password in the `authentication_string` field. the i quit sql. stop the the sql server. start again, but not in safe mode. run `mysql -u root -p`, enter the new password and get an authentication error. – awoodrum87 Jan 18 '19 at 22:02

0 Answers0