MYSQL - How to change user password

Colleagues hello to all.

Each user or database administrator should have knowledge of how to change the password for an account in the MySQL DBMS. There are several ways to change a user's password in the MySQL DBMS, in particular, this is a password change using the SET PASSWORD command or using the ALTER USER command.

The ALTER USER command is only supported by the latest DBMS versions such as 5.7 or 8.0 and higher, if you have MySQL version 5.x.x installed, then changing the password is possible only through the SET PASSWORD command. Both instructions can be used in newer versions.

Later in this article, we will look at all 2 methods.

Article content:

1. View user host.
2. The SET PASSWORD command.
3. The ALTER USER command.
4. Flush cache.
5. Summary

1. View user host.

In MySQL, each account has its own host to which the account is bound. There are three main types of hosts: localhost, % and IP address/domain name.

• localhost - indicates that the account can access the database only locally without the ability to connect remotely.
• % - indicates that you can connect to the database from any hosts, including remote connections.
• IP address/domain name - indicates that you can only connect to the database from a specific ip address or domain name.

In order to find out what host type an account record it is, you need to execute the following SQL query:

sql> SELECT user, host FROM mysql.user;

All user information is stored in the mysql database in the user table. When changing the account password, you also need to specify the host.

2. SET PASSWORD command.

You can change the MySQL account password using the SET PASSWORD command. In order to change the password of the  inzhener account with host % to Qwerty123! you need to execute sql command in console:

sql> SET PASSWORD FOR 'inzhener'@'%' = 'Qwerty123!';

3. ALTER USER command.

You can also change an account's password using the ALTER USER command. In order to change the password for the inzhener2 account hosted at 192.168.2.2 to Qwerty123! you need to execute sql command in console:

sql> ALTER USER 'inzhener2'@'192.168.2.2' IDENTIFIED BY 'Qwerty123!';

4. Reset cache.

It does not matter what command was executed when changing the password of an account, after changing the password, you must reset the privilege cache. You can do this with the command:

sql> FLUSH PRIVILEGES;

5. Results.

Now you know how to change an account password in MySQL. You can change your password in several ways. Both methods are very simple and effective.

Thank you all, I hope that my article helped you in some way.