How to change user password on MySQL
Mysql change the user password using the following method:
- Open the bash shell and connect to the server as the root user
- mysql -u root -h localhost -p
- Run command:
- ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';
Please note that use mysql.exe on MS-Windows host as follows (first change the directory where mysql.exe is located [example: “C:\Program Files\mysql\mysql-5.0.77-win32\bin”]. Let us see examples and syntax in detail.
mysql SQL command to change a user password
To create a new MySQL user account, run the following command:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
Delete MySQL user account
mysql> DROP USER 'database_user@'localhost';
If you try to delete a user account that doesn’t exist an error will occur.
ERROR 1396 (HY000): Operation DROP USER failed for 'database_user'@'localhost'
Same as when working with the databases to avoid the error you can use:mysql> DROP USER IF EXISTS 'database_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Login as root from the shell:$ mysql -u root -p
Switch to mysql database (type command at mysql> prompt, do not include string “mysql>”):mysql> use mysql;
The syntax is as follows for mysql database server version 5.7.5 or older:
SET PASSWORD FOR 'user-name-here'@'hostname' = PASSWORD('new-password');
For mysql database server version 5.7.6 or newer use the following syntax:
ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass';
You can also use the following SQL syntax:
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';
In this example, change a password for a user called tom:
SET PASSWORD FOR 'tom'@'localhost' = PASSWORD('foobar');
OR
UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE USER='tom' AND Host='localhost';
Sample outputs:
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Feel free to replace the values for “tom” (user), “localhost” (hostname), and “foobar” (password) as per your requirements. Finally, type the following command to reload privileges:
FLUSH PRIVILEGES;
Sample outputs:
Query OK, 0 rows affected (0.00 sec)
To exit from mysql> prompt, enter:
quit;
User or you can test new password using the following shell syntax:mysql -u tom -p
When prompted enter the new password you set earlier for tom user.
0 Comments
CAN FEEDBACK
Emoji