In this tutorial, you will learn how to change a MySQL user password using SQL statements for different versions. MySQL changed the SQL statement syntax for changing user passwords from 5.7.6 and newer versions. So, you must check the MySQL server version before running the correct SQL query to change the user password.

Identify MySQL Version

First, connect to the database server by running the following command. The below command will connect to the MySQL database server running on localhost.

mysql -h localhost -u root -p

Enter the MySQL root account password to authenticate. Once the authentications are successful, You will find a database server prompt like mysql>. In the case of MariaDB, the prompt will little differ.

Here you run the SQL statement to identify the MySQL version.

mysql> SELECT VERSION();

Output:

 ------------------------- 
| VERSION()               |
 ------------------------- 
| 5.7.34-0ubuntu0.18.04.1 |
 ------------------------- 
1 row in set (0.02 sec)

The above output shows that you are running MySQL server version 5.7.34.

Change MySQL User Password

You have the version of the running MySQL server. Use one of the following commands to change the MySQL user password as per of database server version running on your system.

In the below SQL statements, make sure to change dbuser with your databsae user and localhost with the users host.

  • Use the ALTER USER to change MySQL user password for the mysql database server version 5.7.6 or newer versions: 
    mysql> ALTER USER 'dbuser'@'localhost' IDENTIFIED BY 'new-password';

    How To Change MySQL User Password mysql user

  • For the MySQL database server version 5.7.5 or older version, Use “SET PASSWORD” statement” to change a MySQL user password using SQL queries. 
    mysql> SET PASSWORD FOR 'dbuser'@'localhost' = PASSWORD('new-password');

    How To Change MySQL User Password mysql user

Conclusion

In this quick write-up, you will find a solution to change a MySQL user password using SQL queries.

,
Show Comments