UPDATE statement can be used to update 'User' and 'Host' column of the user table in mysql database. After the UPDATE statement is executed, we use FLUSH PRIVILEGES statement to reload the privileges from GRANT table of mysql database. The syntax to update password is:
UPDATE user SET password=PASSWORD('myPWD123')
WHERE user= 'user1' AND
host = 'myCompany.com';
The PASSWORD() function changes the password from plain text to encrypted format.
The password changed using SET statement is as given below:
SET PASSWORD FOR 'user1'@'myCompany.com' = PASSWORD('myPWD123');
The GRANT USAGE statement with the IDENTIFIED BY clause can be used to change password as shown below. The password is not passed to PASSWORD() but added directly in this method.
GRANT USAGE ON *.* TO user1@myCompany.com IDENTIFIED BY myPWD123;
To change the password of 'root' UPDATE or mysqladmin command can be used from command prompt as below:
shell> mysqladmin -u root password "newpwd";
shell> mysqladmin -u root -h host_name password "newpwd";