In this tutorial, we will look at how to change a user's password in MySQL. It is a good practice to change passwords at regular intervals and the password used to log in to the MySQL server is no different.
Any user can change their own MySQL password but only users with update privileges on the mysql database can change another user's password. By default, only the root user (the user with superuser privileges on the database) can do this. The SET PASSWORD statement is used to assign/change a MySQL user password.
The syntax for the MySQL SET PASSWORD statement to change an arbitrary user's password is as follows:
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpassword');
To change your own password, the syntax is:
SET PASSWORD = PASSWORD('newpassword');
That is, to change another user's password, you will need to specify their username and hostname exactly as it is in the User and Host columns in the mysql.user table.
To change the password for 'joseph' at '%.somedomain.com' to 'letmein' we would do this:
SET PASSWORD FOR 'joseph'@'%.somedomain.com' = PASSWORD('letmein');
As another example, if we wanted to change our own password to 'newpassword345' we would simply log in to the MySQL server and execute the following:
SET PASSWORD = PASSWORD('newpassword345');
And that's all there is to it. Now you know how to change your own password or the password of another user if you have the appropriate permission.
We can also use the GRANT statement to change a user's password. To change the password for 'joseph' at '%.domain.com' to 'letmein' we would do it this way using the GRANT statement:
GRANT USAGE ON *.* TO 'joseph'@'%.domain.com' IDENTIFIED BY 'letmein';
As always, you will need the requisite permission before you can change a user's password.
We hope this MySQL tutorial on changing user passwords was helpful.