Resetting MySQL root password when current passwor

Question

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 1

For mysql 5.7.16 on Ubuntu 16.04 this worked:

• login without password:

  sudo mysqld_safe --skip-grant-tables --skip-networking &

• change password:

  SET PASSWORD FOR 'root'@'localhost' = 'mypass';

Found solution on MySQL 5.7 Reference Manual

Answer 2

In my case was happening same but I was making mistake in logging into mysql prompt. I was doing by mysql instead of mysql -u root -p

Answer 3

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

Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.`

Answer 4

You need to make sure you are running the command as root by using sudo command.

I found this instruction for resetting mysql password works well.

Following this procedure, you will disable access control on the MySQL server. All connexions will have a root access. It is a good thing to unplug your server from the network or at least disable remote access.

To reset your mysqld password just follow these instructions :

Stop the mysql demon process using this command :

       sudo /etc/init.d/mysql stop

Start the mysqld demon process using the --skip-grant-tables option with this command

       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Because you are not checking user privs at this point, it's safest to disable networking. In Dapper, /usr/bin/mysqld... did not work. However, mysqld --skip-grant-tables did.

start the mysql client process using this command

       mysql -u root

from the mysql prompt execute this command to be able to change any password

       FLUSH PRIVILEGES;

Then reset/update your password

       SET PASSWORD FOR root@'localhost' = PASSWORD('password');

If you have a mysql root account that can connect from everywhere, you should also do:

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

Alternate Method:

       USE mysql
       UPDATE user SET Password = PASSWORD('newpwd')
       WHERE Host = 'localhost' AND User = 'root';

And if you have a root account that can access from everywhere:

       USE mysql
       UPDATE user SET Password = PASSWORD('newpwd')
       WHERE Host = '%' AND User = 'root';

For either method, once have received a message indicating a successful query (one or more rows affected), flush privileges:

FLUSH PRIVILEGES;

Then stop the mysqld process and relaunch it with the classical way:

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql start

Answer 5

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

Answer 6

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.