One of the important operations when working with users in the MySQL DBMS is changing the user’s password. Typically, only the superuser named root can change a user’s password. However, if a regular user is authorized to change the password, then he can also do this.

This article will show you how to change MySQL user password in Ubuntu 20.04 operating system.

How to change MySQL user password

There are several ways to change a user’s password in MySQL, in particular, this is a password change using the SET PASSWORD or ALTER USER command . Further in the article, all 2 methods will be considered.

Step 1. How to check the MySQL version

As of now (February 2022), MySQL supports two major versions. The first one is outdated, but still supporting version 5.7 and the latest up-to-date version 8.0. In order to find out the version you are using, you need to run the following command in the terminal:

mysql --version

How to change MySQL user password

In this case, version 8.0.28 is used. The ALTER USER statement is only available in modern versions of MySQL. If you have version 5.xx then you should use SET PASSWORD in it. In newer versions, both instructions can be used.

Step 2. Logging into the MySQL Console

In order to get into the MySQL console, you need to log in using your username and password. If the required user is already registered on the server, then the login must be performed on behalf of this user. If you are using MySQL for the first time after installation, then you must log in using the root user. The root login command looks like this (where the desired username can be used instead of the root user):

mysql -u root -p

How to change MySQL user password

Next, the program will ask for a password. For security purposes, password is not displayed in the terminal. After entering the password, you must press the Enter key . After that, the MySQL console client interface will be displayed, where you can use SQL commands.

Step 3. View user hosts

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

The localhost record type means that under the account you can only log in locally without the possibility of remote connection, the % (percentage) symbol means that you can use such an account from any hosts (including remote ones), IP address/domain name means that you can use the account is possible only on the selected domain name/IP address. In order to find out the host type of an account, you need to execute the following SQL query:

SELECT user, host FROM mysql.user;

How to change MySQL user password

In this screenshot, you can see that user alex is using host %. Accordingly, this host should be used when changing the password. Users in MySQL and all user information is stored in the mysql database in the user table.

Step 4. Change the user’s password using SET PASSWORD

If you need to change the password for the root user, see Reset the MySQL root password . You can change the MySQL user password using the SET PASSWORD command . To change the password for user alex with host % to somepassword666, run the following command:

SET PASSWORD FOR 'alex'@'%' = 'somepassword666';

How to change MySQL user password

Step 5. Change the user’s password with ALTER USER

You can also set the user’s password using the ALTER USER command . To change password for user alex with host % to anotherpassword666 , execute SQL query:

ALTER USER 'alex'@'%' IDENTIFIED BY 'anotherpassword666';

How to change MySQL user password

Note that the username and hostname of the user are enclosed in single quotes, without these quotes MySQL will not be able to recognize the string.

Step 6. Reset the privilege cache

Regardless of which command was issued to change the password, the privilege cache must be reset after the password has been changed. You can do this by running the command:

FLUSH PRIVILEGES;

How to change MySQL user password

Step 7: Checking the new password

After the password has been changed and the privilege cache has been flushed, it is necessary to check the login as the user whose password was changed. To do this, exit the MySQL shell by executing the exit command :

exit

How to change MySQL user password

Next, you need to enter the MySQL shell under the required user by entering his new password. For example, let’s log in as the user alex, whose password was changed earlier:

mysql -u alex -p

How to change MySQL user password

If the MySQL prompt appears after entering the password, it means that the password has been successfully updated.

Conclusions

Now you know how to change a MySQL user password. You can change your password in several ways. Both methods are simple and fast. What method do you prefer to use? Share it in the comments!

Source: https://losst.ru/. The article is distributed under the CC-BY-SA license