Steps to reset MySQL root password

                         [root@xyzhost conf]# mysql -u root -p         <-- Mysql root password isn't working

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@xyzhost conf]#

Check if the Mysql process is running (its running here)

[root@xyzhost init.d]# ps -ef | grep -v grep | grep mysql

root      2104     1  0 Jan05 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql

mysql     2154  2104  0 Jan05 ?        00:00:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

[root@xyzhost conf]#

Time to reset the Mysql root password !!

Stop the mysql service

[root@xyzhost init.d]# /etc/init.d/mysqld stop

Stopping MySQL:                                            [  OK  ]

Start the Mysql in Safe-mode

[root@xyzhost init.d]# mysqld_safe --skip-grant-tables &

[1] 7256

[root@xyzhost init.d]# Starting mysqld daemon with databases from /var/lib/mysql

[root@xyzhost init.d]# mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.82sp1 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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("mysqlnewp@$$") where User='root';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

Stop the Mysql service

[root@xyzhost init.d]# /etc/init.d/mysqld stop

STOPPING server from pid file /var/run/mysqld/mysqld.pid

110721 12:58:44  mysqld ended

Stopping MySQL:                                            [  OK  ]

[1]+  Done                    mysqld_safe --skip-grant-tables

Start the Mysql service

[root@xyzhost init.d]# /etc/init.d/mysqld start

Starting MySQL:                                            [  OK  ]

Connect to Mysql DB using new password:

[root@xyzhost init.d]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.82sp1 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql                   |

| customers            |

| test                      |

| clients_db            |

+--------------------+

5 rows in set (0.00 sec)

mysql>

Thats it !!