Home » Learn – Change User Password

Learn – Change User Password

Learn – Change User Password

MySQL user is a record that provides the login details, account privileges, and host information for the MySQL account that is used to access and control the database. The user name and password are included in the login information. In some circumstances, changing the user password in the MySQL database is required.
You must keep the following facts in mind when changing the password of any user account:

The information about the user account you want to modify.
A programme that the user whose password you want to change uses. The application will be unable to connect to the database server if the user account password is reset without altering the application connection string.
We can modify the user account in MySQL.

ALTER USER Statement

MySQL allows us to change the user account password in three different ways, which are given below:
Let us see how we can change the user account password in MySQL by using the above statement in detail:

Change user account password using the UPDATE statement

This is the first approach to update the user table of the MySQL database by changing the user password. After executing a UPDATE statement, you must use the FLUSH PRIVILEGE statement to reload privileges from the grant table of the MySQL database. If you wish to modify or update the password for a user named Peter who connects from localhost and uses the password use the SQL queries below.

mysql > USE mysql;

The above statement will not function if you are using MySQL version 5.7.6 or higher. It’s because the authentication string field in the MySQL user table only saves the password. The authentication string column is now included in the UPDATE statement in higher versions, as shown in the following sentence.

mysql > UPDATE user SET password = PASSWORD('password') WHERE user = 'VRB' AND host = 'localhost';

mysql > FLUSH PRIVILEGES;

 

mysql > USE mysql;
mysql > UPDATE user SET authentication_string = PASSWORD('password') WHERE user = 'VRB' AND host = 'localhost';

mysql > FLUSH PRIVILEGES;

Using the SET PASSWORD command, change the password for a user account. The SET PASSWORD statement is the second technique to alter a MySQL database user’s password. You must have the UPDATE privilege to change the password for the other account. The SET PASSWORD line utilises the [email protected] format for the user account. For reloading privileges from the grant tables of the MySQL database, there is no need to use the FLUSH PRIVILEGES statement. Using the SET PASSWORD command, we may change the password of user account peter.

mysql > SET PASSWORD FOR 'VRB'@'localhost' = PASSWORD('password');

If you are using the MySQL version 5.7.6 or higher, the above statement deprecated and will not work in future releases. Instead, we need to use the following statement

mysql > SET PASSWORD FOR 'VRB'@'localhost' = password;

For SQL Server related user creation and lockout visit https://sqlserver-dba.co.uk site