Sometimes you may need to reset user password or recover MySQL password, in case you forgot user password. Here’s how to change user password in MySQL.
MySQL Change User Password
Here are the steps to change user password in MySQL. You can also use these steps to change user password in Mariadb.
1. Log into MySQL Server
On Linux:
Open terminal and run the following command to log into MySQL database server
$ mysql -u root -h localhost -p
On windows:
Navigate to the folder where mysql.exe is located (e.g C:\Program Files\mysql\mysql-5.5\bin) and double-click on mysql.exe to open MySQL command prompt.
Bonus Read : MySQL Cast as Boolean
2. Change User Password
After you have logged into MySQL, run the following command to switch database to mysql
$ use mysql;
There are 3 commands to change user password – using ALTER USER, SET PASSWORD, and UPDATE USER
Run the following command to change user password using ALTER TABLE. Replace user_name, hostname and new_password with your database username, host ip and new password respectively.
ALTER USER 'user_name'@'hostname' IDENTIFIED BY 'new_password';
For example, if you have a remote user from IP 45.23.145.35 with username read_user and new password “abcxyz”, then here’s the command to change user password
ALTER USER 'read_user'@'45.23.145.35' IDENTIFIED BY 'abcxyz';
If you want to change password of a local user, use localhost or 127.0.0.1 as host ip
ALTER USER 'read_user'@'localhost' IDENTIFIED BY 'abcxyz';
Bonus Read : MySQL CAST vs CONVERT
You can also use SET PASSWORD command to change user password in MySQL. Here’s the syntax to change user password using SET PASSWORD command. Replace user_name, hostname and new_password with your database username, host ip and new password respectively.
SET PASSWORD FOR 'user_name'@'hostname' = PASSWORD('new_password');
Here’s the above example using SET PASSWORD command
SET PASSWORD FOR 'read_user'@'localhost' = PASSWORD('abcxyz');
Similarly, here’s the syntax to change user password using UPDATE USER command.
UPDATE mysql.user SET Password=PASSWORD('new_password')
WHERE USER='user_name' AND Host='hostname';
Here’s the above example using UPDATE user command
UPDATE mysql.user SET Password=PASSWORD('abcxyz')
WHERE USER='read_user' AND Host='localhost';
Bonus Read : MySQL Rollback Query
3. Flush Privileges
Run the following command to apply the changes.
$ flush privileges;
Hopefully, the above commands will help you change user password in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.