How To Change MySQL User Password

After installing MySQL server, we can make connection with the server as a root user by default. For security purpose, sometimes we need to set or change the password for root or any existing user. How you can install and use MySQL on Ubuntu is shown in one of the previously published tutorial. In this tutorial, you will learn how you can set or change the password of any existing MySQL user on Ubuntu.

If no password is set for the root user then you can connect with the MySQL database server by using the following command.

$ mysql -u root

$ sudo mysql -u root

It is necessary to set strong password for root or any other user to provide database security. Two ways are shown in this tutorial to change MySQL user’s password. You can follow any one from these.

Changing Password using SET:

The syntax for changing password of any MySQL user by using set statement is,

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('password');

‘hostname’ for local server is ‘localhost’. Run the following MySQL statement to set/reset password for root user. Here, ‘abc890def’ is set as root password.

> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abc890def');

Exit from the server and try to connect without any password. The following access denied error will appear in the terminal.

Run the command with -p option and test the connection by applying wrong password. Then the following error message will appear.

Now, run the command with correct root password which is set in previous step. If you provide the correct password then it will be able to connect successfully with the server.

$ mysql -u root -p

Changing Password using UPDATE:

The syntax for changing MySQL password using update query is given below. All user’s information is stored in user table of mysql database. So, if you can update the value of Password field of user table for any particular user then the password of that user will be changed properly.

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE
USER='username' AND Host='hostname';

To change the password of root user on local server, you have to run the following SQL command. Here, ‘mynewpassword’ is set as new password.

> UPDATE mysql.user SET Password=PASSWORD('mynewpassword') WHERE
USER='root' AND Host='localhost'

Again, exit from the server and restart the database server before testing the new password.

$ sudo service mysql restart

Now, check the new password is working or not for root user.

$ mysql -u root -p

By following any of the above SQL statements, you can easily change the password of any existing MySQL user.

How To Change MySQL User Password

Changing Password using SET:

Changing Password using UPDATE:

About the author

Fahmida Yesmin