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

I removed the MySQL installation and deleted the data files, and then reinstalled it.

Then I was able to set the root password. Once you set the root password to something. mysqladmin won't let you reset it if you don't know it.

To reset it, you've got to have ownership over how mysqld is executed, and feed it an init file to change the root password: https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Answer 2

Probably a bit late here , but here is what I did :

create file resetpass.sh which has :

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

# mysqld_safe --init-file=resetpass.sh

# service mysqld start --skip-grant-tables

# mysql -u root -p

Enter pass

mysql >  change root pass ; flush privs; 

quit

# restart mysql service 

The MySQL version I was using was 5.1.73 under centos6

Answer 3

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.