Mysql – Resetting MySQL root password when current password is not known


I have inherited a server that has mysql installed on it. I don't have the mysql password for any user, not even root (although I have the linux root password). Plus, I am only aware of one other user account besdies root, and that one does not have privileges to perform any action, not even SELECT.

I tried stopping the mysql servicw, restarting with the skip grant tables option, and just logging in without password:

service mysqld stop
service mysqld start --skip-grant-tables &
mysql -u root

But get the following error:

Access denied for user 'root'@'localhost' (using password: NO)

I then tried resetting the password:

mysqladmin -u root password 'newpw'

But that also gives an access denied error.

I also tried logging in as the other user (without pw) and executing the following command:

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

and got this error:

ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'

I have also tried removing mysql and reinstalling, but I get the same errors.

Any suggestions?

Best Solution

try sudo dpkg-reconfigure mysql-server-5.5 and you will be asked for the new root password.

Replace 5.5 with your current mysql-server version