How to change the mysql root password

Question

I have installed MySQL server 5 on redhat linux. I can't login as root so I can't change the root password.

mysql -u root -p  
Enter password:  <blank>
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
(using password: NO)

When I try to set one like this:

mysqladmin -u root password 'newpass'

I get an error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' 
(using password: NO)'

As if there is a root password set.

I have also tried resetting the password using (described here)

/sbin/service mysqld start --skip-grant-tables

And then making:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')     
->  WHERE User='root';  
ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'

I even uninstalled mysql-server (using yum) and then reinstalled it but that did not help.

How do I force reset the root password?

Answer 1

One option is to save UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root'; into a file and then manually start mysqld with --init-file=FILENAME. Once the server starts, it should reset your password, and then you should be able to log in. After this, you should shut down the server and start it normally.

Answer 2

A little late to the game, but I had the same issue on a raspberry pi install and found out that it needs elevation. Adding a sudo to the front of the password change allowed it to work.

sudo mysqladmin -u root password 'newpass'

followed by an elevated sql access

sudo mysql -u root -p  

If either are not run as sudo, it will fail.