Monday, May 26, 2014

HOWTO Change MySQL root Password

Method 1: Changing MySQL root user password using "mysqladmin"

To setup root password for first time, use mysqladmin command at shell prompt as follows:
root@server:~# mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use the following command:
root@server:~# mysqladmin -u root -p'oldpassword' password newpass

For example, If the old password is abc, you can set the new password to 123456, enter:
root@server:~# mysqladmin -u root -p'abc' password '123456'

How do I verify that the new password is working or not?

Use the following mysql command:
root@server:~# mysql -u root -p'123456' db-name-here
# OR
root@server:~# mysql -u root -p'123456' -e 'show databases;'

A note about changing MySQL password for other users

To change a normal user password you need to type the following command:
root@server:~# mysqladmin -u user -p'old-password' password new-password

Method 2: Changing MySQL root user password using mysql command

This is an another method. You can directly update or change the password using the following method for the user called "test":

Login to mysql server, type the following command at shell prompt:
root@server:~# mysql -u root -p
mysql> use mysql;

Change password for user "test", enter:
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='test';

Finally, reload the privileges:
mysql> flush privileges;
mysql> quit