[mysql] Change mysql user password using command line

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.

This question is related to mysql command-line putty

The answer is


As of MySQL 8.0.18 This works fine for me

_x000D_
_x000D_
mysql> SET PASSWORD FOR 'user'@'localhost' = 'userpassword';
_x000D_
_x000D_
_x000D_


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%";

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';"

This works for me. Got solution from MYSQL webpage

In MySQL run below queries:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New_Password';

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, but ALTER USER is now the preferred statement for assigning passwords.


this is the updated answer for WAMP v3.0.6

UPDATE mysql.user 
SET authentication_string=PASSWORD('MyNewPass') 
WHERE user='root';

FLUSH PRIVILEGES;

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.


Note: u should login as root user

 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your password');

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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to command-line

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Flutter command not found Angular - ng: command not found how to run python files in windows command prompt? How to run .NET Core console app from the command line Copy Paste in Bash on Ubuntu on Windows How to find which version of TensorFlow is installed in my system? How to install JQ on Mac by command-line? Python not working in the command line of git bash Run function in script from command line (Node JS)

Examples related to putty

Forward X11 failed: Network error: Connection refused Best way to script remote SSH commands in Batch (Windows) AWS - Disconnected : No supported authentication methods available (server sent :publickey) Change mysql user password using command line How to download a file from my server using SSH (using PuTTY on Windows) Using putty to scp from windows to Linux Saving the PuTTY session logging Putty: Getting Server refused our key Error Google server putty connect 'Disconnected: No supported authentication methods available (server sent: publickey) Batch file for PuTTY/PSFTP file transfer automation