How to reset a lost MySQL root password

Have you ever forgotten your MySQL root password? It’s one of those things that just happens despite the numerous precautions one might take. As a result, you are locked out of your database server. You can’t create new databases and are left with little control over the state of your database server. In such situations knowing how to regain root access to your database server comes in handy. So here’s what you can do to reset the password for the root user in MySQL on both Windows and Linux.

Windows Users:

Log on to your server as the Administrator. Kill the MySQL server if it’s running. To do this you need the Windows Services Manager, so click on the Start Menu, then go to the Control Panel, then to the Administrative Tools, and select Services. Here look for the MySQL server and stop it. If it’s not listed there and MySQL is till running it means that MySQL is not running as a service. In that case you need to load the Task Manager which you should be able to access using the key combination of Ctrl+Alt+Del. Now kill the MySQL process.

With the MySQL process stopped you need to force a change of passwords on MySQL using a combination of the UPDATE and FLUSH options. So launch your favorite text editor and create a new file. Enter the following text into the file replacing “NewMySQLPassword” with your new password:

UPDATE mysql.user SET Password=PASSWORD(“NewMySQLPassword”) WHERE User=’root’;
FLUSH PRIVILEGES;

What the first line does is that it updates the value of the field “Password” in the table mysql.user for the user “root” to “NewMySQLPassword”. The second line flushes the old set of privileges and makes sure your new password is used everywhere. Save this text as C:\mysql_reset.txt.

Next, you need to start your MySQL server passing this file as a configuration parameter. Launch a terminal by going to the Start Menu, then to Run, and then type cmd and hit Enter. Now enter the following command:

C:\mysql\bin\mysqld-nt --init-file=C:\mysql_reset.txt

Once the server is done starting delete the file C:\mysql_reset.txt. Your MySQL root password should be reset now. Now restart your MySQL server again. Go back to the Windows Services Manager again to do that. Your new MySQL root password should work for you now.

Linux Users:

Log on to your Linux machine as the root user. The steps involved in resetting the MySQL root password are to stop the MySQL server, restart it without the permissions active so you can log into MySQL as root without a password, set a new password, and then restart it normally. Here’s how you do it. First, stop the MySQL server:

# /etc/init.d/mysql stop

Now start the MySQL server using the --skip-grant-tables option, which will run the server without loading the permissions settings:

# mysqld_safe --skip-grant-tables &

The & option at the end makes the command you have executed run as a background process. Now log on to your MySQL server as root:

# mysql -u root

It should allow you in without prompting for a password. The following steps will set the new password:

mysql> use mysql;
mysql> update user set password=PASSWORD(“NewMySQLPassword”) where User=’root’;
mysql> flush privileges;
mysql> quit

Replace “NewMySQLPassword” with your own password. Here’s what happens here. The first line selects the MySQL configuration tables. The second line updates the value of the field “Password” for the user “root” to “NewMySQLPassword”. The third line flushes the old set of privileges and makes sure your new password is used everywhere. Now, the last step is to restart the server normally and use your new root password to log in:

# /etc/init.d/mysql stop
# /etc/init.d/mysql start
# mysql -u root -pNewMySQLPassword

Congratulations, your new MySQL root password is set and your MySQL server is ready to be used again. Remember to update all your applications to use this password if you are using it anywhere.