Resetting the root password on a MySQL database

Dev

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’;
mysql>FLUSH PRIVILEGES;

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 Experience Practice Director
The Biggest Takeaways from Tableau Conference 2022 Christmas, I mean Tableau Conference, came early this year!  That’s right, the 2022 Tableau Conference has already come and gone, ...
Better, Faster, Stronger: The Power of the Cloud in BI Two weeks ago, my fellow content connoisseur Andrea Avey sat down with InterWorks IT gurus Scott Matlock and Tim Rhymer to talk about ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

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
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

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