reset the password for all MySQL root accounts

 

On Windows, use the following procedure to reset the password for all MySQL root accounts:

1. Log on to your system as Administrator.

2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From theStart menu, select Control Panel, thenAdministrative Tools, then Services. Find the MySQL service in the list and stop it.

   If your server is not running as a service, you may need to use the Task Manager to force it to stop.

3. Create a text file containing the following statements. Replace the password with the password that you want to use.

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';FLUSH PRIVILEGES;

   Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4. Save the file. For this example, the file will be named C:\mysql-init.txt.

5. Open a console window to get to the command prompt: From the Start menu, select Run, then enter cmd as the command to be run.

6. Start the MySQL server with the special --init-file option (notice that the backslash in the option value is doubled):

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

   If you installed MySQL to a location other than C:\mysql, adjust the command accordingly.

   The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

   You can also add the --console option to the command if you want server output to appear in the console window rather than in a log file.

   If you installed MySQL using the MySQL Installation Wizard, you may need to specify a--defaults-file option:

   C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"         --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.0\\my.ini"         --init-file=C:\\mysql-init.txt

   The appropriate --defaults-file setting can be found using the Services Manager: From theStart menu, select Control Panel, thenAdministrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose theProperties option. The Path to executable field contains the--defaults-file setting.

7. After the server has started successfully, delete C:\mysql-init.txt.

You should now be able to connect to the MySQL server as root using the new password. Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. If you start the server manually, use whatever command you normally use.

C.5.4.1.2. Resetting the Root Password: Unix Systems

On Unix, use the following procedure to reset the password for all MySQL root accounts. The instructions assume that you will start the server so that it runs using the Unix login account that you normally use for running the server. For example, if you run the server using themysql login account, you should log in as mysql before using the instructions. Alternatively, you can log in as root, but in this case you must startmysqld with the --user=mysql option. If you start the server as root without using --user=mysql, the server may create root-owned files in the data directory, such as log files, and these may cause permission-related problems for future server startups. If that happens, you will need to either change the ownership of the files tomysql or remove them.

1. Log on to your system as the Unix user that the mysqld server runs as (for example,mysql).

2. Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are/var/lib/mysql/, /var/run/mysqld/, and/usr/local/mysql/data/. Generally, the file name has an extension of.pid and begins with either mysqld or your system's host name.

   You can stop the MySQL server by sending a normal kill (notkill -9) to the mysqld process, using the path name of the.pid file in the following command:

   shell> kill `cat /mysql-data-directory/host_name.pid`

   Use backticks (not forward quotation marks) with the cat command. These cause the output ofcat to be substituted into the kill command.

3. Create a text file containing the following statements. Replace the password with the password that you want to use.

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';FLUSH PRIVILEGES;

   Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4. Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users. If you are not logged in asmysql (the user the server runs as), make sure that the file has permissions that permitmysql to read it.

5. Start the MySQL server with the special --init-file option:

   shell> mysqld_safe --init-file=/home/me/mysql-init &

   The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

6. After the server has started successfully, delete /home/me/mysql-init.

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally.

C.5.4.1.3. Resetting the Root Password: Generic Instructions

The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using themysql client (but this approach is less secure):

1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use--skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

2. Connect to the mysqld server with this command:

   shell> mysql

3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.

   mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')    ->                   WHERE User='root';mysql> FLUSH PRIVILEGES;

   The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the--skip-grant-tables and --skip-networking options).

Copyright © 1997, 2011, Oracle and/or its affiliates. All rights reserved.Legal Notices

Previous /Next / Up / Table of Contents

User Comments

Posted by Schneelocke on February 16 2005 1:31pm[Delete] [Edit]

On Windows installations, it is also possible to disable grant table loading and restart the server using the MySQL System Tray Monitor (the relevant option is under Configure Instance->Startup Variables->Security).

I got all sorts of unhelpful error messages when trying to reset the root password on the console as described, but using the System Tray monitor and the graphical MySQL Administrator worked flawlessly.

Posted by Chetan Graham on May 12 2008 1:58pm[Delete] [Edit]

Make sure you have your InnoDB Base Directory and Data Directory path's correct or you will continue to get this error. "Error 1045 Access denied for 'root'@'localhost'[using password: Yes] "

If you have InnoDB set up you must have the correct path to these two directories in MySQL.

I've learned the hard way for the last several years on similar errors.

Oh, Also please understand, that [using password: Yes] does not necessarily mean you are using the wrong password.
You might need to allow mysqld-nt.exe from your FireWall or your Virus Software has an inbound FireWall setup or you do not have Administrative rights to do this installation (if you are installing for first time.)

Hope this helps someone.
Blessings,
Chetanji

Posted by Michael Newton on September 12 2008 9:01pm[Delete] [Edit]

You may need to escape backslashes if doing this in Windows. I set init-file=c:\t.txt and got an error in my log. It said it could not find the file 'c:____.txt'; it had converted \t to a tab character.

Posted by cnemo why on September 23 2008 2:36am[Delete] [Edit]

I also found a good example on how to reset is here: http://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/

Posted by Cameron James on January 13 2010 2:41pm[Delete] [Edit]

If you're looking to fix root's permissions, not just reset the password, insert the following code on a new line at the start of the mysql-init file.
It will effectively reset root with the permissions to do whatever root pleases.

grant all privileges on *.* to root@localhost;

Posted by Mofassir Haque on January 15 2010 11:54am[Delete] [Edit]

You can delete root password by re-installing MySqL but mysql folder under program files and under my documents must be deleted first.

Posted by jacques montes on February 26 2010 1:53pm[Delete] [Edit]

When resetting the root password, I had to remove the previously directory "c:\Documents and Settings\Administrator\Application Data\MySQL" to be able to reset the password. And in my case the directory was hidden ...

Posted by Brenda Wallace on October 27 2010 8:57pm[Delete] [Edit]

on debian and ubuntu you can reset the password by doing:

sudo dpkg-reconfigure mysql-server-5.1

Posted by sam sherlock on November 9 2010 10:05pm[Delete] [Edit]

Windows 7 when reseting root password using this method I had to run cmd as administrator before the advice above would take effect.

(and yes I did indeed install mysql and forget what pass I gave root; almost immediately - do-do-donut)

Posted by Klaus Slott on December 3 2010 1:14pm[Delete] [Edit]

If using the example above on a unix system and MySQL is running on non-root account: Remember to ensure that file permissions for the mysql-init file allows mysqld to read the file.

Posted by Barry Galbraith on February 4 2011 3:07am[Delete] [Edit]

When resetting root password in Windows, if you start the server with
>mysqld --defaults-file="c:\mysql\my.ini" --init-file=c:\\init.txt
the command will start the server, but not return to a prompt. It will appear to lock up.

Better to use

>start mysqld --defaults-file="c:\mysql\my.ini" --init-file=c:\\init.txt

It opens a new window, starts mysqld, the new window will close, and return to a prompt.

Posted by Feliciano Exposito Guerrero on August 19 2011 7:33pm[Delete] [Edit]

Hello People,

Thanx for this hint, It rescues me from a not so nice situation. It runs also with mysqld. Not only with mysqld-nt. Nice to see, that backwards thinking is so practical and useable.

Bye, Feliciano

Add your own comment.