Additional languages
« B.1.4 Administration-Related Issues
B.1.4.2 What to Do If MySQL Keeps Crashing »
Section Navigation      [Toggle]

B.1.4.1. How to Reset the Root Password

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.6.1, “General Security Guidelines”.

If you set a root password previously, but have forgotten what it was, you can set a new password. The following procedure is for Windows systems. The procedure for Unix systems is given later in this section.

The procedure under 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 command within it on a single line: 

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

    Save the file with any name. For this example the file will be C:\mysql-init.txt.

  4. Open a console window to get to the DOS command prompt: 

    Start Menu -> Run -> cmd

  5. We are assuming that you installed MySQL to C:\mysql. If you installed MySQL to another location, adjust the following commands accordingly.

    At the DOS command prompt, execute this command: 

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

    The contents of the file named by the --init-file option are executed at server startup, changing the root password. After the server has started successfully, you should delete C:\mysql-init.txt.

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

    C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt.exe"
         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.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.

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

  7. You should be able to connect using the new password.

In a Unix environment, the procedure for resetting the root password is as follows:

  1. Log on to your system as either the Unix root user or as the same 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, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname.

    You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname 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 command within it on a single line: 

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

    Save the file with any name. For this example the file will be ~/mysql-init.

  4. Restart the MySQL server with the special --init-file=~/mysql-init option: 

    shell> mysqld_safe --init-file=~/mysql-init &

    The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

  5. You should be able to connect 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 --user=root options (Windows users omit the --user=root portion).

  2. Connect to the mysqld server with this command: 

    shell> mysql -u root

  3. Issue the following statements in the mysql client: 

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

    Replace “newpwd” with the actual root password that you want to use.

  4. You should be able to connect using the new password.


User Comments

Posted by Annadata Prasad on January 3 2005 4:36pm[Delete] [Edit]

Version 4.1.8. With this version the given command

mysqld_safe --skip-grant-tables &

does not work as is. As soon as you enter the command, mysqld_safe quits. This is because, with 4.1.8 they have put security check to see if mysql is being run as root. So, it needs to be explicity specified that it needs to be run as root. So, everything else in the given instruction remains the same. Insted of the above command run

mysqld_safe --skip-grant-tables -u root &




Posted by Darrell Duane on January 24 2005 7:14pm[Delete] [Edit]

Actually, with MySQL 4.1.8 I found that to get the user to be root I had to start it like this:
mysqld_safe --skip-grant-tables --user=root &

Note also that the password is what is enclosed in double quotes above, and remember that you won't be able to log in with the new password until the MySQL daemon is restarted, reloading user table into memory.

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 Andrew Foster on October 25 2005 3:29am[Delete] [Edit]

When resetting the root password on a Unix system, be sure to supply the --init-file argument with the _full_ system path to the file, regardless of your current working directory.

Posted by Gal Aviel on November 30 2005 7:35pm[Delete] [Edit]

Here's another pitfall.
Remember, that the init file has to be readable
by the process 'mysqld' is running as. In my case, a RedHat9
machine, it's running as user 'mysql' (although initially launched by/as user root).

BUT I named the init
file /root/init.txt which is not readable by user 'mysql' (no permissions) !

What's worse, the error message in the log file was cryptic
and did not say "can't read file because permission denied" rather "can't find file .."
.. go figure.

Buttom line, put the file in a public place that mysql can see or else you'll get a cryptic error message.

Posted by Martin Kestel on December 27 2005 6:59pm[Delete] [Edit]

On Fedora Core 3 the command to start mysqld manually is 'safe_mysqld' (not: 'mysqld_safe').

Posted by Donny Widjaja on February 1 2006 7:07am[Delete] [Edit]

If you need to reset the password in Windows environment, under step number 5. You can add "--console" at the end of the command to show the output in your console instead of logfile.
For example:
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 --console

If you have your server setup correctly, you will see:
060201 0:53:44 [Note] mysqld-nt: ready for connections.
Version: '5.0.18-nt' socket: '' port: 3306 MySQL Community Edition (GPL)

In step number 6, where you need to shutdown the server, you can open another windows cmd console (Start Menu -> Run -> cmd).
Use "mysqladmin -u root -p shutdown" to shutdown the MySQL server. It will prompt you to enter a password, and you can enter the password you specified in the mysql-init.txt file.

Posted by Carlos Guerrero on August 17 2006 4:39am[Delete] [Edit]

Thanks to all:

I have been banging my head for the past 2 days trying to get around this stupid error:

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Just make sure to use the command as stated by the first comment:

root@ubuntu:~# mysqld_safe --skip-grant-tables -u root &

Thanks again.

Carlos.

Posted by les hawkins on August 26 2006 4:02pm[Delete] [Edit]

Trying to be good I had deleted root but then found when I needed super privideges that no user had them - my fault I know but I think an easy mistake to make (I am using SUSE 10.1). The skip grant options worked but I had to FLUSH Privileges before trying to change any access permissions - otherwise they can't be effective. The statement to make a super user is
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME' @ 'localhost' WITH GRANT OPTION MAX QUERIES PER HOUR 0 MAX CONNECTIONS PER HOUR 0 MAX_UPDATES PER HOUR 0 MAX_USER_CONNECTIONS 0;

The stop command for SUSE 10.1 is kill `cat /var/lib/mysql/mysqld.pid`
after restart of mysqld I had to open a new terminal to log on to the server ..
Clearly skip grant tables is terribly insecure as any user can do anything while it is operation. Hope it helps someone

Posted by Mark Garner on September 11 2006 6:06am[Delete] [Edit]

Amen to Schneelocke for the Windows installations/ using the MySQL System Tray Monitor option.

I've been trying to resolve this for the past week, been going thru dozens of forums and posts trying to get an answer.

Did what Scheenllocke said and it worked instantly.

PS Judging by what I've seen in other posts this whole local connection password thing is a major issue for newcomers.

Might be an idea if someone could make it a easy to find topic and solution in the manual.

Posted by Laurent MIMOUN on September 16 2006 1:46pm[Delete] [Edit]

Yes I confirm the init file path must be explicit. ~/init.txt is bad !

Posted by arthur brogard on October 16 2006 3:40am[Delete] [Edit]

Yes, I've been hassled with this for the last two weeks... reading heaps and getting nowhere.

I found downloading and installing mysql gui tools was the answer. I only needed to insert the correct root password it turned out. When I ever changed it I've got no idea but mysql admin helped me sort it out in seconds....

:)

Posted by mark garey on November 16 2006 7:19pm[Delete] [Edit]

my experience was that if you start mysql with --skip-grant-tables and
--user=root, mysql will set the ownership of the log file to root:root
and after you have reset your root password and start mysql again
normally, the startup will fail because of the permissions on that log
file. the fix is to chown the log file back to user mysql:mysql and start again.
fwiw, because of the permissions you get the "file not found" error
mentioned above. check the error log file for details e.g.:

/usr/local/mysql-5.0.22/libexec/mysqld: File './mysql-bin.000087' not found (Errcode: 13)
061115 17:08:35 [ERROR] Failed to open log (file './mysql-bin.000087',
errno 13)
061115 17:08:35 [ERROR] Could not open log file
061115 17:08:35 [ERROR] Can't init tc log
061115 17:08:35 [ERROR] Aborting

good luck!

m+

Posted by Aki Tuomi on December 11 2006 9:26pm[Delete] [Edit]

Lost root privs somehow? Use this to restore full privs to 'root'@'localhost'. NOTE that you HAVE TO use SET PASSWORD to restore password for root.

USE mysql;
REPLACE INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);

You can also add the password into this line, but that is left for the reader to find out how.

Posted by Yoseff Francus on December 28 2006 4:37pm[Delete] [Edit]

The problem with all the solutions other than the replace values is that mysql complains that it cannot fulfill the request to change a password while in safe mode since the where clause is not referencing a key column. Replace was the only way to get around it but it creates a second entry. What puzzles me is that the password should have remained after moving to mysql5 from mysql4

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.

Add your own comment.