How reset MySQL password?

IconsPage/IconWarning3.png 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.

  • 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

Note: This method is not regarded as the securest way of resetting the password. However it works.

References

MySQL 5.0 Reference Manual: How to Reset the Root Password

https://help.ubuntu.com/community/MysqlPasswordReset

http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/

Advertisement