Change Password for User in MySQL database

Change password of user with ALTER USER statement

mysql -u root -p
mysql> ALTER USER hr@localhost IDENTIFIED BY 'Password1';
mysql> FLUSH PRIVILEGES;

Change Password for User with SET PASSWORD command

Syntax:
SET PASSWORD [FOR user] = password_option

password_option: { 'auth_string'
 | PASSWORD('auth_string') }

Example:
--For Current User
SET PASSWORD = 'Pasword1';
FLUSH PRIVILEGES;

--Use latest command above version 5.7.6
SET PASSWORD FOR 'hr'@'localhost' = Password1;
FLUSH PRIVILEGES;

--For older version use PASSWORD() function (its deprecated above version 5.7.6)
SET PASSWORD FOR 'hr'@'localhost' = PASSWORD('Password1');
FLUSH PRIVILEGES;

Check Current user

SELECT CURRENT_USER();

Change Password of user with UPDATE command


---Use latest command used above version 5.7.6
USE mysql;
UPDATE user SET authentication_string = PASSWORD('Password1')
 WHERE user = 'hr' AND host = 'localhost';
FLUSH PRIVILEGES;

--For older version below 5.7.6
user mysql;
UPDATE user 
SET password = PASSWORD('Password1')
 WHERE user = 'hr' AND 
host = 'localhost';
FLUSH PRIVILEGES;

Verify the new password of user by login

$mysql -u username -h localhost -p

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.