When you are using the master production server and there appears a need to recover the root password in MySQL, you cannot restart at once.

The remote database experts suggest a few ways by which you can come out of this situation.

This is a guest post by Charlie Brown, one of the well-known remote database experts. In this article, he is sharing some simple methods to recover the root password without restarting MySQL.

Recover MySQL Root Password without Restarting

• A smaller version of mysqld can be launched without using the InnoDB
• The user files can be copied from the original datadir file to the new smaller file created. For example, the user files can be like : [frm/MYD/MYI]
• After the modification, the files can be set back to their original position

So, let us go through the process of recovering the root password:

During Fresh Installation:  

When a fresh installation takes place in MySQL, there is no previous default password present. Therefore, you can use the command for updating the new password:

Mysqladmin –u root password <YOUR PASSWORD> (where <YOUR_PASSWORD> is your planned one)

The password will automatically get copied in the history logs. Therefore you are required to activate history –c to clear your history in whole and then enter the password in plain text.

You can utilize the MySQL client for installing the fresh password:

• Activate mysql mysql –uroot for logging in the local server of the MySQL and modifications can be done in the MySQL database
• Go through UPDATE user SET password=PASSWORD ( “< YOUR_PASSWORD”>) WHERE User = root  as soon as the MySQL client signals for the change in the root password
• Enter FLUSH PRIVELEGES for enabling the use of your new password
• Type \q for getting out of MySQL client
• Enter mysql –uroot  -p and then activate your new password for ensuring that the change has taken effect

During The Locked Out Situation: 

If you get locked up in the database of MySQL, you can reset your root password for easy access. You can use the command line below and get out of the situation:

• You need to log in as the root user or can switch the previous user accounts
• Activate /etc/init.d/mysql stop for shutting down MySQL ( the server needs to shut down completely before the next step)
• You can enter mysqld_safe –skip grant-tables –skip networking & for restarting MySQL without any network access or authentication formalities
• Mysql mysql –uroot can now be entered for logging into the MySQL server for applying changes in the MySQL database
• After that , enter UPDATE USER SET password=PASSWORD ( “<YOUR_PASSWORD”>) WHERE User = root  as soon as the MySQL client signals for the change in the root password
• Enter FLUSH PRIVELEGES for enabling the use of your new password
• Type \q for getting out of MySQL client
• Enter mysql –uroot  -p and then activate your new password for ensuring that the change has taken effect

Both in the case of fresh installation and in the locked-out situation, the steps are more or less similar, but they are different in the initial steps.

Reset Script Of Root Password:

#!/bin/bash
#
#MySQL Root Password Reset Script
/etc/init.d/mysql stop
Mysqld safe –skip grant tables—skip –networking &
Mysqlasmin –u root password “ “
etc/init.d/mysql restart
mysql mysql -uroot

Therefore the downtime can easily be avoided if there is a need of recovering the root password. We can easily recover the password without restarting MySQL. In case you face a similar situation of recovering the password, just do not worry! Apply all the steps for fast recovery and be aware of the modifications. If you know some other simpler methods to recover the password ignoring the downtime, do not forget sharing them with us!