How to change MySQL root password

Q. How can you change root password of MySQL when you've forgotten root password and when you know root password.

ANS:

I will try to explain the procedure to change MySQL root password in both scenarios.

When Forgotten Root Password

Step # 1: First of all stop MySQL.
Step # 2: You need to disable password prompt of MySQL. So start mysql MySQL with the --skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to MySQL using root user.
Step # 4: Setup new root password
Step # 5: Quit and then restart MySQL

The commands that you will use to achieve this are given below.

Step # 1 : First of all stop MySQL.

# /etc/init.d/mysql stop

OR
# service mysql stop

Step # 2 : Start MySQL without password prompt.

# /etc/init.d/mysql stop

Step # 3 : Connect to MySQL.
MySQL -uroot

Step # 4 : Setup new root password.

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5 : Stop MySQL.

# /etc/init.d/mysql stop

OR

# service mysql stop

Step # 6 : Start MySQL and test with new password.
# /etc/init.d/mysql start
OR
# service mysql start

# mysql -u root -p

That's it and you have setup new root password in case you forgot root password.

When Root Password is known

If you never setup root password of MySQL then it doesn't require password to connect as root. To setup root password for first time, use mysqladmin command at shell prompt as given below:

$ mysqladmin -u root password NEWPASSWORD

But when you want to change existing root password then use following mysqladmin command at shell.

$ mysqladmin -u root -p'oldpassword' password newpass

Let suppose root password is zaeem and you want to change it to abc123 then command would look like

$ mysqladmin -u root -p'zaeem' password 'abc123'

I hope you find this article helpful.