In some cases you might want to change or reset MySQL root password.
To do this there are two ways, using mysqladmin command or using UPDATE sql command.
1. Using mysqladmin command.
Login to the server where the MySQL database is installed. From the current shell run mysqladmin command below:
$ mysqladmin -u root -pcurrentpassword password 'newpassword'
Note that between the option ‘-p’ and the ‘currentpassword’ must be without any space.
For the ‘-u’ option and ‘root’ it can be with or without space. And also the ‘newpassword’, it can be with or withour single quote.
So the following two commands will just work.
$ mysqladmin -uroot -pcurrentpassword password 'newpassword'
or
$ mysqladmin -uroot -pcurrentpassword password newpassword
Verify that you can login to the MySQL using the newpassword.
$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 207702
Server version: 5.0.67 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2. Using UPDATE SQL command
Using this method you have to login first to MySQL using existing root password. On the mysql command prompt, run the following command below to change the MySQL root password.
mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Again verify that you can login using the newpassword.
$ mysql -uroot -pnewpassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 207736
Server version: 5.0.67 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>