How to reset MySQL root password

Tue 31 July 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