Home » How-to, Linux, Mysql, Newbie, Recovery

MySQL root password recovery

27 February 2009 No Comment

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!


Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.