How to reset MySQL root password

Posted by ionut
July 31, 2007

To reset a root password that you forgot (using paths on our system):

[root@host root]#killall mysqld
[root@host root]#/usr/libexec/mysqld -Sg --user=root &

You may have better luck with:

mysqld --skip-grant-tables --user=root

Go back into MySQL with the client:

[root@host root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> UPDATE user
-> SET password=password("newpassword")
-> WHERE user="root";
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;

[root@host root]#killall mysqld

Start MySQL the normal way for your system, and all is good. For Red Hat this is:

/etc/init.d/mysqld start

Bye!

*BSD, Linux

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments
Comment by Bill on September 9, 2008 @ 4:07 pm

Thanks for this great posting. I am learning how to use MySQL via command line for the first time. I screwed up setting the root password and after several attempts at following the MySQL documents instructions I found this site. It took less than a minute to reset my password and now I can go back to using MySQL.

Thanks again.

Leave a comment

(required)

(required)