Resetting the root password on a MySQL database


Resetting the root password on a MySQL database

You are not alone.  Passwords get lost, forgotten, or in my case, unintentionally altered.  I recieved a SQL dump from a client that happened to contain all the system tables.  When I uploaded the database it overwrote the local MySQL database’s root password, which I didn’t find out until I tried to log in the next day.  Luckily, there’s a couple of ways to reset the password which are detailed in the MySQL documentation.

The first method is by far the safest and best method.  This is already well covered in the documentation, however, in my case I just could not get it to work.  Every time I tried to restart the MySQL service with the new initialization script, it hung.  At the bottom of the documentation, it mentions another method that works on all platforms (in either Window’s command prompt or the Unix shell).  After a bit of research and testing, I was able to reset the password using this less secure method.  Do not use this method if you are worried about security and cannot take your database off of the network.

In Windows, shutdown the MySQL service.  This is done easily by opening up the Control Panel -> Administrative Tools -> Services.  Find MySQL, right click, and select “Stop”.  If you do not know or remember the path to your MySQL installation, right click the MySQL service again and select Properties.  You should see the path to the executable in the popup.

Open up the Command Prompt.  Restart the MySQL server with the “–skip-grant-tables” option.

“C:Program FilesMySQLServerbinmysqld” –skip-grant-tables /

You can now connect to the server and issue the reset command:

C:> “Program FilesMySQLServerbinmysql”
mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpassword’)
         ->  WHERE User = ‘root’;

You now have a new password!  Make sure to go back to Control Panel -> Administrative Tools -> Services to stop and restart the MySQL service again to restore security to the table.

More About the Author

Ben Bausili

Global Director of Product
How Do You Make an AI Chatbot? Have you ever considered creating a chatbot? It might seem like a straightforward task, but, in reality, it involves more complexities ...
Solution Stories: Deep Dive To Uncover Hidden Data Issues Welcome to our latest Solution Stories blog series, where we delve into real-world client stories that offer valuable insights into ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!