To reset the MySQL root password under Red Hat Linux:
[root@host root]# service mysqld stop
[root@host root]# /usr/libexec/mysqld -Sg --user=root &
[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.58
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("new_password") 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 again.
service mysqld start
October 7th, 2004 at 15:35
Thanks for pointing me at this, James..
If you ever find you need to recreate the correct permissioins for the root user rather than just reset their password, the following command may help:
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ’secret’ WITH GRANT OPTION;