Bad Programmer Bad Programmer - 1 month ago 16
MySQL Question

Resetting MySQL root password

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?

Answer

Two options:

If you want to use --skip-grant-tables you may need to start mysqld directly as the options aren't necessarily passed along through the init script.

Otherwise, try these instructions from the manual.