0

I cannot login to mysql since I forgot (I lost!) the temporary password I received after installing mysql. So I tried the following steps but they didn't help me:

  1. I stopped mysql server
  2. Then I created a file in /usr/local/mysql/support-files/,called restore, in which I wrote this line: SET PASSWORD FOR root@localhost=PASSWORD('');
  3. Finally I ran this command: sudo mysqld_safe --init-file=/usr/local/mysql/support-files/restore but it stuck on mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data. However I checked mysql from system preference and it already started (showing "mysql is running")
  4. I tried to login to mysql again with this command mysql -u root, but still I receive the same error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Could someone please help me fix this problem ?

mOna
  • 2,341
  • 9
  • 36
  • 60

1 Answers1

1

Try this

1. stop mysql server
2. sudo mysqld_safe --skip-grant-tables &
3. mysql -u root  //it should not ask password
4. use mysql; //change to mysql db
5. update user set password=PASSWORD("secure password") where user='root';
  or (depending on mysql version)
  update user set authentication_string=password('secure password') where user='root';
6. flush privileges;
7. logout from mysql (quit)

8. start mysql server (mysql -u root -p)
knightrider
  • 2,063
  • 1
  • 16
  • 29
  • Thanks for your answer! it worked.. but is realy strange for me.. since I did almost the same steps (the only difference was in step 2 that I used `sudo mysqld_safe --init-file=/usr/local/mysql/support-files/restore` instead of `sudo mysqld_safe --skip-grant-tables &` ). btw, thanks a lot :) – mOna Mar 14 '16 at 16:15
  • @mOna No problem. Please accept the answer as it worked for you – knightrider Mar 14 '16 at 16:15
  • yes, I tried, but I should still wait two more minutes to be able to accept :) btw, I get this error when I try to change password (i.e., in step 5): `ERROR 1054 (42S22): Unknown column 'password' in 'field list'` – mOna Mar 14 '16 at 16:17
  • I also tried with this command: `ALTER USER 'root'@'localhost' IDENTIFIED BY 'myNewPass'`, but I get this error: `ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement` – mOna Mar 14 '16 at 16:22
  • its strange that you get and error and still succeed in reseting the password. Try `select * from information_schema.columns where table_schema = 'mysql' and table_name = 'user'` and check if the password field is there. Also double check if you executed step 4 – knightrider Mar 14 '16 at 16:23
  • I couldn't reset the password! (I could do the step 1 to 4 without any trouble but from step 5 (reset password), I got the error). I tried the query you mentioned and it shows me the table ... – mOna Mar 14 '16 at 16:25
  • thanks to @nodejh in this post: http://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx, I found there is no password field in MySQL 5.7. Instead there is `authentication_string` field, so I had to use: `update user set authentication_string=password('1111') where user='root';` and it worked for me :) – mOna Mar 14 '16 at 16:58
  • @mOna Thanks for input. I have update the answer. May be it will help someone else – knightrider Mar 14 '16 at 17:52