MySQL user is a record that contains the login information, account privileges, and the host information for MySQL account to access and manage the database. The login information includes the user name and password. In some cases, there is a need to change the user password in the MySQL database.
To change the password of any user account, you must have to keep this information in your mind:
- The details of the user account that you want to change.
- An application used by the user whose password you want to change. If you reset the user account password without changing an application connection string, then the application cannot connect with the database server.
MySQL allows us to change the user account password in three different ways, which are given below:
- UPDATE Statement
- SET PASSWORD Statement
- ALTER USER Statement
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 statement is the first way to change the user password for updating the user table of the MySQL database. Here, you have to use the FLUSH PRIVILEGE statement after executing an UPDATE statement for reloading privileges from the grant table of the MySQL database.
Suppose, you want to change or update the password for a user peter that connects from the localhost with the password jtp12345, execute the SQL statements as below:
mysql> USE mysql;
mysql> UPDATE user SET password = PASSWORD('jtp12345') WHERE user = 'peter' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;If you are using the MySQL version 5.7.6 or higher, the above statement will not work. It is because the MySQL user table contains the authentication_string column that stores the password only. Now, the higher versions contain the authentication_string column in the UPDATE statement, like the following statement.
mysql> USE mysql;
mysql> UPDATE user SET authentication_string = PASSWORD('jtp12345') WHERE user = 'peter' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;Change user account password using SET PASSWORD statement
The SET PASSWORD statement is the second way to change the user password in the MySQL database. If you want to change the other account password, you must have the UPDATE privilege. The SET PASSWORD statement uses the user account in the username@localhost format.
There is no need to use the FLUSH PRIVILEGES statement for reloading privileges from the grant tables of the MySQL database. We can use the following statement to change the password of user account peter by using the SET PASSWORD statement:
mysql> SET PASSWORD FOR 'peter'@'localhost' = PASSWORD('jtp12345'); 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 'peter'@'localhost' = jtp12345; Change user account password using ALTER USER statement
The ALTER USER statement is the third way to change the user password in the MySQL database. MySQL uses ALTER USER statement with the IDENTIFIED BY clause for changing the password of a user account. We need to use the following syntax to change the password of a user peter with jtp123.
mysql> ALTER USER peter@localhost IDENTIFIED BY 'jtp123'; Sometimes, you need to reset the MySQL root account password. In that case, you can force to stop and restart the MySQL database server without using the grant table validation.
Leave a Reply