How to Reset MYSQL Database Root Password

he MySQL root password can be set using the following procedure:

Stop the MySQL service:

# service mysqld stop
Stopping MySQL:                                            [  OK  ]

Start MySQL with:

# /usr/bin/mysqld_safe –skip-grant-tables &

On Red Hat Enterprise Linux 3, mysqld_safe was called safe_mysqld:

# /usr/bin/safe_mysqld –skip-grant-tables &

Note: mysql_safe is a shell script which invokes mysqld, but additionally traps any forceful terminations of the MySQL server and avoids any database corruption.

 
Change the password of the root user:

# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(‘new_password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
mysql> exit;

Restart mysqld using mysqladmin to ensure that the service shuts down successfully (as it was started manually in step 2).

# mysqladmin shutdown

Restart the MySQL service as per normal:

# service mysqld start