A.4.2 How to Reset Root Password in MySQL

If you have forgotten the root user password for MySQL, you can restore it with the following procedure:

1. Take down the mysqld server - kill `cat /mysql-data-directory/hostname.pid`

2. Restart mysld with the –skip-grant-tables option.

3. Connect to the mysqld server mysql -h hostname mysql

4. FLUSH PRIVILEGES to enable grant tables

5. Change the root password.

Details/Notes of Steps

1. Take down the mysqld server -

Do not kill -9 The server’s process file runs in .pid format, and is usually in the MySQL database directory, so:kill `cat /mysql-data-directory/hostname.pid`
This procedure requires root access to your Unix server.
2. Restart mysld with the –skip-grant-tables option.

3. Connect to the mysqld server with

mysql -h hostname mysql

and change the password with a GRANT command.
4. FLUSH PRIVILEGES to enable grant tables

5. Change the root password.

source: paraphrased from p. 723 of MySQL Reference Manual, printed in 2002 for MySQL v. 4

Key notes

The book/docs mention at the bottom of instructions that you will get an error until you execute FLUSH PRIVILEGES. But they did not include this as a step Before using the GRANT command.
Important: Use the cli mysql client to do this. I tried to use phpmyadmin and it kicked me out after flush privileges and prompted for login - a reasonable security feature of phpmyadmin. But what you want to do is to be able to get access to your mysql server, then enable privileges, then change privileges so you can get in later.
Hope these instructions save someone else the headache.

This entry was posted on Saturday, July 22nd, 2006 at 3:03 am and is filed under MySQL, WebTechnology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.