MySql: Access denied for user 'root'@'localhost'

By default, MySql has no root password when installed, hence the following should work in most cases. But in some cases, mysql and mysqladmin simply throw Access denied message and you have no clue how to change the password after that.

$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Here is an elegant solution for this highly irritating but common problem.

This is how you reset the password.

1. Start the mysql server instance or daemon with the --skip-grant-tables option. (security setting)

$ mysqld --skip-grant-tables

2. Then Execute these statements.

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';

3. Finally, restart the instance/daemon without the --skip-grant-tables option.

$ /etc/init.d/mysql restart

4. You should be able to connect with your new password.

$ mysql -u root -p
Enter password: my_password

courtesy: Shawn Tucker @ MySql Forums