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’;
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.

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

×

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