How to Reset MySQL root Password in Debian / Ubuntu VPS

Ever, since I started offering Linode and Slicehost unmanaged Linux VPS setup and maintenance service (iVPS.in). I have come across situation where I had to reset my clients MySQL root password. Have you forgotten / lost your MySQL root password?

MySQL password resetting steps are same for the entire Linux flavor like RedHat, CentOS, Fedora …etc only differs is mysql stop and start command.

First, stop the MySQL service.

sudo /etc/init.d/mysql stop

Now, you need to start MySQL service in safe mode this will skip the user privileges table.

sudo mysqld_safe --skip-grant-tables &

Type in following command; this will directly go to MySQL database.

mysql mysql –u root

Now in your MySQL prompt run:

update user set password=PASSWORD("yourpassword") where User='root';

Reload privileges:

flush privileges;

Log out from the MySQL prompt:

Quit

Finally, stop and start the MySQL service.

/etc/init.d/mysql stop

/etc/init.d/mysql start

Now you should be able to log in with new password:

mysql -u root –p