Access denied for user 'root'@'localhost' (using password: Yes) after password reset LINUX

Question

I have a MySQL installed on my linux server, I forgot it's password so I went and changed it using the methods I found on the web. What I did was as follows:

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql --user root mysql
SELECT * FROM user; // I checked if I could access the user table or not
update user SET password = PASSWORD('new_pass') WHERE user = 'root';
flush privileges;
exit

The update query did change the password as it showed me the number of rows affected and Query OK etc.

Then I restarted mysql

/etc/init.d/mysql stop
/etc/init.d/mysql start

Now when I logged in with the new password

mysql -u root -p new_pass

it still gives me errors "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Yes)"

Is there something that I am missing?

Answer 1

You have to reset the password! steps for mac osx(tested and working) and ubuntu

Stop MySQL

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

$ sudo mysqld_safe --skip-grant-tables

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Start MySQL

sudo /usr/local/mysql/support-files/mysql.server start

your new password is 'password'.