Change mysql user password using command line
MysqlCommand LinePuttyMysql Problem Overview
I'm trying to update the password for a database user using the command line, and it's not working for me. This is the code I'm using:
mysql> UPDATE user SET password=PASSWORD($w0rdf1sh) WHERE user='tate256';
Could someone tell me what's wrong with this code.
Mysql Solutions
Solution 1 - Mysql
In your code, try enclosing password inside single quote. Alternatively, as per the documentation of mysql, following should work -
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('cleartext password');
FLUSH PRIVILEGES;
The last line is important or else your password change won't take effect unfortunately.
EDIT:
I ran a test in my local and it worked -
mysql> set password for 'test' = PASSWORD('$w0rdf1sh');
Query OK, 0 rows affected (0.00 sec)
Mine is version 5. You can use following command to determine your version -
SHOW VARIABLES LIKE "%version%";
Solution 2 - Mysql
As of MySQL 5.7.6, use ALTER USER
Example:
ALTER USER 'username' IDENTIFIED BY 'password';
Because:
-
SET PASSWORD ... = PASSWORD('auth_string')syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. -
SET PASSWORD ... = 'auth_string'syntax is not deprecated, butALTER USERis now the preferred statement for assigning passwords.
Solution 3 - Mysql
Note: u should login as root user
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your password');
Solution 4 - Mysql
this is the updated answer for WAMP v3.0.6
UPDATE mysql.user
SET authentication_string=PASSWORD('MyNewPass')
WHERE user='root';
FLUSH PRIVILEGES;
Solution 5 - Mysql
Before MySQL 5.7.6 this works from the command line:
mysql -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$w0rdf1sh');"
I don't have a mysql install to test on but I think in your case it would be
mysql -e "UPDATE mysql.user SET Password=PASSWORD('$w0rdf1sh') WHERE User='tate256';"
Solution 6 - Mysql
In windows 10, just exit out of current login and run this on command line
--> mysqladmin -u root password “newpassword”
where instead of root could be any user.
Solution 7 - Mysql
This works for me. Got solution from MYSQL webpage
In MySQL run below queries:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New_Password';
Solution 8 - Mysql
As of MySQL 8.0.18 This works fine for me
mysql> SET PASSWORD FOR 'user'@'localhost' = 'userpassword';
Solution 9 - Mysql
Your login root should be /usr/local/directadmin/conf/mysql.conf. Then try following
UPDATE mysql.user SET password=PASSWORD('$w0rdf1sh') WHERE user='tate256' AND Host='10.10.2.30';
FLUSH PRIVILEGES;
Host is your mysql host.