Reset MySQL 'root' Password

From this post I tried to explain you about setting the root/user password for mysql. But What if you forgot  root password of mySQL server ?

It happens,well we have a solution for that also.

NOTE: THIS IS ONLY FOR UBUNTU

Please open Terminal from Applications->Accessories->Terminal

Step 1: Stop MySQL Service

# /etc/init.d/mysql stop

For this you should have root user privileges. Use sudo before the code.

Step 2: Create a new SQL script for resetting your password

# nano /root/mysql.reset.sql

nano is a text editor which is default in Ubuntu. You can select any of your favourite editors like vi,gedit.

Append the following code and replace YOUR-NEW-MYSQL-PASSWORD with actual new password you would like to use

UPDATE mysql.user SET Password=PASSWORD('YOUR-NEW-MYSQL-PASSWORD' WHERE  

User='root'; 

FLUSH PRIVILEGES;           

Step 4: Set the new-mySQL-password

# mysqld_safe --init-file=/root/mysql.reset.sql &

It will output some warning like this:

nohup: ignoring input and redirecting stderr to stdout

Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe[20970]: started

And you are done with resetting operation. Simply, stop server and start again:

# killall mysqld
# /etc/init.d/mysql start