Learn the Ways to Recover MySQL Root Password without Restarting

Recover MySQL Root Password without Restarting
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 the few ways by which you can come out of this situation.

Click to read the article and to find out how to resolve this!

This is a guest post byCharlie 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 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 an 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 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 the MySQL .In case you face the similar situation of recovering the password, just do not worry! Apply all the steps for the 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!

[Total: 2    Average: 3.5/5]

4 thoughts on “Learn the Ways to Recover MySQL Root Password without Restarting

  1. Thank you very much for sharing and discussing on SQL. I like to read information information on SQL and Database as well. Nice to see an article on how to recover password. Your post is really helpful to everyone. I have found much more information on database on remote dba experts. Great article and good to read.

  2. I am one of the remote dba experts and love to share the post which are useful to me. I think your post will be one of the favorite list because it gives me the the description what I needed. Nice post. Keep sharing such articles in future.

  3. This is the most useful article for me as it is based on database and I am a database administrator. I think this is the best article to know about the ways to recover mysql root password without restarting. All the steps are explained very well and if you face any problem then you can take refer remote dba support for help. Overall the article is good. Thank you for posting such a great article. Keep it up.

Leave a Reply to Alexander Fernandise Cancel reply

Your email address will not be published.