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.
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 USERto 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';
- 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');
In this quick write-up, you will find a solution to change a MySQL user password using SQL queries.