How To Recover a forgotten MySQL root password tfdusr1 - Mon, 03/07/2016 - 03:29

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:

$ /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:

$ 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:

$ 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

$ mysql -u root -p

When connected, setup the new password for the root user:

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.

$ /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)

$ /etc/init.d/mysql start

All done!


This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.

Tags:

mysql root password recover