A.4.2 How to Reset a Forgotten Root Password

If you never set a root password for MySQL, then the server will not require a password at all for connecting as root. It is recommended to always set a password for each user. See section 4.2.2 How to Make MySQL Secure Against Crackers.

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:

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

   You must be either the Unix root user or the same user mysqld runs as to do this.
2. Restart mysqld with the --skip-grant-tables option.
3. Set a new password with the mysqladmin password command:

   shell> mysqladmin -u root password 'mynewpassword'
   

4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:

   shell> mysqladmin -h hostname flush-privileges
   

5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the --skip-grant-tables option as described above.
2. Connect to the mysqld server with:

   shell> mysql -u root mysql
   

3. Issue the following commands in the mysql client:

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

4. After this, you should be able to connect using the new password.
5. You can now stop mysqld and restart it normally.

User Comments

Users of Red Hat Linux with MySQL installed from the
distribution RPM may find the PID in
/var/run/mysqld/mysqld.pid

Please note that this will not work when you start mysql with the safe_mysqld binary. You *have* to run mysqld.

I used the safe_mysqld with the MySQL version 3.23.54 that is installed with Red Hat 8.0 and the --skip-grant-tables option worked just fine for me. I did the following as root:

# /etc/rc.d/init.d/mysqld stop
# /usr/bin/safe_mysqld --skip-grant-tables
# mysql -u root
mysql> use mysql;
mysql> update user set Password = PASSWORD('pass') where User ='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> exit
Bye
# /etc/rc.d/init.d/mysqld stop #yes this worked
# /etc/rc.d/init.d/mysqld start

Sometimes I have not been able to log on as root, then this solution worked for me;


# /etc/rc.d/init.d/mysqld stop
# /usr/bin/safe_mysqld --skip-grant-tables
# mysql -u root
mysql> use mysql;
mysql> update user set Password = PASSWORD('pass') where User ='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> exit
Bye
# /etc/rc.d/init.d/mysqld stop #yes this worked
# /etc/rc.d/init.d/mysqld start

The line:
2. Restart mysqld with the --skip-grant-tables option.

Should be expanded to include a full example, like:
/usr/bin/safe_mysqld --skip-grant-tables

Lots of folks only ever start/stop mysql using
/etc/init.c/mysqld stop|start

and adding --skip-grant-tables won't work.. finding the correct binary file to use to manually start the mysqld wasn't all that intuitive either...

Mike

Add your own comment.