It happens from time to time to forget some passwords if I’m not using them very very often. Recently I forgot the root password on a MySQL database from an external client. Recovering the password was not such a big deal, but I will explain how I do that for the newbies or anybody who need to know that. To do that you will need to stop your MySQL Database so if you are in a production environment then it will be better to schedule a downtime window. The process will not get more than 5 minutes and is easy, just follow the next steps:

Login as root and:

1. Stop the database
In Debian is easy

[root@random-bugs]# /etc/init.d/mysql stop

(probably) under any RPM (Centos, Redhat, Suse) distribution

[root@random-bugs]# service mysql stop

2. Start MySQL manually
Now start the mysql with –skip-grant-tables

[root@random-bugs]# mysqld_safe –skip-grant-tables &

3. Login to mysql
Just run:

[root@random-bugs]# mysql -u root

4. Set the root password

mysql> use mysql;
mysql> update user set password=password(“your new password”) where User=’root’;
mysql> flush privileges;

5. Shutdown the mysql server

[root@random-bugs]#mysqladmin shutdown

6. Restart your mysql server

Debian

[root@random-bugs]# /etc/init.d/mysql start

Centos/Redhat/Suse

[root@random-bugs]# service mysql start

Now you can test your new password.

Good luck!