What we need to do is to stop the mysql server and then start it again without reading the user table. To be on the safe side, we won’t allow any network connections while we are doing this operation.
All commands are made as root, if you don’t feel comfortable with this, apply sudo where appropriate.
I am also assuming that you are running Debian 🙂
Stop Mysql Server:
|
1 2 3 |
$ /etc/init.d/mysql stop |
To start the mysql daemon in “safe mode” (read the link for more information) without reading the user table into memory, making it possible to login without password we can issue this command:
|
1 2 3 |
$ mysqld_safe --skip-grant-tables |
Running above command is a security issue since it will allow remote connections and practically anyone can do what they want with your mySQL server.
So, how do we solve this? By adding another parameter to the startup command:
|
1 2 3 |
$ mysqld_safe --skip-grant-tables --skip-networking |
Now the mySQL deamon will only accept local connections.
Ok, time to connect and change the password. Use the mysql client without giving any password
|
1 2 3 |
$ mysql -u root -p |
When connected, setup the new password for the root user:
|
1 2 3 4 5 6 |
mysql> use mysql; mysql> update user set password=PASSWORD("NEWPASSWORD") where user="root"; mysql> flush privileges; mysql> Ctrl+D |
Ok, a new password is set and it’s time to shutdown the mySQL and start it again in “normal” mode.
|
1 2 3 |
$ /etc/init.d/mysql stop |
Now start the mysql server, so that it could read the password information and allow remote connections again (according to your configuration)
|
1 2 3 |
$ /etc/init.d/mysql start |
All done!
3 comments for “How To Recover/Reset a forgotten MySQL root password”