How to reset a lost MySQL root password

by Sukrit Dhandhania on November 26, 2008

Linux Security Windows

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.

  • Marcos Santos

    OI como faço para recuperar a senha do root do linux Kurumin

  • xxxxxxx
  • http://m Manfred

    BTW the syntax above is missing a -
    There should be a double minus sign in front of the init. Do that and the commands work fine. Looking at it, its missing elsewhere too – so might be your blog s/w converting double en dash to em dash

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

  • http://www.simplehelp.net Ross McKillop

    Thanks Manfred – you’re right, it did convert the double-dashes into a single dash. It’s all fixed now. Thanks again!

  • http://www.yourwebskills.com DaiLaughing

    Much simpler than the “official” version pointed out by xxxxxxx. Nicely written, thanks.

  • http://yahoo.com amaresh

    Just perfectly described… Saved a lot of time for me…! Thanks

  • Luis

    I’m having some kind of problem…

    Having stopped the MySQL server a running the above script, nothing happens…
    The mysqld-nt.exe keeps running as a process and the server never starts.
    Should this be quick?

  • Slarti

    Hi thank you for posting, the syntax correct for the Unix version is this (attention on the quotes):

    update user set password=PASSWORD(”NewMySQLPassword”) where User=’root’;

  • Slarti

    I ment to say that:
    update user set password=PASSWORD(‘NewMySQLPassword’) where User=’root’;

  • Phil

    Thank you, solved my problem,

    Phil

  • Daniel

    La solución bajo windows funciona?

    Yo tengo el Mysql server 4.1 instalado y se me ha olvidado la clave de root. Quiero saber si esa solución funciona!

    Veo que la sintaxis esta mal?? Por lo que decis..

  • Daniel

    The solution under Windows works?

    I have the Mysql Server 4.1 installed and I forgot the root password. Let me know if this solution works!

    I see that the syntax is wrong? As you say

  • http://www.mwasif.com wasif

    @Daniel,
    I believe the same procedure can be applied to MySQL 4.1, you can also consult mysql manual (http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html) if you have any ambiguity.

  • jan

    Thanks! on linux it works! perfect solution.

  • dido

    Hi,

    Is there any windows tool that can simplfy with a CLICK all these steps to Reset the root password?

    Thanks.

  • Edward

    Thanks bro. Much love ….

  • Miles

    mysql> use mysql; command gives error saying Access denied for user ‘root’@'localhost’ (using password: NO) I used the other commands exactly as in blog but fails to run the use mysql; command. Any ideas?

  • Andy

    I have followed the instructions shown above. I am getting an error message from the Command Prompt window that states “The system cannot find the path specified”. Any ideas why this would be? Cheers,