How to recover the mysql root password?

Steps to recover the mysql root password

1.Stop the mysql service

# service mysql stop

(or)

# /etc/rc.d/init.d/mysql stop

2.Start mysql in safe mode by using command mysqld-safe

# mysqld_safe –skip-grant-tables

It will not read the grant tables and allows anybody to enter in to mysql without asking the password.

3.Login to mysql

# mysql -u root

4.Use the mysql database which contains the passwords for all the databases and update the root password with the following command

# use mysql;

# UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’root’;

5. Exit from mysql and stop the mysql service

mysql> exit

# service mysql stop

6. Remove the entry “skip-grant-tables” in the mysql startup script /etc/rc.d/init.d/mysql

# vi /etc/rc.d/init.d/mysql

Old entry: $bindir/mysqld_safe –skip-grant-tables –datadir=$datadir –pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

New entry: $bindir/mysqld_safe –datadir=$datadir –pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

7. Start mysql service

# service mysql start

Now it will ask the password when some one tries to login to mysql

8. Login to the mysql root accout with the new password

# mysql -u root -p newpassword