Cloud VPS [Ubuntu]: How to Reset your MySQL Password

This article will explain the process of how to reset the password to a MySQL database that is on running on a VPS using Ubuntu as its operating system. By following this procedure, you will disable access control on the MySQL server. All connexions will have a root access.

To reset your mysqld password, follow these instructions:

1. Stop the mysql demon process using this command:


sudo /etc/init.d/mysql stop


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


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


3. Start the mysql client process using this command:


mysql -u root


4. From the mysql prompt, execute this command to be able to change any password:


FLUSH PRIVILEGES;


5. Then, reset/update your password:


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


6. If you have a mysql root account that can connect from everywhere, you need to also add:


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, you need to also add:

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


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

FLUSH PRIVILEGES;


8. Then stop the mysqld process and relaunch it with:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start


When you have completed all these steps, you should be able to easily access your mysql server with the password you have above.

 

Add Feedback