Skip navigation links
A.5.4.2 What to Do If MySQL Keeps Crashing »
Section Navigation      [Toggle]

A.5.4.1. How to Reset the Root Password

[+/-]

A.5.4.1.1. Resetting the Root Password on Windows Systems
A.5.4.1.2. Resetting the Root Password on Unix Systems

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. However, it is recommended to set a password for each account. See Section 5.4.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The next two sections show procedures for Windows and Unix systems, respectively.

A.5.4.1.1. Resetting the Root Password on Windows Systems

Use the following procedure for resetting the password for any MySQL root accounts on Windows:

  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: 

    Start Menu -> Control Panel -> Administrative Tools -> Services

    Then 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 and place the following statements in it. Replace the password with the password that you want to use. 

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

    The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

  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: 

    Start Menu -> Run -> cmd

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

    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.

    Users of MySQL 4.1 and higher who installed MySQL using the MySQL Installation Wizard may need to specify a --defaults-file option: 

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

    The appropriate --defaults-file setting can be found using the Services Manager: 

    Start Menu -> Control Panel -> Administrative Tools -> Services

    Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.

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

  8. 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.

You should now be able to connect to MySQL as root using the new password.

A.5.4.1.2. Resetting the Root Password on Unix Systems

MySQL Enterprise.  For expert advice on security-related issues, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

Use the following procedure for resetting the password for any MySQL root accounts on Unix. 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 the mysql 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 start mysqld 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 to mysql or remove them.)

  1. Log on to your system as the Unix mysql user that the mysqld server runs as.

  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 (not kill -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`

    Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

  3. Create a text file and place the following statements in it. Replace the password with the password that you want to use. 

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

    The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

  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.

  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 MySQL as root using the new password.

Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):

  1. Stop mysqld and restart it with the --skip-grant-tables option.

  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;

You should now be able to connect to MySQL as root using the new password.


User Comments

Posted by Dino Tsoumakis on February 3 2005 7:48am[Delete] [Edit]

If you upgrade from MySQL 3 to MySQL 4.1 without updating Grant Tables, MySQL uses the old password hashing mechanism by default.
But if you start it with '--skip-grant-tables' it uses the new password hashing mechanism!
To be able to reset your root password, you must add the '--old-passwords' option, e.g.:
mysqld_safe --skip-grant-tables --old-passwords &


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 Sjolus on January 22 2007 12:44pm[Delete] [Edit]

If you use PhpMyAdmin, don't forget to edit config.inc.php to match your new password as well, even if this doesn't fit on the mysql-site, it is still worth mentioning. At least it haunted me for 30 stressful minutes.

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 motor2000 motor2000 on July 18 2008 7:13pm[Delete] [Edit]

Thanks Dino Tsoumakis for your comment. I wasted a few hours to resolve this issue, and nowhere in this guide it says that we have to use --old-passwords option!!!

Can someone please update this guide?

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 Shlomi Noach on November 13 2008 7:31am[Delete] [Edit]

If you do use --skip-grant-tables (and the --init-file option is preferable), you must remember to restart MySQL -again- without this flag, after you have set the new root password.

While in --skip-grant-tables mode, anyone can log into the server and do as they please. When starting with this flag, you may also wish to use --skip-networking.

Posted by Matt Murdock on November 9 2009 9:28pm[Delete] [Edit]

Dont know why, but I had to use the mysqld_safe --skip-grant-tables --socket=/var/lib/mysql.sock and not put it in the background (probably didnt matter). Then I was able to connect from the shell with mysql then ran the update....password stuff.

Posted by Mike Baker on December 8 2009 12:35am[Delete] [Edit]

If you already know the root password, but would like to change it, the following works on Windows MySQL 5.1:

SET PASSWORD FOR root@localhost=PASSWORD('newpass');

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

Add your own comment.