MySQL Reset Root Password

Sometimes people need to reset their root mysql password for whatever reason. The following shows you the steps you need to take to reset your root password. Firstly shutdown your mysqld process:

#user@www sudo /etc/init.d/mysqld stop

Secondly create a sql script:

#user@www sudo vi /root/mysql.passwd-reset.sql

Then add the following code to the new sql file:

UPDATE mysql.user SET Password=PASSWORD('YOUR-NEW-MYSQL-PASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

Thirdly start mysql in safe mode and load the new sql file:

#user@www sudo mysqld_safe --init-file=/root/mysql.passwd-reset.sql &

You will see something like this:

nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[20970]: started

Finally restart the mysqld again:

#user@www /etc/init.d/mysqld stop
#user@www /etc/init.d/mysqld start

Enjoy.